1. Resolving page corruption in compressed InnoDB tables

Resolving page corruption in compressed InnoDB tables

Sometimes corruption is not the true corruption. Corruption in compressed InnoDB tables may be a false positive.

Compressed InnoDB table may hit false checksum verification failure. The bug (http://bugs.mysql.com/bug.php?id=73689) reveals itself in the error log as follows:

InnoDB complains that a stored checksum is zero. If you look closely it’s suspicious that calculated checksum is zero too.

Every InnoDB page stores a checksum in first four bytes. When InnoDB reads a page it compares the checksum, stored in the page, and the checksum calculated from the page content. If the checksums mismatch InnoDB believes the page is corrupt and crashes to prevent further corruption.

Zero, however, is a valid checksum. In a database as large as 70 TB (2^32*16k) there will be one page that leads to zero checksum result. So, quite probable event on modern databases where terabyte MySQL instances aren’t rare.

The MySQL documentation suggests that with default settings the stored checksum must match either of three checksum algorithms: none, innodb or crc32.

MySQL documentation screenshot

innodb is old and the only up until 5.6.3 checksum algorithm. Since 5.6.2 crc32 is available. crc32 is faster implementation, besides it may be calculated in hardware if CPU supports that.

I got confused about none algorithm. Although the table hints that a page stores a hard-coded value that’s being checked while reading, actually none means checksums are disabled. This is what the manual says further on though.

Having said that, even though the stored checksum is zero, the calculated value is zero, should the verification should pass. Actually it doesn’t. InnoDB assumes the page must be empty if the stored checksum is zero:

This bug is fixed in 5.6.22 that hasn’t been released yet, so to deal with the “corruption” crc32 should be used.

To convert InnoDB tablespace to crc32 checksums two steps should be done.
First, start MySQL with innodb_checksum_algorithm=none . That

Disabled checksums let InnoDB read the page without crash and rebuild it.

Second step should be done with innodb_checksum_algorithm=crc32

And rebuild the table again:

crc32 will produce different checksum so InnoDB will run fine. Of course, there is a non-zero probability crc2 will return zero on non-empty pages, so it’s better to upgrade to 5.6.22 when it’s released.

Have a question? Ask the experts!

Previous Post Next Post