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