Recently I had a problem where an InnoDB table was corrupted. It was reporting a 2013 error and actually dropping all the connections to the database. This was all that was being reported:
mysql> select * from corrupted_table; ERROR 2013 (HY000): Lost connection to MySQL server during query
The logs weren’t actually reporting anything. Oddly, select count(*) from corrupted_table; was actually returning the correct figure. It was just about two thirds of the way through the table where the error occurred. The usual option is to use:
innodb_force_recovery = 4
In this case, that didn’t work. The table just stopped at the two-thirds corruption, but instead of crashing the innodb_force_recovery option just made it stop.
Truncating the table wasn’t an option. Finally I decided to move a backup of the database to another server to try more risky fixes.
My backup software of choice for mysql is Percona Xtrabackup. I like it as InnoDB tables don’t get locked during the backup. It’s most useful for those several hundred gigabyte sized databases.
I grabbed the backup, moved it to the other server. I unpacked the tgz, and then applied the logs:
innobackupex-1.5.1 --ibbackup=xtrabackup_51 --apply-log `pwd`
Innobackupex suggests the –copy-back step but this can be (a) slow and (b) expensive on diskspace with a big backup.
I skipped –copy-back by instead moving the database files with the applied logs to /var/lib/mysql and after fixing ownerships I started mysql.
I then checked to see if the table had the corruption. Select count(*) returned the same value. I then did the select * from test and to my surprise that didn’t fail. Something in the restore fixed the broken table.
mysqldump then became my friend, and after dumping the now fixed table, and dropping the broken one, was able to get a fully functional table back again.
The tl;dr is: If nothing else works, try restoring a corrupted table from a backup and see if that fixed the innodb corruption.