1

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.

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

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.
  • I added my original post with an answer. My mysql is behaving different to what you are writing (or I am doing something wrong) – Chris Mar 13 '15 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.