Details
Critical Description
Hello,
to check if the contents of a table has changed, I use the "CHECKSUM TABLE xxx EXTENDED" command. However, there are cases where the checksum does not differ despite the change.
Here is a test case:
Table schema:
CREATE TABLE IF NOT EXISTS `test_checksum` (
|
`Idx` INT(11) NOT NULL DEFAULT '0' ,
|
`ID` INT(11) NOT NULL DEFAULT '0' ,
|
`column1` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NOT NULL DEFAULT '' ,
|
`column2` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NOT NULL DEFAULT '' ,
|
`column3` ENUM('eHours','eComp','eDate','eIntervall','eValue','eComplex') CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NOT NULL DEFAULT 'eHours' ,
|
`column4` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL ,
|
`column5` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL ,
|
`column6` DOUBLE NULL ,
|
`column7` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL ,
|
`column8` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL ,
|
`column9` DOUBLE NULL ,
|
`column10` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL ,
|
`column11` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL ,
|
`column12` DOUBLE NULL ,
|
`column13` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL ,
|
`column14` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL ,
|
`column15` DOUBLE NULL ,
|
`column16` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL ,
|
`column17` INT(11) UNSIGNED NOT NULL DEFAULT '10' ,
|
`column18` INT(11) UNSIGNED NOT NULL DEFAULT '0' ,
|
`column19` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NOT NULL DEFAULT '' ,
|
`column20` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NOT NULL DEFAULT '' ,
|
`column21` VARCHAR(12) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL ,
|
`column22` VARCHAR(12) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL ,
|
`column23` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL ,
|
`column24` ENUM('false','true') CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NOT NULL DEFAULT 'false' ,
|
PRIMARY KEY (`Idx`) )
|
ENGINE = MyISAM
|
DEFAULT CHARACTER SET = latin1
|
COLLATE = latin1_german1_ci
|
COMMENT = 'Test checksum table';
|
 |
INSERT INTO `test_checksum`(`Idx`,`ID`,`column1`,`column2`,`column3`,`column4`,`column5`,`column6`,`column7`,`column8`,`column9`,`column10`,`column11`,`column12`,`column13`,`column14`,`column15`,`column16`,`column17`,`column18`,`column19`,`column20`,`column21`,`column22`,`column23`,`column24`) VALUES
|
(1,0,'','','eHours',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,0,'','',NULL,NULL,NULL,'false'),
|
(2,0,'','','eHours',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,0,'','',NULL,NULL,NULL,'false'),
|
(3,0,'','','eHours',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,0,'','',NULL,NULL,NULL,'false'),
|
(4,0,'','','eHours',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,0,'','',NULL,NULL,NULL,'false'),
|
(5,0,'','','eHours',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,0,'','',NULL,NULL,NULL,'false'),
|
(6,0,'','','eHours',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,0,'','',NULL,NULL,NULL,'false'),
|
(7,0,'','','eHours',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,0,'','',NULL,NULL,NULL,'false'),
|
(8,0,'','','eHours',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,0,'','',NULL,NULL,NULL,'false'),
|
(9,0,'','','eHours',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,0,'','',NULL,NULL,NULL,'false'),
|
(10,0,'','','eHours',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,0,'','',NULL,NULL,NULL,'false');
|
Checksum test:
CHECKSUM TABLE `test_checksum` EXTENDED; --> 500528148;
|
 |
UPDATE `test_checksum` SET `column21`='1234567890' WHERE Idx=1;
|
UPDATE `test_checksum` SET `column21`='2345678901' WHERE Idx=2;
|
UPDATE `test_checksum` SET `column21`='3456789012' WHERE Idx=3;
|
UPDATE `test_checksum` SET `column21`='4567890123' WHERE Idx=4;
|
UPDATE `test_checksum` SET `column21`='5678901234' WHERE Idx=5;
|
UPDATE `test_checksum` SET `column21`='6789012345' WHERE Idx=6;
|
UPDATE `test_checksum` SET `column21`='7890123456' WHERE Idx=7;
|
UPDATE `test_checksum` SET `column21`='8901234567' WHERE Idx=8;
|
UPDATE `test_checksum` SET `column21`='9012345678' WHERE Idx=9;
|
UPDATE `test_checksum` SET `column21`='0123456789' WHERE Idx=10;
|
 |
CHECKSUM TABLE `test_checksum` EXTENDED; --> 500528148 but should never be!!!
|
I tested it on a Linux 32bit machine and Windows 64bit, with InnoDB and MyISAM, always same result.
Attachments
Issue Links
- is duplicated by
-
MDEV-17372 TABLE CHECKSUM calculation sometimes ignores columns
-
- Closed
-