Fixing MySQL / InnoDB index corruption

Index corruption is usually a very rare issue, caused either by bugs in MySQL or hardware problems. Here’s how I handled it.

I knew something was wrong when I got the following in the logs:

At first it seemed to be related to a specific index and only appeared when a query was executed that affected the entire table. Oh well, let’s do a table check in MySQL using “check table photos”, the result was:

That wasn’t looking very good. Also keep in mind that InnoDB does not offer a REPAIR function for the InnoDB engine, only for MyISAM. Mostly because it’s not supposed to happen with a InnoDB architecture, and the engine should fix everything at startup by replaying the datalogs.
Let’s stop the MySQL server and do a quick snapshot of the data files, it’s always best to have a copy of the data before you try to do any fixing / recovery.

Starting the MySQL server again and running a “check table photos” produced the following:

So, there was something wrong with all the indexes. And also at this point any query that used indexes failed, basically the table was unusable. At this point I had 3 options:
1) drop the indexes and re-create them
2) create a table using the same structure and indexes and use “insert … select”, that will basically copy the data
3) rebuild the table using “alter table photos engine = innodb” or “optimize table photos”

Since I had created a backup before, I was safe to try anything.
I didn’t like option 1, I wanted something that would get me a fresh start and not work on the existing table data.
Option 2 seemed safe but slow in my mind.
I went with option 3. Keep in mind that on InnoDB both of those commands do the same, “optimize table” is mapped to “alter table photos engine = innodb”.

For all 3 options you need to have at least enough free space as the table you are working on. And it will take time, my table was 6 GB and it took 1 hour using option 3. During that time I had thousands of lines in the logs:

But in the end everything was fine, running a “check table photos” finally produced the “ok” message:


Make sure you have backups and don’t try anything before that!


Leave a Reply

Your email address will not be published. Required fields are marked *