Bug #73374 CHECKSUM TABLE results differ across MySQL versions
Submitted: 24 Jul 2014 11:15 Modified: 21 Aug 2015 14:47
Reporter: Przemyslaw Malkowski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.5 5.6 5.7 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any
Tags: checksum
Triage: Needs Triage: D3 (Medium)

[24 Jul 2014 11:15] Przemyslaw Malkowski
Description:
In some cases, the CHECKSUM TABLE results may differ between MySQL major versions. And it seems that's not due to changed row format as it happens for MyISAM tables too.

How to repeat:
Here is a simple example where checksum result differs in 5.1, 5.5 versus 5.6 and 5.7 versions. The same difference was when InnoDB engine was used.

mysql [localhost] {msandbox} (test) > select @@version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 5.5.38    | MySQL Community Server (GPL) |
+-----------+------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > create table t3 (id int auto_increment primary key, a varchar(100), b text, c datetime) engine=myisam;
Query OK, 0 rows affected (0.30 sec)

mysql [localhost] {msandbox} (test) > insert into t3 values (null, "adadadadadad", "ad aada dada","2014-07-24 12:02:29");
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into t3 values (null, "adadadadadad", "ad aada dada","2014-07-24 12:02:33");
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t3;
+----+--------------+--------------+---------------------+
| id | a            | b            | c                   |
+----+--------------+--------------+---------------------+
|  1 | adadadadadad | ad aada dada | 2014-07-24 12:02:29 |
|  2 | adadadadadad | ad aada dada | 2014-07-24 12:02:33 |
+----+--------------+--------------+---------------------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > checksum table t3;
+---------+------------+
| Table   | Checksum   |
+---------+------------+
| test.t3 | 1001427145 |
+---------+------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(100) DEFAULT NULL,
  `b` text,
  `c` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

-----------------------------------------------------------------------------------------

mysql [localhost] {msandbox} (test) > select @version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 5.6.19    | MySQL Community Server (GPL) |
+-----------+------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > create table t3 (id int auto_increment primary key, a varchar(100), b text, c datetime) engine=myisam;
Query OK, 0 rows affected (0.29 sec)

mysql [localhost] {msandbox} (test) > insert into t3 values (null, "adadadadadad", "ad aada dada","2014-07-24 12:02:29");
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into t3 values (null, "adadadadadad", "ad aada dada","2014-07-24 12:02:33");
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t3;
+----+--------------+--------------+---------------------+
| id | a            | b            | c                   |
+----+--------------+--------------+---------------------+
|  1 | adadadadadad | ad aada dada | 2014-07-24 12:02:29 |
|  2 | adadadadadad | ad aada dada | 2014-07-24 12:02:33 |
+----+--------------+--------------+---------------------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > checksum table t3;
+---------+------------+
| Table   | Checksum   |
+---------+------------+
| test.t3 | 2222903834 |
+---------+------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(100) DEFAULT NULL,
  `b` text,
  `c` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

In this particular case, checksum was 1001427145 in 5.1.73 and 5.5.38 and 2222903834 in 5.6.19 and 5.7.4.

For another, InnoDB table with many rows, I have different checksum results in MySQL 5.1.68 vs 5.5.36, however I cannot include dump as the data is confidential and with just few example rows I am unable to reproduce. In the same case md5sum shows the same result, which proves data is the same:

5.1 >checksum table searchEngineAccounts;
+--------------------------------+------------+
| Table | Checksum |
+--------------------------------+------------+
| test.searchEngineAccounts | 3186292632 |
+--------------------------------+------------+
1 row in set (2.54 sec)

5.5 > checksum table searchEngineAccounts;
+--------------------------------+------------+
| Table | Checksum |
+--------------------------------+------------+
| test.searchEngineAccounts | 1702763565 |
+--------------------------------+------------+
1 row in set (2.44 sec)

5.1 >pager md5sum
PAGER set to 'md5sum'
5.1 >select * from searchEngineUsers;
18f4ad8a9f496c6c059a81c71b2a6091 -
90562 rows in set (0.56 sec)

5.5 > pager md5sum
PAGER set to 'md5sum'
5.5 > select * from searchEngineUsers;
18f4ad8a9f496c6c059a81c71b2a6091 -
90562 rows in set (0.32 sec)

Suggested fix:
Checksum table command should help to ensure data is consistent between tables, while it's not reliable to compare the results across different MySQL versions. This should be at least well explained in documentation. 
Maybe another command should be introduced that is focused specifically on data integrity and would work like the "pager md5sum + select * ..." example.
[24 Jul 2014 19:26] Sveta Smirnova
Thank you for the report.

Verified as described: our manual clearly says at http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-4.html that "InnoDB tables that contain DATETIME and TIMESTAMP types into MySQL 5.6.4 (or later) requires a workaround procedure which is described in the “Server Changes” section of Upgrading from MySQL 5.5 to 5.6 " and says no word about MyISAM. Either this note or behavior of CHECKSUM TABLE should be fixed.

In my case result for 5.0.97, 5.1.74 and 5.5.39 is 1001427145 and for 5.6.21 and 5.7.5 is 2222903834
[11 Sep 2014 16:02] Jon Stephens
Since there's no indication in the docs that CHECKSUM TABLE results have been changed, this should be a Server bug. Else please supply Docs with definitive info as to what we do or don't guarantee about the result from this statement across versions.

Thanks!
[21 Aug 2015 13:38] Jon Stephens
Please note that https://dev.mysql.com/doc/refman/5.6/en/checksum-table.html already states 

    The checksum value depends on the table row format. If the row format 
    changes, the checksum also changes. For example, the storage format for 
    VARCHAR changed between MySQL 4.1 and 5.0, so if a 4.1 table is upgraded to 
    MySQL 5.0, the checksum value may change.
[21 Aug 2015 14:23] Jon Stephens
Also note that the changelog entry in question reads,

    ·For TIME, DATETIME, and TIMESTAMP columns, the encoding and storage 
    requirements in new tables differ from such columns in tables created 
    previously because these types now include a fractional seconds part.

    ·Due to changes to encoding and storage requirements for DATETIME and 
    TIMESTAMP types, importing pre-MySQL 5.6.4 InnoDB tables (using ALTER TABLE 
    ... IMPORT TABLESPACE) that contain DATETIME and TIMESTAMP types into MySQL 
    5.6.4 (or later) requires a workaround procedure which is described in the 
    “Server Changes” section of Upgrading from MySQL 5.5 to 5.6.

The first item states that there was a table storage format change. It is not specific to InnoDB and nothing here says that it is; the changelog entry itself is specific when it begins with, "Incompatible Change: MySQL now permits fractional seconds...". Nor is the changelog entry tagged with "InnoDB" as changes specific to InnoDB usually are.

The second item refers to InnoDB because the statement ALTER TABLE ... IMPORT TABLESPACE is specific to InnoDB.
[21 Aug 2015 14:47] Jon Stephens
5.0+: Added new section "Replication and CHECKSUM TABLE"

  5.5+: Updated example used in "CHECKSUM TABLE Syntax"
  
  5.6: Updated item in "Migrating From MySQL 5.5 to 5.6"

  Changelog: Updated BUG#8523 / BUG#11745064 changelog entry to make it 
  very clear that the changes in temporal types did not apply only to InnoDB

Closed.