While trying to upgrade a database (in place) I got the following error. The server starts up and everything seems to be fine, except that the table mentioned in the message is no longer accessible.
This is Oracle MySQL "5.5.17-log MySQL Community Server (GPL)"
InnoDB: Error: trying to load index <FF>the_idx for table db/the_table
InnoDB: but the index tree has been freed!
141126 15:41:02 [ERROR] Cannot find or open table db/the_table 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.
I made a backup using innobackupex before trying the upgrade, but unfortunately if I try to "prepare" the backup it crashes with signal 6 and I suspect it's also crashing on the same table.
So I've been searching around here and elsewhere and have not found very much about how to recover from this sort of thing. Obviously I'd like to recover the data from this table. The message seems to imply that an index is corrupt so maybe the rest is OK? I can't run CHECK TABLE on it, since MySQL says the table doesn't exist.
I discovered innochecksum, but unfortunately that doesn't like the file either:
# innochecksum -d db/the_table.ibd
file db/the_table.ibd = 3749707776 bytes (228864 pages)...
checking pages in range 0 to 228863
page 0: log sequence number: first = 1383255984; second = 62128266
page 0 invalid (fails log sequence number check)
I'm not sure if that has anything to do with me running it on a separate server after trying to do the innobackupex --apply-log, though.
I have not yet tried innodb_force_recovery because I want to get a better understanding of what went wrong and how I might fix it first to avoid making things worse.
So what's the best course of action? Set innodb_force_recovery=1 (and then 2, 3, etc. until it works?) and see if I can dump the table? Will I be able to drop the table and reload it with innodb_force_recovery=1?
I've also just discovered http://www.percona.com/docs/wiki/innodb-data-recovery-tool%3astart and am having a look at it.
Thanks.
