When optimizing a large MySQL table (e.g., ~300GB), the database server might hang during the final step of swapping the old table with the optimized one. If this hang persists beyond 600 seconds, MySQL intentionally crashes to prevent prolonged instability.
Typical log entries indicating the issue:
---TRANSACTION 60682931334, ACTIVE 11095 sec dropping table
mysql tables in use 1, locked 5
7 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 18
MySQL thread id 96996, OS thread handle 139653211567872, query id 46443818637 localhost root committing alter table to storage engine
optimize table access
Trx read view will not see trx with id >= 60682931343, sees < 60682931343
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
80 read views open inside InnoDB
78 RW transactions active inside InnoDB
---OLDEST VIEW---
Read view low limit trx n:o 60682931325
Trx read view will not see trx with id >= 60682931343, sees < 60682931343
Read view individually stored trx ids:
-----------------
Process ID=1904, Main thread ID=139654330820352 , state=enforcing dict cache limit
Number of rows inserted 858616327, updated 169844377, deleted 765769299, read 3272705447940
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 223, updated 39216, deleted 200, read 133966852
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
InnoDB: ###### Diagnostic info printed to the standard error stream
2025-07-11T18:07:57.321836Z 0 [ERROR] [MY-012872] [InnoDB] [FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2025-07-11T18:07:57.322630Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: srv0srv.cc:2076:ib::fatal triggered thread 139654339213056
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-07-11T18:07:57Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=5a1aae544b3a0658cd01b26db7993c903df70d7a
Server Version: 8.0.35-27 Percona Server (GPL), Release '27', Revision '2f8eeab2'$
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 0x30000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x55588b2fa361]
/usr/sbin/mysqld(print_fatal_signal(int)+0x3b3) [0x55588a348b93]
/usr/sbin/mysqld(my_server_abort()+0x76) [0x55588a348d26]
/usr/sbin/mysqld(my_abort()+0xe) [0x55588b2f40ce]
/usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x349) [0x55588b5a12e9]
/usr/sbin/mysqld(ib::fatal::~fatal()+0xd5) [0x55588b5a3ae5]
/usr/sbin/mysqld(srv_error_monitor_thread()+0x6f3) [0x55588b528b23]
/usr/sbin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, void (*)()> > >::_M_run()+0xc4) [0x55588b427ed4]
/lib/x86_64-linux-gnu/libstdc++.so.6(+0xd6df4) [0x7f2c37056df4]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8609) [0x7f2c37593609]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43) [0x7f2c36d43353]
Please help us make Percona Server better by reporting any
bugs at https://bugs.percona.com/
You may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.
Upon restarting, the log might show repeated warnings about missing .ibd files:
2025-07-11T18:09:26.277646Z 134 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `DBNAME`.`access` because the .ibd file is missing.
Querying the affected table results in:
ERROR 1812 (HY000): Tablespace is missing for table `DBNAME`.`access`.Fortunately, if the .ibd file is still present in the data directory, the table can often be recovered without restoring from backups or failing over to a replica:
ALTER TABLE access IMPORT TABLESPACE;Note: This operation can take considerable time. For a 307GB table:
Query OK, 0 rows affected, 1 warning (36 min 23.67 sec)- Plan optimization operations during low-traffic periods to minimize impact.
- Ensure sufficient disk I/O and server resources before performing large optimizations.
- Regularly monitor MySQL logs for early detection of issues.
- Always verify the existence of critical
.ibdfiles before attempting recovery.