Bug #68987 | MySQL crash with InnoDB assertion failure in file pars0pars.cc | ||
---|---|---|---|
Submitted: | 17 Apr 2013 21:48 | Modified: | 17 Nov 2016 14:13 |
Reporter: | Zoltan Fedor | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
Version: | 5.6.10 | OS: | Linux (CentOS 6.4) |
Assigned to: | CPU Architecture: | Any | |
Tags: | crash, debug, innodb, pars0pars.cc |
[17 Apr 2013 21:48]
Zoltan Fedor
[18 Apr 2013 15:16]
MySQL Verification Team
Hello Zoltan, Thank you for the report. Please let us know if you have upgraded the server and from which version? Also, please provide complete error log.
[18 Apr 2013 15:17]
Zoltan Fedor
I have managed to find the cause of this crash - it is caused by a InnoDB fulltext index. I have used the general query log to log all queries and then after the next crash I have checked what were the last queries executed prior to the crash. It turns out that the next query caused the crashed, ever ytime I issued this query MySQL has crashed: select '1+2JR+175', sr.sr_id from globaltraining.sr where sr.product like '%eikon%' and match(sr.description) against ('+test.test12@test.com' in boolean mode) and sr.description like '%test.test12@test.com %'; Then I tried to re-build the fulltext index on the sr.description column, which also crashed the database, so I decided to move this table back to MyISAM and recreate the fulltext index there. Since then, there is no crashing, the above query now executes without crash. Interestingly I have other InnoDB tables with fulltext index and similar queries and those have no problems, so my conclusion that the InnoDB fulltext engine crashes due to some special data in the table - which is hard to pin-point as this table has over 400k rows.
[20 Apr 2013 5:20]
MySQL Verification Team
Thank you for the feedback. I cannot repeat reported behavior with the dummy data, could you please provide the test data to reproduce at our end? Thanks, Umesh
[21 May 2013 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[9 Apr 2014 9:46]
Sebastian Strzelczyk
We also had such a strange error in our database. The error occured in version 5.6.12 also after a update to 5.6.16. the problem occured only in the only tables with FTS. We loaded sql-dump from a other server to the mysql server, which run fine. Then we renamed the database. After that we had problem to update records which affect the FTS columns. We run mysqlcheck may times, most time like this: mysqlcheck --all-databases --auto-repair -f We dropped the FTS index, but it does not help, the server still crashed. The only thing which helped was, setting the variable to innodb_file_per_table = 1 and dropping the complete table. After restarting and recreate the table, we didn't had the problem anymore. I add the error-log which shows the problem, since the error occured the first time. Hope it help to solve the problem.
[9 Apr 2014 10:01]
MySQL Verification Team
> Then we renamed the database How did you do that? There is not a RENAME DATABASE sql command anymore. Renaming directory on the filesystem is not a supported way to rename the database if you have innodb tables. Better to use RENAME TABLE db1.t1 TO db2.t1 to move table to other database....
[11 Apr 2014 9:44]
Sebastian Strzelczyk
I asked the developer who made the change. He used HeidiSQL to do the rename. HeidiSQL used the RENAME TABLE db1.t1 TO db2.t1 statment to transfer the tables. We didn't made any manual change at the filesystem. I hope it will help you.
[25 May 2015 5:10]
Ramesh Sivaraman
Please find the testcase to reproduce this assertion : DROP DATABASE test;CREATE DATABASE test;USE test; set global innodb_trx_rseg_n_slots_debug=1; CREATE TABLE t1(a CHAR (1),FULLTEXT(a)); INSERT INTO t1 VALUES(); ALTER TABLE t1 DISCARD TABLESPACE; DELETE FROM t1; The attached tarball gives the testcase as an exact match of our system, including some handy utilities $ vi {epoch}_mybase # Update base path in this file (the only change required!). For non-binary distribution please update SOURCE_DIR location also. $ ./{epoch}_init # Initializes the data dir $ ./{epoch}_start # Starts mysqld $ ./{epoch}_cl # To check mysqld is up $ ./{epoch}_run # Run the testcase with pquery binary(produces output) $ vi /dev/shm/{epoch}/error.log.out # Verify the error log $ ./{epoch}_gdb # Brings you to a gdb prompt attached to correct mysqld & generated core $ ./{epoch}_parse_core # Create {epoch}_STD.gdb and {epoch}_FULL.gdb; standard and full var gdb stack traces etc. GDB info #0 0x00007f89d8707771 in pthread_kill () from /lib64/libpthread.so.0 #1 0x0000000000a954ea in my_write_core (sig=6) at /ssd/ramesh/mysql-server/mysql-5.6/mysys/stacktrace.c:422 #2 0x0000000000726494 in handle_fatal_signal (sig=6) at /ssd/ramesh/mysql-server/mysql-5.6/sql/signal_handler.cc:230 #3 <signal handler called> #4 0x00007f89d75135c9 in raise () from /lib64/libc.so.6 #5 0x00007f89d7514cd8 in abort () from /lib64/libc.so.6 #6 0x0000000000b5ca3f in pars_retrieve_table_def (sym_node=0x7f89ba074218) at /ssd/ramesh/mysql-server/mysql-5.6/storage/innobase/pars/pars0pars.cc:865 #7 0x0000000000b5d743 in pars_insert_statement (table_sym=0x7f89ba074218, values_list=0x7f89ba074318, select=0x0) at /ssd/ramesh/mysql-server/mysql-5.6/storage/innobase/pars/pars0pars.cc:1372 #8 0x0000000000d2788d in yyparse () at pars0grm.y:379 #9 0x0000000000b5eb1f in pars_sql (info=0x7f89ba06d278, str=0x7f89ba00fad8 "PROCEDURE P() IS\nBEGIN INSERT INTO \"test/FTS_", '0' <repeats 14 times>, "14_DELETED\" VALUES (:doc_id);\nEND;\n") at /ssd/ramesh/mysql-server/mysql-5.6/storage/innobase/pars/pars0pars.cc:2243 #10 0x0000000000d2415d in fts_parse_sql (fts_table=0x7f89d8cbb100, info=0x7f89ba06d278, sql=0x1057ed8 "BEGIN INSERT INTO \"%s\" VALUES (:doc_id);") at /ssd/ramesh/mysql-server/mysql-5.6/storage/innobase/fts/fts0sql.cc:213 #11 0x0000000000d0c9d3 in fts_delete (ftt=0x7f89ba0ac188, row=0x7f89ba06b260) at /ssd/ramesh/mysql-server/mysql-5.6/storage/innobase/fts/fts0fts.cc:3004 #12 0x0000000000d0ce46 in fts_commit_table (ftt=0x7f89ba0ac188) at /ssd/ramesh/mysql-server/mysql-5.6/storage/innobase/fts/fts0fts.cc:3140 #13 0x0000000000d0cf12 in fts_commit (trx=0x7f89ba0c7478) at /ssd/ramesh/mysql-server/mysql-5.6/storage/innobase/fts/fts0fts.cc:3182 #14 0x0000000000c29021 in trx_commit_low (trx=0x7f89ba0c7478, mtr=0x7f89d8cbb270) at /ssd/ramesh/mysql-server/mysql-5.6/storage/innobase/trx/trx0trx.cc:1353 #15 0x0000000000c2910e in trx_commit (trx=0x7f89ba0c7478) at /ssd/ramesh/mysql-server/mysql-5.6/storage/innobase/trx/trx0trx.cc:1417 #16 0x0000000000c2991e in trx_commit_for_mysql (trx=0x7f89ba0c7478) at /ssd/ramesh/mysql-server/mysql-5.6/storage/innobase/trx/trx0trx.cc:1635 #17 0x0000000000aad6d0 in innobase_commit_low (trx=0x7f89ba0c7478) at /ssd/ramesh/mysql-server/mysql-5.6/storage/innobase/handler/ha_innodb.cc:3402 #18 0x0000000000aada42 in innobase_commit (hton=0x7f89d23f98c0, thd=0x7f89c7fee000, commit_trx=false) at /ssd/ramesh/mysql-server/mysql-5.6/storage/innobase/handler/ha_innodb.cc:3550 #19 0x0000000000637f21 in ha_commit_low (thd=0x7f89c7fee000, all=false, run_after_commit=true) at /ssd/ramesh/mysql-server/mysql-5.6/sql/handler.cc:1493 #20 0x000000000071095e in TC_LOG_DUMMY::commit (this=0x17f1768 <tc_log_dummy>, thd=0x7f89c7fee000, all=false) at /ssd/ramesh/mysql-server/mysql-5.6/sql/log.h:115 #21 0x0000000000637d65 in ha_commit_trans (thd=0x7f89c7fee000, all=false, ignore_global_read_lock=false) at /ssd/ramesh/mysql-server/mysql-5.6/sql/handler.cc:1436 #22 0x000000000089e25c in trans_commit_stmt (thd=0x7f89c7fee000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/transaction.cc:434 #23 0x00000000007d8f61 in mysql_execute_command (thd=0x7f89c7fee000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:5006 #24 0x00000000007dbdbc in mysql_parse (thd=0x7f89c7fee000, rawbuf=0x7f89ba01f010 "DELETE FROM t1", length=14, parser_state=0x7f89d8cbce70) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:6245 #25 0x00000000007cf36f in dispatch_command (command=COM_QUERY, thd=0x7f89c7fee000, packet=0x7f89c13e1001 "DELETE FROM t1", packet_length=14) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:1332 #26 0x00000000007ce45e in do_command (thd=0x7f89c7fee000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:1034 #27 0x0000000000796879 in do_handle_one_connection (thd_arg=0x7f89c7fee000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_connect.cc:982 #28 0x0000000000796362 in handle_one_connection (arg=0x7f89c7fee000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_connect.cc:898 #29 0x0000000000d71288 in pfs_spawn_thread (arg=0x7f89d2ffe600) at /ssd/ramesh/mysql-server/mysql-5.6/storage/perfschema/pfs.cc:1860 #30 0x00007f89d8702df3 in start_thread () from /lib64/libpthread.so.0 #31 0x00007f89d75d41ad in clone () from /lib64/libc.so.6
[25 May 2015 5:14]
Ramesh Sivaraman
Assertion is with debug build. MS version 5.6.21-debug
[25 May 2015 5:15]
Ramesh Sivaraman
Testcase bundle
Attachment: 1432526507_bug_bundle.tar.gz (application/gzip, text), 1014.70 KiB.
[17 Nov 2016 14:02]
MySQL Verification Team
c:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > " Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.36-debug Source distribution PULL: 2016-NOV-05 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.6 > DROP DATABASE test;CREATE DATABASE test;USE test; ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist Query OK, 1 row affected (0.00 sec) Database changed mysql 5.6 > set global innodb_trx_rseg_n_slots_debug=1; Query OK, 0 rows affected (0.00 sec) mysql 5.6 > CREATE TABLE t1(a CHAR (1),FULLTEXT(a)); Query OK, 0 rows affected (2.15 sec) mysql 5.6 > INSERT INTO t1 VALUES(); Query OK, 1 row affected (0.04 sec) mysql 5.6 > ALTER TABLE t1 DISCARD TABLESPACE; ERROR 1637 (HY000): Too many active concurrent transactions mysql 5.6 > DELETE FROM t1; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql 5.6 > 2016-11-17 11:58:34 8688 [Note] c:\dbs\5.6\bin\mysqld: ready for connections. Version: '5.6.36-debug' socket: '' port: 3560 Source distribution PULL: 2016-NOV-05 2016-11-17 11:59:10 2e04 InnoDB: Warning: cannot find a free slot for an undo log. Do you have too InnoDB: many active transactions running concurrently? 2016-11-17 11:59:16 2e04 InnoDB: Assertion failure in thread 11780 in file pars0pars.cc line 865 InnoDB: Failing assertion: sym_node->table != NULL InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. R6010 - abort() has been called 13:59:16 UTC - mysqld got exception 0x80000003 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388608 read_buffer_size=131072 max_used_connections=1 max_threads=151 thread_count=1 connection_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 67958 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x20601114e20 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... 7ff6f7a4ea45 mysqld.exe!my_sigabrt_handler()[my_thr_init.c:455] 7ff6f8031e1f mysqld.exe!raise()[winsig.c:594] 7ff6f8033d50 mysqld.exe!abort()[abort.c:82] 7ff6f7da6f0e mysqld.exe!pars_retrieve_table_def()[pars0pars.cc:865] 7ff6f7da2a61 mysqld.exe!pars_insert_statement()[pars0pars.cc:1375] 7ff6f7e4f61b mysqld.exe!yyparse()[pars0grm.y:379] 7ff6f7da1290 mysqld.exe!pars_sql()[pars0pars.cc:2245] 7ff6f7dfaeff mysqld.exe!fts_parse_sql()[fts0sql.cc:213] 7ff6f7b6565d mysqld.exe!fts_delete()[fts0fts.cc:2988] 7ff6f7b659d1 mysqld.exe!fts_commit_table()[fts0fts.cc:3124] 7ff6f7b58fa7 mysqld.exe!fts_commit()[fts0fts.cc:3166] 7ff6f7bc37ac mysqld.exe!trx_commit_low()[trx0trx.cc:1352] 7ff6f7bc30e7 mysqld.exe!trx_commit()[trx0trx.cc:1417] 7ff6f7bc3e89 mysqld.exe!trx_commit_for_mysql()[trx0trx.cc:1635] 7ff6f7ad2819 mysqld.exe!innobase_commit_low()[ha_innodb.cc:3551] 7ff6f7ad0db2 mysqld.exe!innobase_commit()[ha_innodb.cc:3698] 7ff6f7563159 mysqld.exe!ha_commit_low()[handler.cc:1493] 7ff6f755355a mysqld.exe!TC_LOG_DUMMY::commit()[log.h:115] 7ff6f75626b9 mysqld.exe!ha_commit_trans()[handler.cc:1436] 7ff6f777a87f mysqld.exe!trans_commit_stmt()[transaction.cc:434] 7ff6f768de26 mysqld.exe!mysql_execute_command()[sql_parse.cc:5068] 7ff6f7685fba mysqld.exe!mysql_parse()[sql_parse.cc:6416] 7ff6f768f6a9 mysqld.exe!dispatch_command()[sql_parse.cc:1372] 7ff6f768e6c5 mysqld.exe!do_command()[sql_parse.cc:1036] 7ff6f76dffe2 mysqld.exe!do_handle_one_connection()[sql_connect.cc:982] 7ff6f76dfe12 mysqld.exe!handle_one_connection()[sql_connect.cc:900] 7ff6f7f0a2e5 mysqld.exe!pfs_spawn_thread()[pfs.cc:1862] 7ff6f7a4d016 mysqld.exe!pthread_start()[my_winthread.c:62] 7ff6f8042395 mysqld.exe!_callthreadstartex()[threadex.c:376] 7ff6f80425e7 mysqld.exe!_threadstartex()[threadex.c:359] 7fff5ca48364 KERNEL32.DLL!BaseThreadInitThunk() 7fff5ead5e91 ntdll.dll!RtlUserThreadStart()
[17 Nov 2016 14:13]
MySQL Verification Team
Thank you for the feedback and test case.
[8 May 2017 10:36]
Mark Penzer
We have the same issue, is there a fix for this? MySQL version 5.6.35 2017-05-08 11:04:31 7f405379a700 InnoDB: Assertion failure in thread 139914255116032 in file pars0pars.cc line 865 InnoDB: Failing assertion: sym_node->table != NULL InnoDB: We intentionally generate a memory trap.
[29 Dec 2017 13:48]
MySQL Verification Team
Ramesh's testcase is for debug build only. The original reporter of this bug is a different problem to what was verified here.
[12 Mar 2018 15:30]
Joaquin Cuenca Abela
I attached a SQL that reproduces this bug in a clear MySQL install (reproduced in 5.7.21 and 5.7.20, not able to reproduce in 8.0.4-rc). To reproduce it download the attached SQL into the current directory and run: $ docker run -v $PWD:/docker-entrypoint-initdb.d -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:5.7.21 Surprisingly the results were not consistent. It only crashes every 2 or 3 docker runs (always starting from scratch). The stack trace is: 2018-03-12 15:24:42 0x7fec7d7fa700 InnoDB: Assertion failure in thread 140653694527232 in file pars0pars.cc line 822 InnoDB: Failing assertion: sym_node->table != NULL InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 15:24:42 UTC - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail. key_buffer_size=8388608 read_buffer_size=131072 max_used_connections=1 max_threads=151 thread_count=1 connection_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68193 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0 thread_stack 0x40000 mysqld(my_print_stacktrace+0x2c)[0x55c9a270a0dc] mysqld(handle_fatal_signal+0x479)[0x55c9a2036df9] /lib/x86_64-linux-gnu/libpthread.so.0(+0x110c0)[0x7feca2c890c0] /lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcf)[0x7feca1415fcf] /lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7feca14173fa] mysqld(+0x61f729)[0x55c9a200d729] mysqld(_Z21pars_insert_statementP10sym_node_tPvP10sel_node_t+0x3a8)[0x55c9a27ed2d8] mysqld(_Z7yyparsev+0x1ad8)[0x55c9a29f8d98] mysqld(_Z8pars_sqlP11pars_info_tPKc+0x9e)[0x55c9a27eeaae] mysqld(_Z13fts_parse_sqlP11fts_table_tP11pars_info_tPKc+0x153)[0x55c9a29d94e3] mysqld(_Z14fts_write_nodeP5trx_tPP10que_fork_tP11fts_table_tP12fts_string_tP10fts_node_t+0x262)[0x55c9a29b4f32] mysqld(+0xfcb0b3)[0x55c9a29b90b3] mysqld(_Z14fts_sync_tableP12dict_table_tbbb+0x214)[0x55c9a29bea14] mysqld(_Z23fts_optimize_sync_tablem+0x42)[0x55c9a29c5a22] mysqld(_Z19fts_optimize_threadPv+0x8ed)[0x55c9a29ceabd] /lib/x86_64-linux-gnu/libpthread.so.0(+0x7494)[0x7feca2c7f494] /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7feca14cbaff] The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. ERROR 2013 (HY000) at line 371: Lost connection to MySQL server during query
[13 Apr 2018 8:25]
Daniel van Dorp
I experienced the exact same issue with MySQL 5.7.21 on Ubuntu 14.04 (Trusty LTS). The commit that could be related: https://github.com/mysql/mysql-server/commit/14e5c10f873992d7fe581c10d68dd9b379af02f6 , seems to indicate that this change is present in the following MySQL versions: - mysql-8.0.4 - mysql-8.0.3 - mysql-5.7.21 - mysql-5.7.20 - mysql-5.6.39 - mysql-5.6.38 As I was preparing a big production migration, I had to resort to rolling back to mysql-5.7.19 in which I was indeed unable to reproduce the problem. As per the comment by Joaquin Cuenca Abela on 12 Mar 15:30 in this thread, he was able to reproduce it in mysql-5.7.21 and mysql-5.7.20 from time to time, but not consistently. Others (like eg. Zoltan Fedor) there suggest that it is caused by an InnoDB fulltext index rebuild, that the InnoDB fulltext engine crashes due to some special data in the table (however, that would be pretty hard to pin-point). I hope this information provides some useful context that could help in resolving this matter, as ideally one should not have to resort to rollback to older versions, and this is really blocking us.