Need some major help recovering some InnoDB tables

[Coop1979]

Hi all -

I tried an update from the 5.5.x branch of Percona to the 5.6.x branch and it has gone horribly for me. After much strife I was able to get the 5.6.x branch to start properly, but then it wouldn't read my InnoDB tables (which is pretty much all of my tables).

I'm now trying to get 5.5.x running again so that I can do a mysqldump of the databases and just import them into the 5.6.x install, but I can't even get 5.5.x running anymore. It's giving me scary messages in the error log like this when I try to start it:

141210  0:42:54  InnoDB: Warning: table 'pc_xenforo/xf_news_feed'
InnoDB: in InnoDB data dictionary has unknown flags 50.
InnoDB: Dump of the tablespace extent descriptor:  len 40; hex 000000000000000200000000022effffffff000000000004aaaaaaaaaaaaaafeabaaaaeaaaaaebae; asc              .                          ;
InnoDB: Serious error! InnoDB is trying to free page 799
InnoDB: though it is already marked as free in the tablespace!
InnoDB: The tablespace free space info is corrupt.
InnoDB: You may need to dump your InnoDB tables and recreate the whole
InnoDB: database!

Can anyone like @eva2000 help? I'm running OS X with Homebrew providing the packages. Willing to compensate well to get my site back up.

 

[eva2000]

Unfortunately I am to busy with other client server optimisation paid work right now. The error you got is because of changes introduced in MySQL 5.6.16 and it's impact on downgrades.

But try this in SSH command line which for InnoDB tables maps to ALTER TABLE command:

mysql -u mysqlusername -p -e "OPTIMIZE TABLE pc_xenforo.xf_news_feed;"

Assuming

• pc_xenforo is your database name and xf_news_feed is your affected table name
• mysqlusername = mysql username and prompt for password is mysqlusername's password which runs your database
• double check entire mysql error logs as there is most likely other database tables (innodb based) which have similar errors which you'd need to do the same OPTIMIZE TABLE tablename command on.

Disclaimer: no guarantees this will work so I am no responsible for any data loss you may suffer. You can backup your database before running this command.

Honestly, I wouldn't downgrade, if you moved to Percona 5.6 stick with it rather than do downgrades (unless you know what you're doing).

edit: also make sure you use Percoan 5.6 latest stable if you are upgrading http://www.percona.com/downloads/Percona-Server-5.6/