[MDEV-17085] CHECKSUM TABLE EXTENDED does not work correctly Created: 2018-08-28  Updated: 2020-08-25  Resolved: 2019-01-16

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 5.5, 10.0, 10.1, 10.0.30, 10.3.9, 10.2, 10.3
Fix Version/s: 10.4.2, 10.1.38, 5.5.63, 10.0.38, 10.2.22, 10.3.13

Type: Bug Priority: Critical
Reporter: Christian Stern Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: checksum
Environment:

Linux 32bit
Windows 64bit

Issue Links:
Duplicate
is duplicated by MDEV-17372 TABLE CHECKSUM calculation sometimes ... Closed

 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.

 Comments   
Comment by Alice Sherepa [ 2018-08-28 ]

Thanks for the report!
Repeatable on MariaDB 5.5-10.3

CREATE TABLE t1 ( c1 int NOT NULL, c2 int NOT NULL, c4 varchar(20), c5 varchar(20), c6 varchar(20), c7 varchar(20), c8 varchar(20), c9 varchar(20), c10 varchar(20), c11 varchar(20), c12 varchar(20), c13 varchar(20), c14 varchar(20), c15 varchar(20), c16 varchar(20), c19 int NOT NULL, c20 int NOT NULL, c21 varchar(20), c22 VARCHAR(20), c23 varchar(20));
 
 
 insert into t1 values (5,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,NULL,NULL);
 
 
 
CHECKSUM TABLE t1 EXTENDED; 
UPDATE t1 SET c21='cat' WHERE  c1=5;
 
CHECKSUM TABLE t1 EXTENDED;


MariaDB [test]> CHECKSUM TABLE t1 EXTENDED; 
+---------+------------+
 
| Table   | Checksum   |
 
+---------+------------+
 
| test.t1 | 2491740852 |
 
+---------+------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> UPDATE t1 SET c21='cat' WHERE  c1=5;
 
Query OK, 1 row affected (0.007 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
MariaDB [test]> CHECKSUM TABLE t1 EXTENDED;
 
+---------+------------+
 
| Table   | Checksum   |
 
+---------+------------+
 
| test.t1 | 2491740852 |
 
+---------+------------+
 
1 row in set (0.001 sec)

Comment by Elena Stepanova [ 2018-08-29 ]

It looks like starting from 11th column (c12 in Alice's example), the values don't affect the checksum at all, regardless of what you insert there, it's still the magic 2491740852. Values for first 10 columns do matter, both upon INSERT and UPDATE.

Comment by Lennart Schedin [ 2018-10-04 ]

I wrote the ticket MDEV-17372. It was closed as a duplicate with reference to this ticket.

I have debugged the code for MariaDB server using both the SQL in my ticket and this ticket. The problem (in both) is that the checksum calculation code erroneously believes that some columns are NULL, and are thus ignored in the checksum calculation. Relevant code block: https://github.com/MariaDB/server/blob/a49ec980422e04ce1a37344be09aa6254f16fa2a/sql/sql_table.cc#L7889-L7890

See my Analysis-section in MDEV-17372 for more details.

Comment by Lennart Schedin [ 2018-10-04 ]

Note: when using the --old startup parameter or adding old to my.cnf this bug will not occur (because of a slightly different checksum calculation). See https://mariadb.com/kb/en/library/checksum-table/ for configuration documentation.

Comment by Oleksandr Byelkin [ 2019-01-16 ]

The problem was in calculating of the mask to clear unused null bits in case of using full byte

Comment by Oleksandr Byelkin [ 2019-01-16 ]

reviewed by Marko

Generated at Thu Feb 08 08:33:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.