[MDEV-11040] Different checksum values - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.1.18, 10.0(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
Fix Version/s: 10.5
Component/s: Admin statements, Storage Engine - Aria, Storage Engine - InnoDB
Labels:
- upstream

Description

The following set of commands lead to two different checksums:

MariaDB [test]> drop table if exists t1, t2;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

MariaDB [test]> create table t1(c varchar(100)) engine=innodb row_format=fixed; # "fixed" not a valid innodb row_format

Query OK, 0 rows affected, 1 warning (0.04 sec)

MariaDB [test]> show warnings;

+---------+------+--------------------------------------+

| Level   | Code | Message                              |

+---------+------+--------------------------------------+

| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |

+---------+------+--------------------------------------+

1 row in set (0.00 sec)

MariaDB [test]> create table t2(c varchar(100)) engine=innodb row_format=compact;

Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> insert into t1 values("hello");

Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into t2 select * from t1;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test]> select t1.c = t2.c from t1, t2;

+-------------+

| t1.c = t2.c |

+-------------+

|           1 |

+-------------+

1 row in set (0.00 sec)

MariaDB [test]> checksum table t1;

+---------+------------+

| Table   | Checksum   |

+---------+------------+

| test.t1 | 3908893439 |

+---------+------------+

1 row in set (0.01 sec)

MariaDB [test]> checksum table t2;

+---------+------------+

| Table   | Checksum   |

+---------+------------+

| test.t2 | 1852344401 |

+---------+------------+

1 row in set (0.00 sec)

While the following works ok.

MariaDB [test]> drop table if exists t1, t2;

Query OK, 0 rows affected (0.08 sec)

MariaDB [test]> create table t1(c varchar(100)) engine=innodb row_format=compact;

Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> create table t2(c varchar(100)) engine=innodb row_format=compact;

Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> insert into t1 values("hello");

Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into t2 select * from t1;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test]> select t1.c = t2.c from t1, t2;

+-------------+

| t1.c = t2.c |

+-------------+

|           1 |

+-------------+

1 row in set (0.00 sec)

MariaDB [test]> checksum table t1;

+---------+------------+

| Table   | Checksum   |

+---------+------------+

| test.t1 | 1852344401 |

+---------+------------+

1 row in set (0.00 sec)

MariaDB [test]> checksum table t2;

+---------+------------+

| Table   | Checksum   |

+---------+------------+

| test.t2 | 1852344401 |

+---------+------------+

1 row in set (0.00 sec)

Attachments

Issue Links

relates to

MDEV-7635 update defaults and simplify mysqld config parameters

Closed

Activity

Ascending order - Click to sort in descending order

Nirbhay Choubey (Inactive) created issue - 2016-10-12 14:20

Elena Stepanova made changes - 2016-10-12 15:47

Field	Original Value	New Value
Status	Open [ 1 ]	Confirmed [ 10101 ]

Elena Stepanova added a comment - 2016-10-12 16:07

Reproducible with MySQL 5.6 / 5.7 as well.

Interestingly, in MySQL 5.7 / MariaDB 10.2 it assumes 'DYNAMIC', but otherwise the result is the same – the checksum for the table is different from both COMPACT and DYNAMIC.

create table t1 (c varchar(100)) engine=InnoDB;

create table t2 (c varchar(100)) engine=InnoDB row_format=compact;

create table t3 (c varchar(100)) engine=InnoDB row_format=fixed;

Warnings:

Warning	1478	InnoDB: assuming ROW_FORMAT=DYNAMIC.

create table t4 (c varchar(100)) engine=InnoDB row_format=dynamic;

insert into t1 values ('Hello');

insert into t2 values ('Hello');

insert into t3 values ('Hello');

insert into t4 values ('Hello');

checksum table t1, t2, t3, t4;

Table	Checksum

test.t1	2947131221

test.t2	2947131221

test.t3	691918331

test.t4	2947131221

Elena Stepanova added a comment - 2016-10-12 16:07 Reproducible with MySQL 5.6 / 5.7 as well. Interestingly, in MySQL 5.7 / MariaDB 10.2 it assumes 'DYNAMIC', but otherwise the result is the same – the checksum for the table is different from both COMPACT and DYNAMIC. create table t1 (c varchar (100)) engine=InnoDB; create table t2 (c varchar (100)) engine=InnoDB row_format=compact; create table t3 (c varchar (100)) engine=InnoDB row_format=fixed; Warnings: Warning 1478 InnoDB: assuming ROW_FORMAT= DYNAMIC . create table t4 (c varchar (100)) engine=InnoDB row_format= dynamic ; insert into t1 values ( 'Hello' ); insert into t2 values ( 'Hello' ); insert into t3 values ( 'Hello' ); insert into t4 values ( 'Hello' ); checksum table t1, t2, t3, t4; Table Checksum test.t1 2947131221 test.t2 2947131221 test.t3 691918331 test.t4 2947131221

Elena Stepanova made changes - 2016-10-12 16:07

Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.2 [ 14601 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.2 [ 14601 ]
Labels		upstream

Nirbhay Choubey (Inactive) made changes - 2016-10-14 17:08

Link

This issue relates to ~~MDEV-7635~~ [ ~~MDEV-7635~~ ]

Jan Lindström (Inactive) made changes - 2017-01-23 12:44

Assignee

Marko Mäkelä [ marko ]

Marko Mäkelä added a comment - 2020-08-26 07:13

It turns out that the problem specifically affects ROW_FORMAT=FIXED and CHECKSUM TABLE, even in other storage engines:

--source include/have_innodb.inc

SET @save_strict=@@GLOBAL.innodb_strict_mode;

SET GLOBAL innodb_strict_mode=OFF;

create temporary table t1 (c varchar(100)) engine=InnoDB;

create temporary table t2 (c varchar(100)) engine=InnoDB row_format=fixed;

create temporary table t3 (c varchar(100)) engine=Aria row_format=dynamic;

create temporary table t4 (c varchar(100)) engine=Aria row_format=fixed;

insert into t1 values ('Hello');

insert into t2 values ('Hello');

insert into t3 values ('Hello');

insert into t4 values ('Hello');

checksum table t1, t2, t3, t4;

SET GLOBAL innodb_strict_mode=@save_strict;

Aria and InnoDB are acting consistently here:

10.2 8cf8ad86d4b6f3479d80f3d8e8c2bcf463966924
SET @save_strict=@@GLOBAL.innodb_strict_mode;
SET GLOBAL innodb_strict_mode=OFF;
create temporary table t1 (c varchar(100)) engine=InnoDB;
create temporary table t2 (c varchar(100)) engine=InnoDB row_format=fixed;
Warnings:
Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
create temporary table t3 (c varchar(100)) engine=Aria row_format=dynamic;
create temporary table t4 (c varchar(100)) engine=Aria row_format=fixed;
insert into t1 values ('Hello');
insert into t2 values ('Hello');
insert into t3 values ('Hello');
insert into t4 values ('Hello');
checksum table t1, t2, t3, t4;
Table Checksum
test.t1 2947131221
test.t2 691918331
test.t3 2947131221
test.t4 691918331
SET GLOBAL innodb_strict_mode=@save_strict;

I got an identical result with 10.5 d8ba2930d665579e4da1f795094ab7396b453d49 as well.

For the record, SHOW TABLE STATUS would indicate that the actual Row_format of both InnoDB tables is Dynamic. The DYNAMIC and COMPACT format should be identical in this case, because nothing will be stored off-page. I tried also REDUNDANT, and it is yielding the same checksum 2947131221, even though the internal storage format is a little different. The data would be converted to the TABLE::record buffer anyway.

One thing that I might suspect is the uninitialized tail of the VARCHAR buffer, but the following patch did not make any difference:

diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc

index 9340d5060d9..ed9c2c534db 100644

--- a/storage/innobase/row/row0sel.cc

+++ b/storage/innobase/row/row0sel.cc

@@ -2815,6 +2815,9 @@ row_sel_field_store_in_mysql_format_func(

 	ut_ad(len != UNIV_SQL_NULL);

 	MEM_CHECK_DEFINED(data, len);

 	MEM_CHECK_ADDRESSABLE(dest, templ->mysql_col_len);

+	for (ulint i = templ->mysql_col_len; i--; ) {

+		dest[i] = static_cast<byte>(ut_rnd_gen());

+	}

 #ifdef HAVE_valgrind_or_MSAN

 	MEM_UNDEFINED(dest, templ->mysql_col_len);

 #endif /* HAVE_valgrind_or_MSAN */

Marko Mäkelä added a comment - 2020-08-26 07:13 It turns out that the problem specifically affects ROW_FORMAT=FIXED and CHECKSUM TABLE , even in other storage engines: --source include/have_innodb.inc SET @save_strict=@@ GLOBAL .innodb_strict_mode; SET GLOBAL innodb_strict_mode= OFF ; create temporary table t1 (c varchar (100)) engine=InnoDB; create temporary table t2 (c varchar (100)) engine=InnoDB row_format=fixed; create temporary table t3 (c varchar (100)) engine=Aria row_format= dynamic ; create temporary table t4 (c varchar (100)) engine=Aria row_format=fixed; insert into t1 values ( 'Hello' ); insert into t2 values ( 'Hello' ); insert into t3 values ( 'Hello' ); insert into t4 values ( 'Hello' ); checksum table t1, t2, t3, t4; SET GLOBAL innodb_strict_mode=@save_strict; Aria and InnoDB are acting consistently here: 10.2 8cf8ad86d4b6f3479d80f3d8e8c2bcf463966924 SET @save_strict=@@GLOBAL.innodb_strict_mode; SET GLOBAL innodb_strict_mode=OFF; create temporary table t1 (c varchar(100)) engine=InnoDB; create temporary table t2 (c varchar(100)) engine=InnoDB row_format=fixed; Warnings: Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC. create temporary table t3 (c varchar(100)) engine=Aria row_format=dynamic; create temporary table t4 (c varchar(100)) engine=Aria row_format=fixed; insert into t1 values ('Hello'); insert into t2 values ('Hello'); insert into t3 values ('Hello'); insert into t4 values ('Hello'); checksum table t1, t2, t3, t4; Table Checksum test.t1 2947131221 test.t2 691918331 test.t3 2947131221 test.t4 691918331 SET GLOBAL innodb_strict_mode=@save_strict; I got an identical result with 10.5 d8ba2930d665579e4da1f795094ab7396b453d49 as well. For the record, SHOW TABLE STATUS would indicate that the actual Row_format of both InnoDB tables is Dynamic . The DYNAMIC and COMPACT format should be identical in this case, because nothing will be stored off-page. I tried also REDUNDANT , and it is yielding the same checksum 2947131221, even though the internal storage format is a little different. The data would be converted to the TABLE::record buffer anyway. One thing that I might suspect is the uninitialized tail of the VARCHAR buffer, but the following patch did not make any difference: diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc index 9340d5060d9..ed9c2c534db 100644 --- a/storage/innobase/row/row0sel.cc +++ b/storage/innobase/row/row0sel.cc @@ -2815,6 +2815,9 @@ row_sel_field_store_in_mysql_format_func( ut_ad(len != UNIV_SQL_NULL); MEM_CHECK_DEFINED(data, len); MEM_CHECK_ADDRESSABLE(dest, templ->mysql_col_len); + for (ulint i = templ->mysql_col_len; i--; ) { + dest[i] = static_cast<byte>(ut_rnd_gen()); + } #ifdef HAVE_valgrind_or_MSAN MEM_UNDEFINED(dest, templ->mysql_col_len); #endif /* HAVE_valgrind_or_MSAN */

Marko Mäkelä made changes - 2020-08-26 07:13

Component/s		Admin statements [ 11400 ]
Component/s		Storage Engine - Aria [ 10126 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]
Assignee	Marko Mäkelä [ marko ]	Oleksandr Byelkin [ sanja ]
Priority	Minor [ 4 ]	Major [ 3 ]

Sergei Golubchik made changes - 2020-09-01 17:35

Fix Version/s

10.6 [ 24028 ]

Julien Fritsch made changes - 2020-12-01 12:38

Fix Version/s

10.1 [ 16100 ]

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

Workflow

MariaDB v3 [ 77804 ]

MariaDB v4 [ 143906 ]

Ralf Gebhardt made changes - 2022-08-04 09:20

Fix Version/s

10.2 [ 14601 ]

Julien Fritsch made changes - 2023-04-27 14:25

Fix Version/s

10.3 [ 22126 ]

Julien Fritsch made changes - 2024-09-10 15:07

Fix Version/s

10.4 [ 22408 ]

People

Assignee:: Oleksandr Byelkin

Reporter:: Nirbhay Choubey (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2016-10-12 14:20

Updated:: 2024-09-10 15:07

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