sql函数在数据库服务器(mariadb)上调用两次时会导致segfault

brtdzjyr  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(245)

我在ArchLinux上使用了一个mariadb mysql服务器（版本10.4.13-mariadb），并且我已经成功地创建了一个函数，每当第二次调用它时就会崩溃数据库（或者至少在我的测试中第一次使用时它从未崩溃过服务器）。没有明显的错误是从来没有打印，服务器只是重新启动，我看了 journalctl -e -u mariadb.service 但这对我并没有多大帮助，尽管它让我相信这次坠机是由细分故障造成的。
我发现了一些2005年的错误报告，其中描述了类似的错误，但经过仔细检查，我不认为这是由相同的问题造成的。谁能告诉我这里发生了什么事？一整天都快把我逼疯了。
调用sql函数 register_user 看起来是这样的：在将行添加到 User table。

DELIMITER $$
CREATE PROCEDURE register_user(user_name TEXT, user_email TEXT, user_passw_hash TEXT, user_invite_code INT)
RETURNS BOOLEAN
BEGIN
    -- Check: no previous use of email and invite code is still valid.
    IF NOT EXISTS (
        SELECT * FROM User WHERE email = user_email
    )
    AND EXISTS (
        SELECT * FROM Invite
        WHERE id = user_invite_code 
        AND valid_till > NOW()
    )
    AND NOT EXISTS (
        SELECT Invite.id, Invite.max_uses, COUNT(User.joined_with)
        FROM Invite
        LEFT JOIN User ON User.joined_with = Invite.id
        GROUP BY Invite.id, Invite.max_uses
        HAVING COUNT(User.joined_with) >= Invite.max_uses
    )

    -- If checks succeed insert values into users table and return true.
    THEN INSERT INTO User(email, name, passw_hash, joined_with)
        VALUES(user_email, user_name, user_passw_hash, user_invite_code);
        RETURN TRUE;

    ELSE RETURN FALSE;

    END IF;
END$$

DELIMITER ;

编辑：我修改了一些函数参数名称，以减少混淆。
函数使用下表：

CREATE TABLE Invite (
    id INT PRIMARY KEY,
    valid_till TIMESTAMP,
    max_uses INT UNSIGNED
);

CREATE TABLE User (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL DEFAULT '' UNIQUE,
    name TEXT,
    passw_hash TEXT DEFAULT '' NOT NULL,
    joined_with INT NOT NULL,
    joined_on TIMESTAMP,
    FOREIGN KEY (joined_with) REFERENCES Invite(id)
);

的输出 journalctl -e -u mariadb.service :

jun 02 17:49:14 LinuxIsPower systemd[1]: Started MariaDB 10.4.13 database server.
jun 02 17:50:35 LinuxIsPower mysqld[29155]: 200602 17:50:35 [ERROR] mysqld got signal 11 ;
jun 02 17:50:35 LinuxIsPower mysqld[29155]: This could be because you hit a bug. It is also possible that this binary
jun 02 17:50:35 LinuxIsPower mysqld[29155]: or one of the libraries it was linked against is corrupt, improperly built,
jun 02 17:50:35 LinuxIsPower mysqld[29155]: or misconfigured. This error can also be caused by malfunctioning hardware.
jun 02 17:50:35 LinuxIsPower mysqld[29155]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
jun 02 17:50:35 LinuxIsPower mysqld[29155]: We will try our best to scrape up some info that will hopefully help
jun 02 17:50:35 LinuxIsPower mysqld[29155]: diagnose the problem, but since we have already crashed,
jun 02 17:50:35 LinuxIsPower mysqld[29155]: something is definitely wrong and this may fail.
jun 02 17:50:35 LinuxIsPower mysqld[29155]: Server version: 10.4.13-MariaDB
jun 02 17:50:35 LinuxIsPower mysqld[29155]: key_buffer_size=134217728
jun 02 17:50:35 LinuxIsPower mysqld[29155]: read_buffer_size=131072
jun 02 17:50:35 LinuxIsPower mysqld[29155]: max_used_connections=1
jun 02 17:50:35 LinuxIsPower mysqld[29155]: max_threads=153
jun 02 17:50:35 LinuxIsPower mysqld[29155]: thread_count=7
un 02 17:50:35 LinuxIsPower mysqld[29155]: It is possible that mysqld could use up to
jun 02 17:50:35 LinuxIsPower mysqld[29155]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467756 K  bytes of memory
jun 02 17:50:35 LinuxIsPower mysqld[29155]: Hope that's ok; if not, decrease some variables in the equation.
jun 02 17:50:35 LinuxIsPower mysqld[29155]: Thread pointer: 0x7f34e7807348
jun 02 17:50:35 LinuxIsPower mysqld[29155]: Attempting backtrace. You can use the following information to find out
jun 02 17:50:35 LinuxIsPower mysqld[29155]: where mysqld died. If you see no messages after this, something went
jun 02 17:50:35 LinuxIsPower mysqld[29155]: terribly wrong...
jun 02 17:50:35 LinuxIsPower mysqld[29155]: stack_bottom = 0x7f35254835b8 thread_stack 0x49000
jun 02 17:50:35 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(my_print_stacktrace+0x2a)[0x557ad1d044ea]
jun 02 17:50:35 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(handle_fatal_signal+0x578)[0x557ad181b9f8]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: sigaction.c:0(__restore_rt)[0x7f3526002960]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN4JOIN7cleanupEb+0x2c3)[0x557ad165da93]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN4JOIN7destroyEv+0x40)[0x557ad165def0]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN13st_select_lex7cleanupEv+0x6a)[0x557ad16c48ca]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN30subselect_single_select_engine7prepareEP3THD+0x3a)[0x557ad18ce64a]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN14Item_subselect10fix_fieldsEP3THDPP4Item+0x139)[0x557ad18ce0a9]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN9Item_cond10fix_fieldsEP3THDPP4Item+0x1b8)[0x557ad1852958]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN3THD16sp_fix_func_itemEPP4Item+0x2c)[0x557ad15890ec]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN3THD20sp_prepare_func_itemEPP4Itemj+0xf)[0x557ad158915f]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN20sp_instr_jump_if_not9exec_coreEP3THDPj+0x1e)[0x557ad15891be]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x114)[0x557ad158fdb4]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN7sp_head7executeEP3THDb+0x8cc)[0x557ad158accc]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN7sp_head16execute_functionEP3THDPP4ItemjP5FieldPP11sp_rcontextP11Query_arena+0x53d)[0x557ad158cb7d]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN7Item_sp12execute_implEP3THDPP4Itemj+0x122)[0x557ad1838742]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN7Item_sp7executeEP3THDPbPP4Itemj+0x23)[0x557ad1838903]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN12Item_func_sp7val_intEv+0xa)[0x557ad189944a]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZNK12Type_handler14Item_send_tinyEP4ItemP8ProtocolP8st_value+0x18)[0x557ad176cb48]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN8Protocol19send_result_set_rowEP4ListI4ItemE+0xa5)[0x557ad156e5d5]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN11select_send9send_dataER4ListI4ItemE+0x4b)[0x557ad15d1d0b]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN4JOIN10exec_innerEv+0xd14)[0x557ad167e4e4]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_ZN4JOIN4execEv+0x25)[0x557ad167e755]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x138)[0x557ad167c8c8]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x154)[0x557ad167d394]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(+0x66c02c)[0x557ad160f02c]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_Z21mysql_execute_commandP3THD+0x68c7)[0x557ad161d617]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x1c9)[0x557ad161fed9]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1d34)[0x557ad1622de4]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_Z10do_commandP3THD+0x105)[0x557ad1624625]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1c6)[0x557ad1708936]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: /usr/bin/mysqld(handle_one_connection+0x33)[0x557ad1708a73]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: pthread_create.c:0(start_thread)[0x7f3525ff7422]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: :0(__GI___clone)[0x7f35257c7bf3]
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Trying to get some variables.
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Some pointers may be invalid and cause the dump to abort.
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Query (0x7f34e785fde0): SELECT register_user('matthijs', 'an@email.com', 'sdalioas', 1235) AS success
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Connection ID (thread ID): 8
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Status: NOT_KILLED
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_push>
jun 02 17:50:36 LinuxIsPower mysqld[29155]: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
jun 02 17:50:36 LinuxIsPower mysqld[29155]: information that should help you find out what is causing the crash.
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Writing a core file...
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Working directory at /var/lib/mysql
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Resource Limits:
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Limit                     Soft Limit           Hard Limit           Units
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max cpu time              unlimited            unlimited            seconds
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max file size             unlimited            unlimited            bytes
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max data size             unlimited            unlimited            bytes
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max stack size            8388608              unlimited            bytes
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max core file size        unlimited            unlimited            bytes
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max resident set          unlimited            unlimited            bytes
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max processes             62581                62581                processes
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max open files            16364                16364                files
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max locked memory         65536                65536                bytes
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max address space         unlimited            unlimited            bytes
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max file locks            unlimited            unlimited            locks
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max pending signals       62581                62581                signals
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max msgqueue size         819200               819200               bytes
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max nice priority         0                    0
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max realtime priority     0                    0
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Max realtime timeout      unlimited            unlimited            us
jun 02 17:50:36 LinuxIsPower mysqld[29155]: Core pattern: |/usr/lib/systemd/systemd-coredump %P %u %g %s %t %c ...
jun 02 17:50:37 LinuxIsPower systemd[1]: mariadb.service: Main process exited, code=killed, status=11/SEGV
jun 02 17:50:37 LinuxIsPower systemd[1]: mariadb.service: Failed with result 'signal'.
jun 02 17:50:42 LinuxIsPower systemd[1]: mariadb.service: Scheduled restart job, restart counter is at 21.
jun 02 17:50:42 LinuxIsPower systemd[1]: Stopped MariaDB 10.4.13 database server.
jun 02 17:50:42 LinuxIsPower systemd[1]: Starting MariaDB 10.4.13 database server...
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] /usr/bin/mysqld (mysqld 10.4.13-MariaDB) starting as process 29410 ...
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Warning] Could not increase number of max_open_files to more than 16364 (request: 32194)
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Using Linux native AIO
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Uses event mutexes
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Number of pools: 1
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Using SSE2 crc32 instructions
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts)
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Completed initialization of buffer pool
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=2135060
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Creating shared tablespace for temporary tables
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Waiting for purge to start
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: 10.4.13 started; log sequence number 2135069; transaction id 4666
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] InnoDB: Buffer pool(s) load completed at 200602 17:50:42
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] Server socket created on IP: '::'.
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] Reading of all Master_info entries succeeded
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] Added new Master_info '' to hash table
jun 02 17:50:42 LinuxIsPower mysqld[29410]: 2020-06-02 17:50:42 0 [Note] /usr/bin/mysqld: ready for connections.
jun 02 17:50:42 LinuxIsPower mysqld[29410]: Version: '10.4.13-MariaDB'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Arch Linux
jun 02 17:50:42 LinuxIsPower systemd[1]: Started MariaDB 10.4.13 database server.
sqlmysqlmariadb

1条答案

按热度按时间
fjnneemd

fjnneemd1#

数据库不应该崩溃，这是正确的。要改进代码（并可能降低崩溃的可能性），可以执行以下操作：
使用过程而不是函数，因为您所做的看起来更像过程
第二张支票不也应该包括 invite_code 作为参数传入？
修复 GROUP BY 询问，因为它似乎不正确
不要使用与列名匹配的参数名（如 name )

赞(0）分享  回复(0）举报  2021-07-26
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com