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.