MySQL – Cannot find or open table from the internal data dictionary of InnoDB

This error usually occurs when deleting database and then creating new database with same name. With InnoDB engine usually the database and all it’s contents gets removed but the same exists in InnoDB internal directory which creates error while trying to restore new database with same name.

[ERROR] Cannot find or open table from the data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? or, the table contains indexes that this version of the engine doesn’t support. See

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

Now if you try to remove database from server ‘/var/lib/mysql’ and again try to restore you will still get following error.

InnoDB: Error: table `database`.`accesslog` already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

To fix this error you need to restore old database from backup to a newly created database and then copy .frm files to the database having issue. From here drop the tables, what happens here is MySQL thinks that the table exists and deletes the same from it’s internal data directory. Once this is done you may restore the database and now you will not face any error.

Alternatively, to fix this error you can restore database with another name and then change the corresponding connection string in your application which uses this database.

Total
0
Shares
Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts