Can't Delete Orphaned MySQL Temp Table

Question

One of my servers had a hick-up yesterday and decided to leave temporary table while (while running alter table add indexes).

ls -l *sql*
-rw-rw---- 1 mysql mysql  8570 Mar 13 12:05 #sql-ib32694.frm
-rw-rw---- 1 mysql mysql 98304 Mar 13 12:05 #sql-ib32694.ibd


mysql> drop table `#mysql50##sql-ib32694`;
ERROR 1051 (42S02): Unknown table 'hdb.#mysql50##sql-ib32694'

Running the alter table on the original table again:

ERROR 1050 (42S01) at line 1: Table 'hdb/#sql-ib32694' already exists

???

I followed all the instructions here:

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

also tried innodb_force_recovery = 4

So I deleted those files manually (getting the typical entry in error.log for missing tablespace), recreated them in another DB, copied them over to this and nothing changes.

No success. I had some issues like that some time ago and I managed to remove it. This tiny little table is really stubborn though.

---

I can delete it, but then I get the same error message like for orphaned tables (in the error.log)

Could not find a valid tablespace file for 'hdb/#sql-ib32694' ...

Also - different to what you are writing here

Don't worry about the temp table name being used again. Look at the name of the temp table

#sql-7a87_230c32.ibd

There are 10 hexadecimal digits. The chances of reusing that temp table name is 1 in 1610 or 240 which 1,099,511,627,776. I think you will be OK in this.

when I do the ALTER TABLE on the table I ran it on what created the temp file, I get the error message (after I deleted the table)

ERROR 1050 (42S01) at line 1: Table 'hdb/#sql-ib32694' already exists

so it seems MySQL is using the same temp table name when you do an alter table.

No master/slave here.

Answer 1

Since it is a temp table, you are OK to just delete the files.

rm -f *#sql-ib32694.*

Is this harmful to InnoDB ? No. There was a data dictionary entry still inside ibdata1. However, that entry is either missing or invalid (due to the tablespace_id). There is actually a one in 1,099,511,627,766 chance of it causing a problem.

See my older posts on why it is not that harmful and how to deal with

• Jun 04, 2014 : temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave
• Dec 23, 2014 : #1025 - Error on rename of table errno: 150 : Table was deleted while tried to assign foreign key

Please keep in mind that temp tables (regardless of storage engine) are transient in nature. A temp table will disappear under these circumstances:

• When a query is finished using it
• When a DB connection terminates normally
• When a DB connection terminates abnormally

If a temp table still exists due to a crash, rest assured that the data dictionary no longer acknowledges its existence. Please delete them and keep a good conscience.

If you are worried about, your only recourse when this happens is to perform a fully InnoDB Cleanup. See my post Howto: Clean a mysql InnoDB storage engine? in StackOverflow. The lesson here

• Use CREATE TEMPORARY TABLE (...) ENGINE=MyISAM; if you have to use temp tables in your app.
• Any system-generated InnoDB temp tables can be deleted after a crash.