[Bug 1729536] Re: InnoDB: Failing assertion: sym_node->table != NULL
Unfortunately, we had big hopes but we experienced again this exact bug, with the exact same trace for version 5.1.21. +-+ | VERSION() | +-+ | 5.7.21-0ubuntu0.16.04.1-log | +-+ Trace found in error.log: 2018-03-16 05:53:38 0x7f8e4a4bf700 InnoDB: Assertion failure in thread 140249108576000 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. 04:53:38 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=16777216 read_buffer_size=131072 max_used_connections=0 max_threads=214 thread_count=7 connection_count=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 101418 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 0x3 /usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xe8f29b] /usr/sbin/mysqld(handle_fatal_signal+0x489)[0x787029] /lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7fb5700bc390] /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38)[0x7fb56f475428] /lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7fb56f47702a] /usr/sbin/mysqld[0x75ca3e] /usr/sbin/mysqld(_Z21pars_insert_statementP10sym_node_tPvP10sel_node_t+0x3a8)[0xf62a08] /usr/sbin/mysqld(_Z7yyparsev+0x1227)[0x11839a7] /usr/sbin/mysqld(_Z8pars_sqlP11pars_info_tPKc+0x9e)[0xf6425e] /usr/sbin/mysqld(_Z13fts_parse_sqlP11fts_table_tP11pars_info_tPKc+0x190)[0x11635b0] /usr/sbin/mysqld(_Z14fts_write_nodeP5trx_tPP10que_fork_tP11fts_table_tP12fts_string_tP10fts_node_t+0x292)[0x113ddb2] /usr/sbin/mysqld[0x1141568] /usr/sbin/mysqld(_Z14fts_sync_tableP12dict_table_tbbb+0x329)[0x11475b9] /usr/sbin/mysqld(_Z23fts_optimize_sync_tablem+0x42)[0x114ef02] /usr/sbin/mysqld(_Z19fts_optimize_threadPv+0x57c)[0x115845c] /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fb5700b26ba] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fb56f54741d] 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. We recovered the replication by skipping the duplicate entries that were apparently already processed. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1729536 Title: InnoDB: Failing assertion: sym_node->table != NULL To manage notifications about this bug go to: https://bugs.launchpad.net/mysql-server/+bug/1729536/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1729536] Re: InnoDB: Failing assertion: sym_node->table != NULL
@Andreas: It wasn't private before ... I don't have access to it anymore either, even when I'm logged in the portal. We proceeded with the update of MySQL to version 5.7.21 a few days ago. We just reactivated the OPTIMIZE and TRUNCATE query for the run of tomorrow morning. I will post some feedback here in a week time, or earlier if the issue still is present. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1729536 Title: InnoDB: Failing assertion: sym_node->table != NULL To manage notifications about this bug go to: https://bugs.launchpad.net/mysql-server/+bug/1729536/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1729536] Re: InnoDB: Failing assertion: sym_node->table != NULL
Apologies for my lack of feedback but those were quite busy days ... A small update though: the bug I reported on the MySQL bugs has been set as duplicate of the following on the bugs.mysql.com portal: https://bugs.mysql.com/bug.php?id=88844 Severity is S1: critical. ** Bug watch added: MySQL Bug System #88844 http://bugs.mysql.com/bug.php?id=88844 -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1729536 Title: InnoDB: Failing assertion: sym_node->table != NULL To manage notifications about this bug go to: https://bugs.launchpad.net/mysql-server/+bug/1729536/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1729536] Re: InnoDB: Failing assertion: sym_node->table != NULL
Hi Lars, Thanks for your reply. Please note that we experienced a crash again this morning, while all optimise queries were removed from our batches ... so I fear it might be misleading. I will try to reproduce the crash in a VM. In the meantime, please find our mysqld configuration file below: == Master configuration == # MySQL 5.7 configuration (2016.07.13 - made by efj) # For explanations see http://dev.mysql.com/doc/mysql/en/server-system-variables.html # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] # Basic Settings - do not touch user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking skip-name-resolve # Limiting maximum amount of connexions max_connections = 250 max_user_connections = 50 # Security sql_mode= '' # Default options for new db/tables default-storage-engine=InnoDB character-set-server=utf8 collation-server=utf8_general_ci # Fine tuning for full text searches - At least 3 characters ft_min_word_len=2 # Accept incoming connections from all clients bind-address= 0.0.0.0 # Fine Tuning key_buffer_size = 16M max_allowed_packet = 48M thread_stack= 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed, the first time they are touched myisam-recover-options = BACKUP # Query Cache Configuration query_cache_limit = 1M query_cache_size= 128M # Error log - should be very few entries. log_error = /var/log/mysql/error.log # Slow queries slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log # Logging and Replication server-id = 7 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days= 5 max_binlog_size = 100M log-slave-updates master_info_repository=TABLE relay_log_info_repository=TABLE relay_log=relay-bin.log # InnoDB server specific configuration innodb_buffer_pool_size = 150G innodb_log_file_size = 256M innodb_log_buffer_size=4M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_thread_concurrency = 8 innodb_file_per_table == Slave configuration == # MySQL 5.7 configuration (2016.07.13 - made by efj) # For explanations see http://dev.mysql.com/doc/mysql/en/server-system-variables.html # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] # Basic Settings - do not touch user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking skip-name-resolve # Limiting maximum amount of connexions max_connections = 250 max_user_connections = 50 # Default options for new db/tables default-storage-engine=InnoDB character-set-server=utf8 collation-server=utf8_general_ci # Security sql_mode= '' # Fine tuning for full text searches - At least 3 characters ft_min_word_len=2 # Accept incoming connections from all clients bind-address= 0.0.0.0 # Fine Tuning key_buffer_size = 16M max_allowed_packet = 48M thread_stack= 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed, the first time they are touched myisam-recover-options = BACKUP # Query Cache Configuration query_cache_limit = 1M query_cache_size= 128M # Error log - should be very few entries. log_error = /var/log/mysql/error.log # Slow queries slow_query_log = 1 slow_query_log_file= /var/log/mysql/mysql-slow.log # Logging and Replication server-id = 8 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days= 3 max_binlog_size = 100M log-slave-updates master_info_repository=TABLE relay_log_info_repository=TABLE relay_log=relay-bin.log # InnoDB server specific innodb_buffer_pool_size = 150G innodb_log_file_size = 256M innodb_log_buffer_size=4M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_thread_concurrency = 8 innodb_file_per_table == PhpMyAdmin dump of the *supposed to be* problematic table == -- phpMyAdmin SQL
[Bug 1729536] Re: InnoDB: Failing assertion: sym_node->table != NULL
Dears, I finally finished narrowing it down to the single request causing the crash. I reactivated the TRUNCATE requests and those are working flawlessly. It is an OPTIMIZE TABLE request on a table of about 7 entries that causes the mysql service crash. It might be under use simultaneously, but only on the mysql master server, not on the slave where the crash occurs. This OPTIMIZE TABLE gets executed on the master server, then replicated on the slave server where it causes a mysql service crash, with the backtrace mentioned a few days ago. What can I do next to further investigate this in order to provide feedback over here in an efficient way ? Thanks in advance for the help, Eric -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1729536 Title: InnoDB: Failing assertion: sym_node->table != NULL To manage notifications about this bug go to: https://bugs.launchpad.net/mysql-server/+bug/1729536/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1729536] Re: InnoDB: Failing assertion: sym_node->table != NULL
I reactivated one set of optimize queries for this night and it crashed with the exact same backtrace. So I can confirm this is indeed related to this specific set. However, there is nothing fancy about them at all. It is a simple SQL script with 9 OPTIMIZE statements being run from the mysql client (CLI) on a single database. OPTIMIZE TABLE Table1; OPTIMIZE TABLE Table2; OPTIMIZE TABLE Table3; ... When executing the queries manually, one by one, all work. When executing the queries from an sql script via the mysql client (CLI), all works. I would tend to believe that it must be linked to some execution context. The only thing I can think of is concurrent queries being run at the same time ... but again, in our case, the crash occurs on a slave server running queries from its master only. In this configuration, there should not be anything like a concurrent query I believe ? Only binlog entries being read one by one and applied ? -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1729536 Title: InnoDB: Failing assertion: sym_node->table != NULL To manage notifications about this bug go to: https://bugs.launchpad.net/mysql-server/+bug/1729536/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1729536] Re: InnoDB: Failing assertion: sym_node->table != NULL
@Olaf: it wasn't available anymore unfortunately. After deactivating the OPTIMIZE statements and changing the TRUNCATE statement to DELETE FROM in our pre-production scripts, I can happily confirm there was no outage on our server during this night. If this can help investigating this bug in any way? Thanks in advance, Eric -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1729536 Title: InnoDB: Failing assertion: sym_node->table != NULL To manage notifications about this bug go to: https://bugs.launchpad.net/mysql-server/+bug/1729536/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1729536] Re: InnoDB: Failing assertion: sym_node->table != NULL
@ChristianEhrhardt Thanks for your replies and initiatives. Much appreciated. I did indeed notice the install package we could rollback to using apt-get install mysql-server=XXX was 5.7.11, but this version is more than a year and a half old, which doesn't sound like such a good idea. Coming back to our use case: the replication master is still running version 5.7.19 and is working flawlessly (fortunately ...), only the replication slave is running version 5.7.20. Without proof of any kind so far, but based on Olaf's message, this would suggest that regression was indeed linked to the update from 5.7.19 to 5.7.20. As silly as it may sound, the crash is now "stabilised" on our infrastructure and handled on a daily basis using the same recovery method: fast forward the replication (basically replay the statements that weren't rolled back at the moment of crash) until there is no more replication error. I believe we will keep on doing this until a fix is published. @Olaf: >> What I do know though is that it has to be somehow reproducible because the >> timeframe within which it happens is the same every day. > That's interesting. What makes this timeframe different from other > timeframes? Are you altering tables? It is the only time of the day (pre-production activity) when we are running DDL queries (OPTIMIZE / TRUNCATE). To my knowledge of InnoDB, OPTIMIZE is not supported as such by InnoDB, and actually performs a recreate of the whole table from scratch. TRUNCATE will also perform a full table recreate. As for the rest of the day (actual production), we have only DML queries running on the servers, except for the upgrades we manually perform. To move forward and get some concrete information, I will go through all of our batches and: - disable exceptionally all the OPTIMIZE queries - replace the TRUNCATE statements by DELETE FROM statements. I will post some feedback here tomorrow morning. Thanks again for your replies, Eric -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1729536 Title: InnoDB: Failing assertion: sym_node->table != NULL To manage notifications about this bug go to: https://bugs.launchpad.net/mysql-server/+bug/1729536/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1729536] Re: InnoDB: Failing assertion: sym_node->table != NULL
Hi ChristianEhrhardt, I went through the bug reports you mention, but this does not seem to apply to what we are experiencing. We also tried many approaches thinking there was some problem with the data at first. The only thing we did not try yet is to recreate the ibdata/ib_log files because it seems like an overkill. Especially knowing the fact that we double checked the data and that, after a restart of the mysql service and fixing the replication status, queries actually seem to work and not make the server crash ... I believe this is somehow contextual, but I am at loss of ideas as to how to approach this. What I do know though is that it has to be somehow reproducible because the timeframe within which it happens is the same every day. Thanks in advance for any help, it would be more than welcome. On a side note, if there exists a way to elegantly rollback to version 5.7.19, I would be more than happy to hear about it too. Best regards, Eric -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1729536 Title: InnoDB: Failing assertion: sym_node->table != NULL To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1729536/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1729536] Re: InnoDB: Failing assertion: sym_node->table != NULL
Hi Olaf, You are not alone ... This bug did not occur on our systems when we were using version 5.7.19, but since we upgraded to 5.7.20, we get this on a regular basis, but still are unable to locate the exact cause. I thought it was related specifically to replication, but according to your message, it seems it can happen for some other reason as well ? I already reported this bug on the mysql bug reporting space a few days ago: https://bugs.mysql.com/bug.php?id=88653 Content of the bug report: We are experiencing repeated and random crashes on a replication slave of multiple masters. This server is replicating several other MySQL servers via multiple channels (4 to be exact). What we can observe is that at some point the server crashes, then restarts and launches innodb recovery processes. After this, we have again a running MySQL server, but the master positioning for all channels is rolled back to some point in the past ... but not the data. It is necessary to skip the records one by one for each replication channel, until we reach the point when the crash happened, so we can resume the replication. Here is a dump of the relevant part of the file /var/log/mysql/error.log: == BACKTRACE BEGIN == 2017-11-24T23:00:35.795018Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6825ms. The settings might not be optimal. (flushed=3 and evicted=0, during the time.) 2017-11-25 05:32:25 0x7fa6ecbbc700 InnoDB: Assertion failure in thread 140354913027840 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. 04:32:25 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=16777216 read_buffer_size=131072 max_used_connections=8 max_threads=214 thread_count=10 connection_count=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 101418 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 0x3 /usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xe8a93b] /usr/sbin/mysqld(handle_fatal_signal+0x489)[0x786749] /lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7fce1300a390] /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38)[0x7fce123c3428] /lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7fce123c502a] /usr/sbin/mysqld[0x75c31a] /usr/sbin/mysqld(_Z21pars_insert_statementP10sym_node_tPvP10sel_node_t+0x3a8)[0x1024088] /usr/sbin/mysqld(_Z7yyparsev+0x1227)[0x12455d7] /usr/sbin/mysqld(_Z8pars_sqlP11pars_info_tPKc+0x9e)[0x10258de] /usr/sbin/mysqld(_Z13fts_parse_sqlP11fts_table_tP11pars_info_tPKc+0x190)[0x12251e0] /usr/sbin/mysqld(_Z14fts_write_nodeP5trx_tPP10que_fork_tP11fts_table_tP12fts_string_tP10fts_node_t+0x292)[0x11ffa52] /usr/sbin/mysqld[0x12031d8] /usr/sbin/mysqld(_Z14fts_sync_tableP12dict_table_tbbb+0x329)[0x1209219] /usr/sbin/mysqld(_Z23fts_optimize_sync_tablem+0x42)[0x1210b62] /usr/sbin/mysqld(_Z19fts_optimize_threadPv+0x57c)[0x121a0bc] /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fce130006ba] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fce124953dd] 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. == BACKTRACE END == Server activity and configuration description: - The instance of MySQL is running on Ubuntu Server 16.04.3 LTS 64 bits with all updates applied. Complete version of the package is: 5.7.20-0ubuntu0.16.04.1. - The server has 160Gb of RAM, so I believe we can conclude this is not a problem of memory - That this server is not running any activity other than those replications, at all. What we can observe: - The backtrace is the same for all crashes we experienced. - Not sure whether this is linked, but we very frequently get the "[Note] Error reading relay log event for channel '': slave SQL thread was killed - [Note] Slave
