MySQL Forums
Forum List  »  InnoDB

Orphaned temp tables in data dictionary
Posted by: Blake Harps
Date: March 19, 2013 06:26PM

Running 5.5.x with file per table enabled.

Background:
Started with a huge partitioned MyISAM table and attempted to convert it to InnoDB. MySQL server crashed mid-way and left a ton of #sql-XXX_xxxx#P#pXXX files in the database directory. I didn't notice the left over temp files right away and ended up eventually removing partitioning on the table and converting it to InnoDB.

I later realized that there were 100+ of the #sql-XXX_xxxx#P#pXXX.ibd files. After not finding a way to drop them or even reference them from within MySQL, I used a quick bash one liner to remove them.

Now I'm getting the following on start up:

130319 23:40:03 InnoDB: Error: table 'advertiser_XXX_database/#sql-944_9dfea#P#p102'
InnoDB: in InnoDB data dictionary has tablespace id 50445,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

If I create dummy #sql...ibd files and put them in place of the previously orphaned files I no longer get the errors on start up, but still cannot find a way to purge the reference to them from the data dictionary. Both dropping the table or discarding the tablespace work on the orphaned temp partitions, as the temp names are not valid table names.

Server is running fine other than the start up errors, so it's not hyper critical but I definitely want to figure out a way to resolve it.

Options: ReplyQuote


Subject
Views
Written By
Posted
Orphaned temp tables in data dictionary
4744
March 19, 2013 06:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.