MySQL Bugs: #72135: Incomplete instructions for dealing with orphaned InnoDB temporary tables

Bug #72135	Incomplete instructions for dealing with orphaned InnoDB temporary tables
Submitted:	26 Mar 2014 16:37	Modified:	7 Apr 2014 17:47
Reporter:	Kolbe Kegel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any

Description:
"Troubleshooting InnoDB Data Dictionary Operations" at http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html makes this claim:

"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 “#”."

The problem with this is that a table called "#sql-ib371", for example, would have a .frm file with the name "@0023sql@002dib371.frm".

How to repeat:
n/a

Suggested fix:
In order to remove an orphaned InnoDB temporary table, you must use prefix the name with "#mysql50#" when executing the DROP TABLE statement, like this:

  DROP TABLE `#mysql50##sql-ib371`;

Hello Kolbe,

Thank you for the report.

Thanks,
Umesh

Posted by developer:
 
Orphaned temporary table instructions have been revised. The revised content will appear soon, with the next published documentation build.
Thank you for the bug report.

https://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html