Issue with startup: trying to load index for temp table

[carshann]

Hi guys,

InnoDB: Error: trying to load index PRIMARY for table tmp/#sqlb96_11856_13e
InnoDB: but the index tree has been freed!
InnoDB: Index is corrupt but forcing load into data dictionary
InnoDB: Error: trying to load index “idx_cell_email_message_group_id” for table “tmp”."#sqlb96_11856_13e"
InnoDB: but the first index is not clustered!

I had an issue recently on one of our reporting servers where a reset (potentially whilst performing alters) occurred due to the server being completely starved of memory. In the process there were a few corrupted (normal) tables, and the temporary table above. I’ve managed to fix the other issues, but that one, I just can’t for the life of me fix.

I keep being redirected back to the InnoDB recovery page on MySQL (http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html), however the section on temporary tables doesn’t say much short of looking through the table monitor.

I’ve:
[LIST]
[]checked the filesystem to just in case the temp table existed on disk somewhere
[]checked the full output of InnoDB Table Monitor (as instructed in the URL above, however there was no mention of this table in its output)
[]tried creating a temporary table (as a long shot, and I’m guessing completely incorrect), with the same name.
[]tried innodb_force_recovery
[/LIST] Is there anything else I can do here?

Thanks, in advance!

[niljoshi]

Hi,

Can you provide what exact MySQL/PS version you are using? there was some bug related to this issue and it was resolved in earlier version.
http://docs.oracle.com/cd/E17952_01/mysql-5.1-relnotes-en/news-5-1-63.html
[LIST]
[*]InnoDB: If the server crashed during a TRUNCATE TABLE or CREATE INDEX statement for an InnoDB table, or aDROP DATABASE statement for a database containing InnoDB tables, an index could be corrupted, causing an error message when accessing the table after restart:

InnoDB: Error: trying to load index index_name for table table_name InnoDB: but the index tree has been freed! In MySQL 5.1, this fix applies to the InnoDB Plugin, but not the built-in InnoDB storage engine. (Bug #12861864, Bug #11766019)
[/LIST]
Not sure, the same thing is with you. Btw: have you tried all [COLOR=#252C2F]innodb_force_recovery and check?

[carshann]

Hi niljoshi,

Thanks for getting back to me! This was using Percona Server version: 5.5.33-31.1-log Percona Server (GPL), basically the last server to upgrade before moving our entire infrastucture to Percona 5.6

I’d tried all the innodb_force_recovery options, but it there something I’m supposed to do when using these that isn’t automatically fixed/corrected as part of starting with the option enabled? It didn’t appear to fix the issue.

[carshann]

Hmm, I’ve tried a few more things, but can’t seem to get rid of the startup message (stopping me from updating to Percona 5.6, yet 5.5 runs fine regardless). This bug appears to shed more light on the issue, but hasn’t directly helped solving it as yet:
http://bugs.mysql.com/bug.php?id=45976

Could you think of anything else that might help the issue?