[MDEV-17085] CHECKSUM TABLE EXTENDED does not work correctly - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.0.30, 10.3.9, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.4.2, 10.1.38, 5.5.63, 10.0.38, 10.2.22, 10.3.13
Component/s: OTHER
Labels:
- checksum
Environment:
Linux 32bit
Windows 64bit

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

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2018-08-28 21:11

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)

Alice Sherepa added a comment - 2018-08-28 21:11 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)

Elena Stepanova added a comment - 2018-08-29 12:21

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.

Elena Stepanova added a comment - 2018-08-29 12:21 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.

Lennart Schedin added a comment - 2018-10-04 11:16

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.

Lennart Schedin added a comment - 2018-10-04 11:16 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.

Lennart Schedin added a comment - 2018-10-04 11:25

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.

Lennart Schedin added a comment - 2018-10-04 11:25 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.

Oleksandr Byelkin added a comment - 2019-01-16 09:57

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

Oleksandr Byelkin added a comment - 2019-01-16 09:57 The problem was in calculating of the mask to clear unused null bits in case of using full byte

Oleksandr Byelkin added a comment - 2019-01-16 12:55

reviewed by Marko

Oleksandr Byelkin added a comment - 2019-01-16 12:55 reviewed by Marko

People

Assignee:: Oleksandr Byelkin

Reporter:: Christian Stern

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2018-08-28 13:42

Updated:: 2024-07-07 23:38

Resolved:: 2019-01-16 13:47

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server