MySQL 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:

 

Shell

2014-10-18 08:26:31 7fb114254700 InnoDB: Compressed page type (17855); stored checksum in field1 0; calculated checksums for field1: crc32 4289414559, innodb 0, none 3735928559; page LSN 24332465308430; page number (if stored to page already) 60727; space id (if stored to page already) 448

InnoDB: Page may be an index page where index id is 516

 

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.

 

 

 

 

 

 

 

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:

 

 

 

$ cat page/page0zip.cc

 

page_zip_verify_checksum(

...

       /* declare empty pages non-corrupted */

        if (stored == 0) {

                /* make sure that the page is really empty */

                ulint i;

                for (i = 0; i < size; i++) {

                        if (*((const char*) data + i) != 0) {

                                return(FALSE);

                        }

                }

 

                return(TRUE);

        }

 

 

 

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

 

 

 

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.

 

 

 

$ cat /etc/my.cnf

...

[mysqld]

...

innodb_checksum_algorithm=crc32

 

 

 

 

And rebuild the table again:

 

mysql> ALTER TABLE sakila.actor ENGINE InnoDB ROW_FORMAT Compressed;

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

 

 

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.