How to Fix MySQL Online DDL Failures Caused by Stale Temporary Tables

1. Symptom

Version: Community Edition 5.7.21

The data directory contains intermediate table files; no DDL is running, yet related tables cannot perform online DDL.

[root commercial_push_1]# ls
'#sql-13fd_3.frm'
'#sql-13fd_3.ibd'

2. Analysis

It is likely that a previous DDL failure left the temporary table files undeleted, preventing the table from executing online DDL.

3. Resolution

Refer to the official documentation for the correct handling method:

Inspect system tablespace

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

Adding the identifier and executing DROP resolves the issue.

DROP TABLE `#mysql50##sql-13fd_3`;

Other handling methods:

If the .frm file was mistakenly deleted, regenerate the table structure via sys_columns , then CREATE and drop it.

If both .frm and .ibd were deleted, a restart will auto‑generate the .ibd; use tools such as undrop for innodb to recover the schema before dropping.

If .frm and .ibd are inconsistent, rename the files to matching names and then drop the table.

Example case from a user:

[root xxx]# ls
-rw-r----- 1 mysql mysql 15196 Mar 20 15:13 '#sql-1076b_15b58.frm'
-rw-r----- 1 mysql mysql 131072 Mar 20 15:15 '#sql-ib1814-561096274.ibd'
# mv '#sql-1076b_15b58.frm' '#sql-ib1814-561096274.frm'
MySQL [xxx]> DROP TABLE `#mysql50##sql-1076b_15b58`;
Query OK, 0 rows affected (0.0362 sec)

Summary

Initially I tried removing the files directly, which caused error‑log entries after a restart; using rename on the replica can also solve the issue.

The error‑log links often point to official documentation that provides reliable solutions.

Always reproduce the issue on a replica first; never execute destructive commands on the primary.

Reference

[1] innodb‑troubleshooting‑datadict: http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html