How to get rid of orphaned InnoDB temporary tables

December 30, 2014 · by · in MariaDB, MySQL

If InnoDB or XtraDB in MySQL or MariaDB are interrupted while performing an ALTER TABLE, they’ll leave a temporary table sitting in your data directory. This thing will be taking up space, which is no good. If you’re usinginnodb-file-per-table, or course, you can solve that problem by removing the .frm and .ibd files for the table. Unfortunately, that’ll give you errors when you start up the server. So, how do you get rid of the temporary table without causing errors? The MySQL reference manual offers a solution, but it’s imcomplete and doesn’t work. This one does.

First, let’s set up a scenario to demonstrate what I’m talking about. I’m using an old version here to illustrate that this isn’t some new-fangled feature but rather something that’s been around for quite a while.

mysql 5.1.65 (root) [test]> alter table t1 add key (c1), add key(c2), add key(c3);
ERROR 2013 (HY000): Lost connection to MySQL server during query

$ ls -lh ./data/test/
total 1130544
-rw-rw----  1 kolbe  kolbe   8.4K Mar 26 17:57 #sql-f3be_1.frm
-rw-rw----  1 kolbe  kolbe    24M Mar 26 17:57 #sql-f3be_1.ibd
-rw-rw----  1 kolbe  kolbe   8.4K Mar 26 17:41 t1.frm
-rw-rw----  1 kolbe  kolbe   528M Mar 26 17:44 t1.ibd

There we can see the #sql-f3be_1.frm and #sql-f3be_1.ibd files created for the new table being created by ALTER TABLE.

However, if we try to drop this table, it doesn’t work:

mysql 5.1.65 (root) [test]> drop table `#sql-f3be_1`;
ERROR 1051 (42S02): Unknown table '#sql-f3be_1'

In fact, we can still create a table with that name:

mysql 5.1.65 (root) [test]> create table `#sql-f3be_1` (id int) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

And now we see these new files alongside the ones we had before:

-rw-rw----  1 kolbe  kolbe   8.4K Mar 26 18:06 @0023sql@002df3be_1.frm
-rw-rw----  1 kolbe  kolbe    96K Mar 26 18:06 @0023sql@002df3be_1.ibd

That’s because MySQL and MariaDB encode special characters for the filesystem. See my blog post “Demystifying Identifier Mapping” and The MySQL Reference Manual for more information about that.

The trick here is to prefix the tablename with #mysql50# to prevent the server from escaping the hash mark and hyphen:

mysql 5.1.65 (root) [test]> drop table `#mysql50##sql-f3be_1`;
Query OK, 0 rows affected (0.01 sec)

Et voilà, no more leftover temporary InnoDB junk in the datadir:

$ ls -lh ./data/test/
total 1081368
-rw-rw----  1 kolbe  kolbe   8.4K Mar 26 17:41 t1.frm
-rw-rw----  1 kolbe  kolbe   528M Mar 26 17:44 t1.ibd

If you’ve made the mistake of removing the .frm file for this temporary table, you might be seeing something like this in your error log:

140326 18:10:35  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
140326 18:10:35  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './test/#sql-f3db_2.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

Well, it’s very nice that this error message sends you to the reference manual, but even newer versions of the manual don’t offer correct advice for this situation. Wrong:

If MySQL crashes in the middle of an ALTER TABLE operation, you may end up with an orphaned temporary table inside the InnoDB tablespace. Using the Table Monitor, you can see listed a table with a name that begins with #sql-. You can perform SQL statements on tables whose name contains the character “#” if you enclose the name within backticks. Thus, you can drop such an orphaned table like any other orphaned table using the method described earlier. To copy or rename a file in the Unix shell, you need to put the file name in double quotation marks if the file name contains “#”.

We already saw what happens if you try that (“Unknown table”). I filed bug #72135 in hopes that the documentation can be improved.

If you find yourself in a situation where you’ve deleted the .frm file for the temporary table, you can copy any other InnoDB .frm file to have that name and drop the table as suggested above.

$ cp t1.frm "#sql-f3db_2.frm"

mysql 5.1.65 (root) [test]> drop table `#mysql50##sql-f3db_2`;
Query OK, 0 rows affected (0.00 sec)

And on server reboot, you should be free of those annoying messages.

  • Ask Question
    • Tags: , ,