Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17085

CHECKSUM TABLE EXTENDED does not work correctly

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              christian.stern Christian Stern
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: