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:

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

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:

$ 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

$ cat /etc/my.cnf
...
[mysqld]
...
innodb_checksum_algorithm=none

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

mysql> ALTER TABLE sakila.actor ENGINE InnoDB ROW_FORMAT Compressed;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Use innodb_checksum_algorithm=crc32 for the second step:

$ 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 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