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: | |
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 |
[24 Jul 2014 11:15]
Przemyslaw Malkowski
[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.