Bug #109513 Our PRD env mysql 8.0.30/8.0.31 server crashed several times a day
Submitted: 3 Jan 1:41 Modified: 3 Jan 15:02
Reporter: Yuanjun Li Email Updates:
Status: Duplicate Impact on me:
None 
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 1:41] Yuanjun Li
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
[3 Jan 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 2:11] Yuanjun Li
when we executed a select sql on this "problem" table, the server crashed too.
[3 Jan 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 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 15:02] Yuanjun Li
so please give some workaround tips to help us to hold on until to the bug been fixed.