You're right, it shouldn't happen in a professional 21st century database product. It's allowed by an architectural flaw in InnoDB for which there's no universal fix.

The simplest remedy if you just want the orphaned table gone: create an identical table in another database, stop the server, copy the .frm file to the database where it's missing, restart the server, drop the table.

The simplest remedy if you want the orphaned table but not its data: create an identical table in another database, stop the server, move the .frm file to the database where it belongs, restart the server.

The simplest remedy if you need to restore the orphaned table with its data intact: copy any database changes made since the last backup of the problem database to a temporary database, drop the database, restore it from the backup, then copy back in the changes you just saved off.

But these simple fixes can fail, especially if .frm files have gone missing and no backups of them exist. The MySQL error log may report assertion failures. If such errors prevent the server from starting, you may have to traverse one of two versions of hell:

- use the InnoDB Data Recovery Tool to recover your data (http://code.google.com/p/innodb-tools/), or

- set innodb_force_recovery=1, restart the server, run mysqldump to back up all databases that need to be kept, stop the server, abandon the current data folder, initialise a new MySQL data folder, and import the data there.

More on such nightmares at ...

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

http://stackoverflow.com/questions/6804397/how-do-i-recreate-an-frm-file-for-an-mysql-innodb-table-with-only-the-ibdata-and

And here is a an example solution for the opposite problem, tablespace info gone missing for an existing InnoDB table:

https://www.percona.com/blog/2013/11/05/how-to-recover-an-orphaned-ibd-file-with-mysql-5-6

Last updated 16 Aug 2024