1. Repair Corrupted InnoDB Table with Corruption in Secondary Index

Repair Corrupted InnoDB Table with Corruption in Secondary Index

UPDATE: We announced online data recovery from corrupt databases.

Check it out on https://recovery.twindb.com/

UPDATE (01/01/2017): We stopped further development of undrop-for-innodb and do not support its open source versions.

InnoDB provides no means to repair corrupted table space. Once a table got corrupt the only way to repair MySQL files is to start it with innodb_force_recovery={4,5,6} in hope that you can dump the table, so you can rebuild the table space from scratch. At least this is what the manual says. But let’s take a closer look at InnoDB corruption. In some case you can repair InnoDB table space much faster.

UPDATE: If corruption is in PRIMARY index check post Recover Corrupt MySQL Database

What InnoDB provides to repair tablespace corruption

InnoDB doesn’t let you repair the table space, but you can rebuild secondary indexes with ALTER TABLE DROP/ADD KEY. That may be very useful in case corruption malformed pages where secondary index is stored.

So, before starting a whole story with innodb_force_recovery, dump, drop, create and reload a table check the MySQL error log. Who knows, maybe it can give clues how repair the table space without rebuilding it from scratch.

Check where the corruption is

Let me illustrate my point. As usually we will work with actor table from sakila database. It has secondary key on last_name which is good for our case:

So I went ahead and did some modifications in one page of the secondary index idx_actor_last_name. Any access to the index will lead to checksum test failure which is enough for InnoDB to crash.

Let’s check the error log

then a dump of the page 4 goes

InnoDB is even friendly enough to tell you that page 4 belongs to the secondary index idx_actor_last_name.

There are several other messages before it dies.

The key message however is the corruption is in the secondary index idx_actor_last_name.

If you try to drop the index MySQL will crash.

How to fix wrong InnoDB page checksum

There is innochecksum tool in MySQL distribution that can test offline InnoDB tablespace.
We slightly modified innochecksum and added option -f that means if the checksum of a page is wrong, rewrite it in the InnoDB page header.

So, download the latest revision of TwinDB Recovery Toolkit and compile it:

Stop MySQL and make a copy of actor.ibd, just in case something goes wrong.
Now test and fix checksums in actor.ibd. Page 4 is bad indeed.

And now add -f option to fix the checksums.

Start MySQL and fix corrupted tablespace

Now start MySQL. innodb_force_recovery option should be disabled.

When MySQL starts, drop index idx_actor_last_name:

For extra security you may want to rebuild the table:

Now you may add index idx_actor_last_name again:

Now the InnoDB tablespace is nice and clean.

Have a question? Ask the experts!

Previous Post Next Post