​DDL Failures? How to Clean Up Intermediate Table Residues in MySQL

​Fault Phenomenon​

Version: Community Edition 5.7.21

Data directory contains intermediate table files, yet no active DDL processes exist. Affected tables cannot execute Online DDL.

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

‍

Root Cause Analysis​
The issue likely stems from an aborted DDL operation in the past, leaving behind orphaned intermediate files that block subsequent Online DDL executions.

​Resolution Steps​
Refer to the official documentation for standardized procedures:

1. Identify Orphaned Tables​
Query the InnoDB system tables to locate residual entries:

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

‍

​2. Drop the Residual Table

‍​Use the identified identifier to remove the orphaned table directly:

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

‍

​Alternative Scenarios & Solutions​

• ​Missing .frm File: Recreate the table structure via SHOW CREATE TABLE, then drop it.
• ​Both .frm and .ibd Deleted: Restart MySQL to regenerate the .ibd file. Use tools like innodb-undrop to recover the schema if needed. Note: Restart may log errors but won’t disrupt services.
• ​Schema Mismatch: Rename inconsistent files to align their identifiers before dropping. Example:

mv #sql-1076b_15b58.frm #sql-ib1814-561096274.frm  
DROP TABLE `#mysql50##sql-1076b_15b58`;  

‍

Key Takeaways​

• Always simulate fixes on a Secondary replica before applying to Primary.
• Avoid manual file deletions; use MySQL-native commands to prevent metadata inconsistencies.
• Refer to MySQL’s error logs (e.g., ibd file invalidation errors) for troubleshooting clues.

For deeper insights, consult the official guide:
InnoDB Data Dictionary Troubleshooting

‍