Mysql – Can’t Delete Orphaned MySQL Temp Table

innodbMySQLmysql-5.6temporary-tables

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.

Best Answer

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.

Related Solutions

Mysql – ERROR Cannot find or open table

I've spent a bit of time trying to reproduce the error on the partition scheme, but cannot get the exact error with the orphaned table

120927 16:57:04 [ERROR] Cannot find or open table reportingdb/v3_zone_date_cpm7k#P#pcurrent_2012926 from the internal data dictionary of InnoDB though the .frm file for the table exists.

By moving the .ibd file for the partition out of the data directory (which it seems somehow has happened), I get an expected error:

[ERROR] MySQL is trying to open a table handle but the .ibd file for table foo/v3_zone_date_cpm7k#P#pcurrent_2012822 does not exist.

From a chat discussion I know you have an outdated backup file. Barring actually being able to force drop the partition 'pcurrent_2012926' (some data loss), the steps to restore this backup are as follows (a month worth of data loss unfortunately):

Take a backup of your main server (just in case!)
Restore the backup on a different server
Take a mysqldump of the table: mysqldump -uuser -p reportingdb v3_zone_date_cpm7k > v3_zone_date_cpm7k.sql
copy v3_zone_date_cpm7k.sql to the main server
On the main server, attempt to do this: DROP TABLE reportingdb.v3_zone_date_cpm7k
If that works, import your dumpfile: mysql -uuser -p reportingdb < v3_zone_date_cpm7k.sql which should restore that table (with a month-old table)
If the DROP TABLE does not work, try moving the v3_zone_date_cpm7k.frm and other files to a different location and restarting the server. Then import the dump file

The last step is in regards to the error message telling you you have an orphaned table:

This means that there is an orphaned .frm file without a corresponding table inside InnoDB. You can drop the orphaned .frm file by deleting it manually. [src]

I really hope this is not necessary and you can restore the partition by another means. This is a last resort method.

I finally reproduced your initial error. Though it will do little to restore the partition, it might be helpful to understand to keep this from happening in the future (potential issue in how backup/restore process is handled, or how the partitions are created):

I copied the v3_zone_date_cpm7k to a different location (as a backup).
issue a DROP TABLE v3_zone_date_cpm7k
copy the v3_zone_date_cpm7k files BACK to the datadir

desc v3_zone_date_cpm7k;

ERROR 1146 (42S02): Table 'v3_zone_date_cpm7k' doesn't exist

[ERROR] Cannot find or open table v3_zone_date_cpm7k#P#p00 from the internal data dictionary of InnoDB though the .frm file for the table exists.

Mysql – temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave

I hope I can answer this adequately because I hate temp tables that use InnoDB. Here is why

InnoDB Architecture

In this diagram, the data dictionary has that temp table as an entry. Once the temp table is done being used for ALTER TABLE, the data dictionary entry should disappear.

Here are your questions and my answer for them ...

1.. When alter has been successfully done then why this temp table still exist and not deleted.

Under normal circumstances, when a DB Connection terminates, all open temp tables are supposed to be dropped. The only reason a temp table will linger after an ALTER TABLE is done is if the DB Connection running the ALTER TABLE terminates before the temp table can be dropped. It is possible that the rollback segments (See Diagram) has the original info of the table before the ALTER TABLE was issued.

Since you mentioned this as happening on the Slave, the only suspect (or person of interest) for this problem would be the SQL thread for MySQL Replication. Why? It is the owner of the temp tables need to complete the SQL being executed from the relay logs. If you kill a SQL thread in a manner other than STOP SLAVE; while it is performing an ALTER TABLE, it may not drop temp tables in an orderly manner. Thus, you will have a lingering temp table regardless of the storage engine.

2.. Now how can I delete this temp table and what will be its impact.

You could go ahead and delete the .frm and .ibd. I only say this because it is temp table. The table should have disappeared. The impact ? The data dictionary will still have the temp table registered. Just a few bytes get wasted.

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 ₁₆10 or ₂40 which 1,099,511,627,776. I think you will be OK in this.

Don't delete them just yet. Look at the your third question ...

3.. May I read this temp file or know by any command that for which table this temp table was created.

I understand the need for this question. Evidently, you want to know what the table was that was having the ALTER TABLE done. Unfortunately, you cannot read any temp table through standard SQL.

However, you could probably rename the .frm and use a utility to read the table structure.

Here is an old post of mine to do this with a .frm in the Windows environment : How can extract the table schema from just the .frm file?

EPILOGUE

Even though the temp table no longer has a useless purpose at this point, deleting the InnoDB temp table there is that 1 in trillion chance that InnoDB be behave erratically.

Your last resort when this happens is to perform a fully InnoDB Cleanup. See my post Howto: Clean a mysql InnoDB storage engine? in StackOverflow

UPDATE 2014-06-10 08:36 EDT

It should be evident from the file timestamps and the commentary in your second update that a shutdown must have taken place in the middle of an ALTER TABLE. All transactional information for MVCC regarding the InnoDB table was held in a state of suspended animation in one of the following places:

Transaction Logs (a.k.a. Redo Logs, which are the files ib_logfile0 and ib_logfile1),
Rollback Segments & Undo Space (located in the system tablespace file ibdata1)
Double Write Buffer (located in the system tablespace file ibdata1)

When you started mysql back up, the MVCC for the table was replayed to bring the original table into a consistent state. That may result in either the table being rolled back to the original state before the ALTER TABLE or if everything passed through the Redo Logs, the table now existing in the new design. The fact that the .ibd file has a later timestamp show that MVCC must have been applied to it on the startup of mysql (which includes crash recovery).

Once the shutdown happened, the InnoDB table lost its identity as a temp table and became a regular table in the eyes of mysqld. Thus, you should be able to delete the temp files with impunity.