Mysql – RDS MYSQL instance rebooted and won’t load INNODB table

amazon-rdscorruptioninnodbMySQLrecovery

So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error in the logs.

140324 16:22:23 [ERROR] Cannot find or open table dbname/table_name 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.

The problem is that no one noticed for three days that the table was missing and therefore my automated snapshots are useless (I only retain them that long) as all the snapshots have the same problem.

During my attempts to fix things I have had indications from the db that indicate that the .frm and .ibd files are all inplace. The show tables command lists the table (so frm is there), and I get the error below if I (on a copy) drop the table and try and recreate it.

140324 16:46:05 InnoDB: Error creating file './dbname/table_name.ibd'.
140324 16:46:05 InnoDB: Operating system error number 17 in a file operation.
InnoDB: Error number 17 means 'File exists'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: The file already exists though the corresponding table did not
InnoDB: exist in the InnoDB data dictionary. Have you moved InnoDB
InnoDB: .ibd files around without using the SQL commands
InnoDB: DISCARD TABLESPACE and IMPORT TABLESPACE, or did
InnoDB: mysqld crash in the middle of CREATE TABLE? You can
InnoDB: resolve the problem by removing the file './dbname/table_name.ibd'
InnoDB: under the 'datadir' of MySQL.

I suspect there was some issue with an index before the instance was restarted, or if it indeed crashed then perhaps that caused a corruption.

I managed to recreate the data (probably) from logs though it's only a guess, not a sure thing.

So I'm asking what I should do at this point. I'd love to find a solution that allows me to get that table back with the data it had, but if I have to delete it and recreate it that's also ok. Obviously I can't touch any mysql data files as it's an rds instance. Mysql version is 5.5.27.

Best Answer

You need to open the case with AWS Support team here. As only they have direct access to the server consoles and only they can help you to perform required operations as suggested in the output of attempt to recreate table.

Alternatively you can create the table with new name, as a possible workaround for the problem.