1. Resolving Page Corruption In Compressed InnoDB Tables

Resolving Page Corruption In Compressed InnoDB Tables

Sometimes corruption is not what is seems. Corruption in compressed InnoDB tables may be a false positive.

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

InnoDB complains about a stored checksum being a zero. If you look closely, you’ll find it suspicious that a calculated checksum is zero, too.

Every InnoDB page stores a checksum in the 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 don’t match, 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 a zero checksum result. Consequently, it’s a quite probable event on modern databases with a lot of terabyte MySQL instances.

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

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

I got confused about the 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, 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.

Use innodb_checksum_algorithm=crc32 for the second step:

And rebuild the table again:

crc32 will produce a different checksum, so InnoDB will run fine. Of course, there is a non-zero probability that 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