Description:
MySQL server hangs when run the following script after 10 minutes. After 600s, mysql server gone away
2025-08-13T14:58:58.090812+08:00 825 [ERROR] [MY-012006] [InnoDB] Waited for 590 secs for hash index ref_count (2) to drop to 0. index: "idx_random8" table: "test_db/test_table"
2025-08-13T14:59:03.184278+08:00 825 [ERROR] [MY-012006] [InnoDB] Waited for 595 secs for hash index ref_count (2) to drop to 0. index: "idx_random8" table: "test_db/test_table"
2025-08-13T14:59:08.211452+08:00 825 [ERROR] [MY-012006] [InnoDB] Waited for 600 secs for hash index ref_count (2) to drop to 0. index: "idx_random8" table: "test_db/test_table"
2025-08-13T14:59:08.211588+08:00 825 [ERROR] [MY-013183] [InnoDB] Assertion failure: btr0sea.cc:295:sleep_counter < 60000 thread 140284842141440
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.
2025-08-13T06:59:08Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=6ccc406237347096d78af993fed3ca570a5ac52b
Thread pointer: 0x7f95e8007e50
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 = 7f969c2ed9b0 thread_stack 0x100000
/u01/zzh/mysql8041/install/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x43) [0x4ae8205]
/u01/zzh/mysql8041/install/bin/mysqld(print_fatal_signal(int)+0x3a2) [0x36c43ff]
/u01/zzh/mysql8041/install/bin/mysqld(my_server_abort()+0x6b) [0x36c46b1]
/u01/zzh/mysql8041/install/bin/mysqld(my_abort()+0xd) [0x4adebd3]
/u01/zzh/mysql8041/install/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x1d1) [0x4ee886d]
/u01/zzh/mysql8041/install/bin/mysqld(btr_search_await_no_reference(dict_table_t*, dict_index_t*, bool)+0x1e8) [0x4f7a373]
/u01/zzh/mysql8041/install/bin/mysqld() [0x5065780]
/u01/zzh/mysql8041/install/bin/mysqld(dict_index_remove_from_cache(dict_table_t*, dict_index_t*)+0x28) [0x5065ad6]
/u01/zzh/mysql8041/install/bin/mysqld() [0x50e84db]
/u01/zzh/mysql8041/install/bin/mysqld(ddl::drop_indexes(trx_t*, dict_table_t*, bool)+0x243) [0x50e8758]
/u01/zzh/mysql8041/install/bin/mysqld() [0x4bcd041]
/u01/zzh/mysql8041/install/bin/mysqld(bool ha_innobase::commit_inplace_alter_table_impl<dd::Table>(TABLE*, Alter_inplace_info*, bool, dd::Table*)+0xffa) [0x4bef422]
/u01/zzh/mysql8041/install/bin/mysqld(ha_innobase::commit_inplace_alter_table(TABLE*, Alter_inplace_info*, bool, dd::Table const*, dd::Table*)+0x19a) [0x4bc613c]
/u01/zzh/mysql8041/install/bin/mysqld(handler::ha_commit_inplace_alter_table(TABLE*, Alter_inplace_info*, bool, dd::Table const*, dd::Table*)+0xd9) [0x3868c51]
/u01/zzh/mysql8041/install/bin/mysqld() [0x357359c]
/u01/zzh/mysql8041/install/bin/mysqld(mysql_alter_table(THD*, char const*, char const*, HA_CREATE_INFO*, Table_ref*, Alter_info*)+0x3c06) [0x357f582]
/u01/zzh/mysql8041/install/bin/mysqld(Sql_cmd_create_or_drop_index_base::execute(THD*)+0x1b3) [0x3bb7563]
/u01/zzh/mysql8041/install/bin/mysqld(mysql_execute_command(THD*, bool)+0x2156) [0x3495d52]
/u01/zzh/mysql8041/install/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x798) [0x349b394]
/u01/zzh/mysql8041/install/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x16ed) [0x34910ec]
/u01/zzh/mysql8041/install/bin/mysqld(do_command(THD*)+0x5bd) [0x348f037]
/u01/zzh/mysql8041/install/bin/mysqld() [0x36af4ad]
/u01/zzh/mysql8041/install/bin/mysqld() [0x56b094a]
/lib64/libpthread.so.0(+0x7ea5) [0x7f981ee45ea5]
/lib64/libc.so.6(clone+0x6d) [0x7f981d8f0b0d]
The show processlist results is
mysql> show processlist;
+-----+------+-----------------+---------+---------+------+------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------------+---------+---------+------+------------------------------------------+------------------------------------------------------------------------------------------------------+
| 14 | zzh | localhost | test_db | Query | 0 | init | show processlist |
| 816 | zzh | localhost:45592 | test_db | Query | 566 | Waiting for table metadata lock | alter table test_table DROP INDEX idx_random5 |
| 821 | zzh | localhost:45602 | test_db | Query | 566 | Waiting for table metadata lock | alter table test_table DROP INDEX idx_random5 |
| 825 | zzh | localhost:45610 | test_db | Query | 613 | committing alter table to storage engine | CREATE INDEX idx_random8 ON test_table (field3(100),field4(100),field8(100)) |
| 846 | zzh | localhost:45652 | test_db | Query | 600 | Waiting for table metadata lock | CREATE INDEX idx_random6 ON test_table (field3,field4) |
| 875 | zzh | localhost:45714 | test_db | Query | 561 | Waiting for table metadata lock | alter table test_table add column add_char1 varchar(512) default NULL |
| 887 | zzh | localhost:45738 | test_db | Query | 551 | Waiting for table metadata lock | ALTER TABLE test_table ADD COLUMN virtual_col INT AS (field1 + field2) |
| 899 | zzh | localhost:45762 | test_db | Query | 540 | Waiting for table metadata lock | SELECT COUNT(*)
FROM test_table
WHERE DATE_FORMAT(field6, '%H:%i:%s') != f |
| 903 | zzh | localhost:45774 | test_db | Query | 533 | Waiting for table metadata lock | UPDATE test_table SET field5 = '2023-07-18', field6 = '2023-07-18 07:35:14', field7 = '07:35:14' WHE |
| 905 | zzh | localhost:45778 | test_db | Query | 527 | Waiting for table metadata lock | CREATE INDEX idx_random9 ON test_table (field9 desc) |
| 908 | zzh | localhost:45784 | test_db | Query | 522 | Waiting for table metadata lock | alter table test_table add column (add_char varchar(512) default '213rrrrrrrrrrdfc2ce4dd323232323232 |
| 915 | zzh | localhost:45798 | test_db | Query | 507 | Waiting for table metadata lock | CREATE INDEX idx_random8 ON test_table (field3(100),field4(100),field8(100)) |
+-----+------+-----------------+---------+---------+------+------------------------------------------+------------------------------------------------------------------------------------------------------+
12 rows in set, 1 warning (0.00 sec)
How to repeat:
Run the following script
nohup sh randomTransaction.sh <host> <user> <port> <password> <thread_num> &
