MySQL Bugs: #69020: CRC32 not handled correctly + MySQL tables not CRC32 compliant

Bug #69020	CRC32 not handled correctly + MySQL tables not CRC32 compliant
Submitted:	21 Apr 2013 11:06	Modified:	31 May 2015 11:21
Reporter:	Louis at oracle	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	crc32, strict

Description:
Hello,

I tried to im improve performance and one of the logical options is to off load the CRC32 calculations to the CPU

So IMHO you should use option
innodb_checksum_algorithm=strict_crc32

However forget it. It does not work yet. I engaged two problems:
1) 2013-04-21 12:27:20 6700 [Note] InnoDB: Not using CPU crc32 instructions
2) InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
2013-04-21 12:30:20 172c  InnoDB: Assertion failure in thread 5932 in file buf0buf.cc line 4069
InnoDB: We intentionally generate a memory trap.

Both lines grepped form the error log.

Louis

How to repeat:
In order to garante completely correct / actual my sql-kernel related tables It took following action

- backup the tables
- removed mysql server from my windows7 64 bit server
- added checksum_algorithm=strict_crc32 to my.ini
- download the mysql 5.6.11 64 bit zip (forget the installer, does not work)
- place the content of the data dir from the downloaded zip in your datadir
- remove the log files from that dir (they are not crc32 and will not fit your definitions any way i assume)
- install the service with sc pointing to your my.ini
- start the service
- log files will be created
- server will not start due to corrupt Database page / data tables

The problem seems to be that the default provided table are not "strict"

Suggested fix:
So the solution seems to be that oracle / mysql should tranform their kernal tables to be crc32 compliant

The work arround for this problem (problem 2) is 
- *after* you did every thing described above
- change to checksum_algorithm=crc32
The server will start

First item should be solved as well of course. My CPU (AMD buldozer) is capable to help with crc32 IMHO.

Louis

The corruption reports are a known problem, but since it requires SUPER privs (that can do anything anyway) it didn't get much more attention than trying to document it.

The limited platform support is also a known problem:
http://bugs.mysql.com/bug.php?id=68036

Bug 13630849 - CHANGING INNODB_CHECKSUM_ALGORITHM LEADS TO CORRUPTION/CRASHING
Bug 13600064 - INNODB_CHECKSUM_ALGORITHM IS A DYNAMIC PARAMETER, CAN LEAD TO DB CORRUPTION

The docs at http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-crc32-checksum 

say "be sure that all the InnoDB data is created using the CRC32 ",
of course that is not so easy because we ship the datadir already created on windows no-install builds .. further complicated by the fact you cannot simply remove the ibdata* without first converting `mysql`. tables from innodb to myisam.

[9 Apr 2015 10:55] Daniel T Price

Fixed as of the upcoming 5.6.25, 5.7.8, 5.8.0 releases, and here's the
changelog entry:

The "strict_*" forms of "innodb_checksum_algorithm" settings
("strict_none", "strict_innodb", and "strict_crc32") caused the server to
halt when a non-matching checksum was encountered, even if a non-matching
checksum was valid. For example, with
"innodb_checksum_algorithm=strict_crc32", encountering a valid "innodb"
checksum would halt the server. Instead of halting the server, a message
is now printed to the error log and the page is accepted as valid if it
matches an "innodb", "crc32" or "none" checksum.

The 5.6 and 5.7 innodb_checksum_algorithm description was also updated.

Bug#20568464 	MITIGATE THE STRICTNESS OF STRICT_* MODES OF INNODB_CHECKSUM_ALGORITHM