A few weeks ago we had a power outage on one of our web servers that hosts several websites, we didn't think it affected anything and everything came back up as normal as far as we were aware. I have been analysing the logs over the past few days to try and optimise as I noticed increased load recently – after exploring the mysql .err file I can see continual loading error messages such as the following –
Cannot find or open table example_example/table1 from
the internal 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.5/en/innodb-troubleshooting.html
how you can resolve the problem.
We have tried the suggested link on MySQL but doesn't seem to be working.
We do not have backup of the databases from a couple of weeks ago, so not quite sure how to diagnose or resolve this but have come to a brick wall.
Any advice would be greatly appreciated.
Best Answer
The InnoDB dictionary contains information about all tables in your database(s). The dictionary is a set of InnoDB tables hidden from a user.
Looks like a records about example_example/table1 is missing in the dictionary.
I'd explore content of SYS_INDEXES and SYS_TABLES to see if the table is in it. https://twindb.com/how-to-recover-innodb-dictionary/