How MySQL removes temporary tables that begin with "#sql-"

Author:skate
Time:2014/09/28

How MySQL removes temporary tables that begin with "#sql-"

Symptom: After rebuilding the index, it is found that the MySQL server's disk space is almost full.

Rebuild the index with the following command

Mysql> ALTER TABLE Skatetab add unique index (ID, UID), drop primary key, add primary key (UID, id);

In the process of rebuilding the index, because of insufficient space, the MySQL server reboot, after the restart found that the space is less than 100G. Then see which directory is occupying this 100G, and finally found in the data directory
Many similar #sql-*.ibd temporary files and #sql-*.frm with the same file name. Since you know it is a temporary table, then delete it, certainly not directly through the RM deleted, because in the Ibdata to save the dictionary information and the undo letter
Data, the database restarts will be an error.

method to delete:

In the process of ALTER TABLE, if MySQL suddenly crash, there will be some intermediate tables in the data directory, these intermediate tables are "#sql-" the beginning of the temporary table, in your data directory will see
#sql-*.ibd and the corresponding #sql-*.frm, if #sql-*.ibd and #sql-*.frm two files exist in the data directory, you can drop table directly, similar to:

mysql> drop table ' #mysql50 # #sql -928_76f7 ';

The prefix "#mysql50 #" is the secure encoding for MySQL to ignore the file name, which is introduced in Mysql5.1

Because my data directory in #sql-*.ibd and #sql-*.frm Two files are saved, so the direct drop can be, disk space more than 100 g is also recycled, as shown below

mysql> drop table ' #mysql50 # #sql -928_76f7 ';
Query OK, 0 rows affected (16.28 sec)

Note: If there is only #sql-*.ibd in the data directory and there is no #sql-*.frm, special handling is required.
1. Create a table structure (including the same columns and indexes) as you want to delete the table in another data schema

Mysql> CREATE DATABASE Test
Mysql> CREATE table test.tmp like Skatetab; Copy only the structure and index of a table, without copying data

2. Copy the. frm file of the newly created temporary table into the data directory you want to delete, and modify the same file name as "#sql-*.ibd"

shell> CP test/tmp.frm #sql -928_76f7.frm

3. Confirm that the #sql-*.ibd and #sql-*.frm two files are saved and then drop directly, as follows:

mysql> drop table ' #mysql50 # #sql -928_76f7 ';

Reference: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html

------END------

How MySQL removes temporary tables that begin with "#sql-"