| Bug #109513 | Our PRD env mysql 8.0.30/8.0.31 server crashed several times a day | ||
|---|---|---|---|
| Submitted: | 3 Jan 2023 1:41 | Modified: | 3 Jan 2023 15:02 |
| Reporter: | Yuanjun Li | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.30, 8.0.31 | OS: | CentOS (CentOS Linux release 7.7.1908 (Core)) |
| Assigned to: | CPU Architecture: | x86 (Intel(R) Xeon(R) Gold 5220 CPU @ 2.20GHz) | |
[3 Jan 2023 1:48]
Yuanjun Li
here is mysql server's config file: [root@saas2 ~]# cat /etc/my4306.cnf [client] default-character-set=utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] #basedir=/data/mysql/data/ datadir=/data/mysql/data/4306 port=4306 user=mysql pid-file = /var/run/mysqld/mysqld4306.pid socket = /var/run/mysqld/mysqld4306.sock default-time-zone = '+08:00' lc_messages_dir=/usr/share/mysql-8.0/ lc_messages = en_US character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect='SET NAMES utf8mb4' skip-character-set-client-handshake = true max_connections = 5000 default-authentication-plugin=mysql_native_password innodb_file_per_table=1 skip-ssl explicit_defaults_for_timestamp #performance innodb_buffer_pool_size = 18800M innodb_log_file_size=512M innodb_log_files_in_group=4 max_heap_table_size = 256M tmp_table_size = 256M tmpdir=/data/mysql/data/temp4306 max_allowed_packet = 64M slow_query_log=1 slow_query_log_file = /var/log/mysql4306-slow.log log-error=/var/log/mysqld4306.log long_query_time = 2 #binlog binlogļ¼enable GTID server_id=32 gtid_mode=on enforce_gtid_consistency=on master_info_repository=table relay_log_info_repository=table binlog_checksum=none log_slave_updates=on log_bin=mysql-bin binlog_format=row relay-log=saas2-relay-bin #group replication transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="cb1e163d-94e7-47e2-880f-82a481xxxbcf" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "192.168.254.32:7606" loose-group_replication_group_seeds= "192.168.254.31:7606,192.168.254.32:7606" loose-group_replication_bootstrap_group= off
[3 Jan 2023 2:11]
Yuanjun Li
when we executed a select sql on this "problem" table, the server crashed too.
[3 Jan 2023 3:02]
Yuanjun Li
We also upgrade to 8.0.31 to verify, but no luck. here is a 8.0.31 server crashed log caused by a select sql: InnoDB: Next record offset is nonsensical 59222 in record at offset 0 InnoDB: rec address 0x7f4779ad4000, space id 129950, page 0 2023-01-03T02:54:25.757239Z 336 [ERROR] [MY-013183] [InnoDB] Assertion failure: page0page.ic:617 thread 139915640461056 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/8.0/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 2023-01-03T02:54:25Z UTC - mysqld got signal 6 ; Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. BuildID[sha1]=30a13d091dc0c69784b16dd4169f1382a73d2f9d Thread pointer: 0x7f402c000fc0 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 = 7f40a60c4c50 thread_stack 0x100000 /usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x2128b81] /usr/sbin/mysqld(print_fatal_signal(int)+0x387) [0xfd62d7] /usr/sbin/mysqld(my_server_abort()+0x7e) [0xfd642e] /usr/sbin/mysqld(my_abort()+0xe) [0x2122a9e] /usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x33a) [0x242277a] /usr/sbin/mysqld(page_cur_search_with_match_bytes(buf_block_t const*, dict_index_t const*, dtuple_t const*, page_cur_mode_t, unsigned long*, unsigned long*, unsigned long*, unsigned long*, page_cur_t*)+0x9a7) [0x2321a87] /usr/sbin/mysqld(btr_cur_search_to_nth_level(dict_index_t*, unsigned long, dtuple_t const*, page_cur_mode_t, unsigned long, btr_cur_t*, unsigned long, char const*, unsigned long, mtr_t*)+0x1fa4) [0x246ae74] /usr/sbin/mysqld(Row_sel_get_clust_rec_for_mysql::operator()(row_prebuilt_t*, dict_index_t*, unsigned char const*, que_thr_t*, unsigned char const**, unsigned long**, mem_block_info_t**, dtuple_t const**, mtr_t*, lob::undo_vers_t*)+0x113) [0x2395543] /usr/sbin/mysqld(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long)+0x204e) [0x239d5de] /usr/sbin/mysqld(ha_innobase::general_fetch(unsigned char*, unsigned int, unsigned int)+0x218) [0x2230ac8] /usr/sbin/mysqld(handler::ha_index_next_same(unsigned char*, unsigned char const*, unsigned int)+0x74) [0x10ed164] /usr/sbin/mysqld(RefIterator<false>::Read()+0xe6) [0x13b9f96] /usr/sbin/mysqld(FilterIterator::Read()+0x18) [0x13ab5b8] /usr/sbin/mysqld(Query_expression::ExecuteIteratorQuery(THD*)+0x29b) [0xf3f4cb] /usr/sbin/mysqld(Query_expression::execute(THD*)+0x30) [0xf3f840] /usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0x189) [0xecbbc9] /usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0xb32) [0xe68f02] /usr/sbin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x41a) [0xe6c88a] /usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xd4c) [0xe6db3c] /usr/sbin/mysqld(do_command(THD*)+0x207) [0xe6fe17] /usr/sbin/mysqld() [0xfc6280] /usr/sbin/mysqld() [0x28ec089] /lib64/libpthread.so.0(+0x814a) [0x7f485692214a] /lib64/libc.so.6(clone+0x43) [0x7f4854d8ff23] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f402c023d40): SELECT user_id FROM hr_interview_process_copy2 WHERE is_deleted = 0 AND interviewer_id = 14338 Connection ID (thread ID): 336 Status: NOT_KILLED
[3 Jan 2023 14:28]
MySQL Verification Team
Hi Mr. Li, Thank you for your bug report. However, we already have a bug that was verified and which has a fully repeatable test case, unlike yours. So, your bug is a duplicate of the following one: https://bugs.mysql.com/bug.php?id=108094 Since that bug is a Security Vulnerability bug, you can not view it. However, we have added a notice to that bug, so that your report is closed when that bug is fixed. It is not expected that the bug in question will be fixed very soon .......
[3 Jan 2023 15:02]
Yuanjun Li
so please give some workaround tips to help us to hold on until to the bug been fixed.
Description: ENV: Linux saas2 3.10.0-1062.18.1.el7.x86_64 #1 SMP Tue Mar 17 23:49:17 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux CentOS Linux release 7.7.1908 (Core) When a special insert into sql executed, the mysql server crashed. here is the logs: ``` 2023-01-03T00:15:00.726766+08:00 5423 [ERROR] [MY-013183] [InnoDB] Assertion failure: rec.cc:391 thread 140250822293248 16:15:00 UTC - mysqld got signal 6 ; Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. Thread pointer: 0x7f8db0199430 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 = 7f8eb0712bf0 thread_stack 0x100000 /usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x200098d] /usr/sbin/mysqld(print_fatal_signal(int)+0x2df) [0xf16ebf] /usr/sbin/mysqld(my_server_abort()+0x7e) [0xf1700e] /usr/sbin/mysqld(my_abort()+0xa) [0x1ffacba] /usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x31f) [0x22b509f] /usr/sbin/mysqld(rec_get_offsets(unsigned char const*, dict_index_t const*, unsigned long*, unsigned long, ut::Location, mem_block_info_t**)+0xb7) [0x22c17b7] /usr/sbin/mysqld(page_cur_search_with_match(buf_block_t const*, dict_index_t const*, dtuple_t const*, page_cur_mode_t, unsigned long*, unsigned long*, page_cur_t*, rtr_info* )+0x203) [0x21dc913] /usr/sbin/mysqld(btr_root_raise_and_insert(unsigned int, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t const*, mtr_t*)+0x6a8) [0x22e9c98] /usr/sbin/mysqld(btr_cur_pessimistic_insert(unsigned int, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t*, unsigned char**, big_rec_t**, que_thr_t*, mtr_t*)+0x46d ) [0x22ef60d] /usr/sbin/mysqld(btr_insert_on_non_leaf_level(unsigned int, dict_index_t*, unsigned long, dtuple_t*, ut::Location, mtr_t*)+0x2d7) [0x22df437] /usr/sbin/mysqld(btr_page_split_and_insert(unsigned int, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t const*, mtr_t*)+0xb4a) [0x22e7eca] /usr/sbin/mysqld(btr_cur_pessimistic_insert(unsigned int, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t*, unsigned char**, big_rec_t**, que_thr_t*, mtr_t*)+0x1d2 ) [0x22ef372] /usr/sbin/mysqld(row_ins_clust_index_entry_low(unsigned int, unsigned long, dict_index_t*, unsigned long, dtuple_t*, que_thr_t*, bool)+0xd53) [0x2209313] /usr/sbin/mysqld(row_ins_clust_index_entry(dict_index_t*, dtuple_t*, que_thr_t*, bool)+0xf6) [0x220ee56] /usr/sbin/mysqld(row_ins_step(que_thr_t*)+0x480) [0x220f8a0] /usr/sbin/mysqld() [0x221cd14] /usr/sbin/mysqld(ha_innobase::write_row(unsigned char*)+0x339) [0x2104eb9] /usr/sbin/mysqld(handler::ha_write_row(unsigned char*)+0x1f8) [0x102bb88] /usr/sbin/mysqld(write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*)+0x624) [0x12972e4] /usr/sbin/mysqld(Sql_cmd_insert_values::execute_inner(THD*)+0x841) [0x1298ca1] /usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0x189) [0xe05319] /usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0x9f0) [0xdac9c0] /usr/sbin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x41a) [0xdb06aa] /usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xd3e) [0xdb194e] /usr/sbin/mysqld(do_command(THD*)+0x207) [0xdb3b47] /usr/sbin/mysqld() [0xf07620] /usr/sbin/mysqld() [0x2511165] /lib64/libpthread.so.0(+0x7e65) [0x7f939210fe65] /lib64/libc.so.6(clone+0x6d) [0x7f939072a88d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f8db026c9e0): INSERT INTO t_sys_sms_log ( phone, content, send_time, server, msgSender, send_status, receive_status, batch_id ) VALUES ( '188745688xx', ' 6i.cn/7PPa', '2023-01-03 00:12 :29.0', 33, ' ', 0, 0, 1059625896660570112 ) Connection ID (thread ID): 5423 Status: NOT_KILLED 2023-01-03T06:43:05.024933+08:00 10096 [ERROR] [MY-013183] [InnoDB] Assertion failure: rec.cc:391 thread 140476123555584 22:43:05 UTC - mysqld got signal 6 ; Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. Thread pointer: 0x7fc2ace2c200 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 = 7fc325719bf0 thread_stack 0x100000 /usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x200098d] /usr/sbin/mysqld(print_fatal_signal(int)+0x2df) [0xf16ebf] /usr/sbin/mysqld(my_server_abort()+0x7e) [0xf1700e] /usr/sbin/mysqld(my_abort()+0xa) [0x1ffacba] /usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x31f) [0x22b509f] /usr/sbin/mysqld(rec_get_offsets(unsigned char const*, dict_index_t const*, unsigned long*, unsigned long, ut::Location, mem_block_info_t**)+0xb7) [0x22c17b7] /usr/sbin/mysqld(page_cur_search_with_match(buf_block_t const*, dict_index_t const*, dtuple_t const*, page_cur_mode_t, unsigned long*, unsigned long*, page_cur_t*, rtr_info* )+0x203) [0x21dc913] /usr/sbin/mysqld(btr_root_raise_and_insert(unsigned int, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t const*, mtr_t*)+0x6a8) [0x22e9c98] /usr/sbin/mysqld(btr_cur_pessimistic_insert(unsigned int, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t*, unsigned char**, big_rec_t**, que_thr_t*, mtr_t*)+0x46d ) [0x22ef60d] /usr/sbin/mysqld(btr_insert_on_non_leaf_level(unsigned int, dict_index_t*, unsigned long, dtuple_t*, ut::Location, mtr_t*)+0x2d7) [0x22df437] /usr/sbin/mysqld(btr_page_split_and_insert(unsigned int, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t const*, mtr_t*)+0xb4a) [0x22e7eca] /usr/sbin/mysqld(btr_cur_pessimistic_insert(unsigned int, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t*, unsigned char**, big_rec_t**, que_thr_t*, mtr_t*)+0x1d2 ) [0x22ef372] /usr/sbin/mysqld(row_ins_clust_index_entry_low(unsigned int, unsigned long, dict_index_t*, unsigned long, dtuple_t*, que_thr_t*, bool)+0xd53) [0x2209313] /usr/sbin/mysqld(row_ins_clust_index_entry(dict_index_t*, dtuple_t*, que_thr_t*, bool)+0xf6) [0x220ee56] /usr/sbin/mysqld(row_ins_step(que_thr_t*)+0x480) [0x220f8a0] /usr/sbin/mysqld() [0x221cd14] /usr/sbin/mysqld(ha_innobase::write_row(unsigned char*)+0x339) [0x2104eb9] /usr/sbin/mysqld(handler::ha_write_row(unsigned char*)+0x1f8) [0x102bb88] /usr/sbin/mysqld(write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*)+0x624) [0x12972e4] /usr/sbin/mysqld(Sql_cmd_insert_values::execute_inner(THD*)+0x841) [0x1298ca1] /usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0x189) [0xe05319] /usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0x9f0) [0xdac9c0] /usr/sbin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x41a) [0xdb06aa] /usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xd3e) [0xdb194e] /usr/sbin/mysqld(do_command(THD*)+0x207) [0xdb3b47] /usr/sbin/mysqld() [0xf07620] /usr/sbin/mysqld() [0x2511165] /lib64/libpthread.so.0(+0x7e65) [0x7fc8bb75ee65] /lib64/libc.so.6(clone+0x6d) [0x7fc8b9d7988d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7fc2adbadd40): INSERT INTO t_sys_sms_log ( phone, content, send_time, server, msgSender, send_status, receive_status, batch_id ) VALUES ( '191185577xx', ' 50.00 131871563xx 6i.cn/7P2N', '2023-01-03 06:43:05.019', 33, ' ', 0, 0, 1059723558336466944 ) Connection ID (thread ID): 10096 Status: NOT_KILLED ``` How to repeat: execute a special insert into sql repeatly. Suggested fix: no idea