[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

Christian Stern created issue - 2018-08-28 13:42

Alice Sherepa made changes - 2018-08-28 21:12

Field	Original Value	New Value
Affects Version/s		5.5 [ 15800 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]

Alice Sherepa made changes - 2018-08-28 21:12

Fix Version/s

5.5 [ 15800 ]

Alice Sherepa made changes - 2018-08-28 21:12

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova made changes - 2018-08-29 12:22

Fix Version/s		10.0 [ 16000 ]
Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Assignee		Sergei Golubchik [ serg ]

Alice Sherepa made changes - 2018-10-04 09:46

Link

This issue is duplicated by ~~MDEV-17372~~ [ ~~MDEV-17372~~ ]

Sergei Golubchik made changes - 2018-11-07 21:38

Assignee

Sergei Golubchik [ serg ]

Oleksandr Byelkin [ sanja ]

Julien Fritsch made changes - 2018-12-18 19:18

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Golubchik made changes - 2018-12-19 01:25

Priority

Critical [ 2 ]

Major [ 3 ]

Oleksandr Byelkin made changes - 2019-01-15 11:52

Priority

Major [ 3 ]

Critical [ 2 ]

Oleksandr Byelkin made changes - 2019-01-15 11:52

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2019-01-15 14:13

Comment

[ null checked like this :
{code}
inline bool is_real_null(my_ptrdiff_t row_offset= 0)
{ return null_ptr ? (null_ptr[row_offset] & null_bit ? 1 : 0) : 0; }
{code}
null_bit is 32
null_ptr is uchar array, so no surprise that it return incorrect result ]

Oleksandr Byelkin made changes - 2019-01-16 13:47

Component/s		OTHER [ 10125 ]
Component/s	Storage Engine - InnoDB [ 10129 ]
Component/s	Storage Engine - MyISAM [ 10600 ]
Fix Version/s		10.4.2 [ 23229 ]
Fix Version/s		10.1.38 [ 23209 ]
Fix Version/s		5.5.63 [ 23210 ]
Fix Version/s		10.0.38 [ 23211 ]
Fix Version/s		10.3.13 [ 23215 ]
Fix Version/s		10.2.22 [ 23250 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	5.5 [ 15800 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 89126 ]

MariaDB v4 [ 154865 ]

Jira Automation (IT) made changes - 2024-07-04 07:23

Zendesk Related Tickets

136395

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

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration