[MDEV-11040] Different checksum values Created: 2016-10-12  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Admin statements, Storage Engine - Aria, Storage Engine - InnoDB
Affects Version/s: 10.1.18, 10.0, 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Nirbhay Choubey (Inactive) Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: upstream

Issue Links:
Relates
relates to MDEV-7635 update defaults and simplify mysqld c... Closed

 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)



 Comments   
Comment by Elena Stepanova [ 2016-10-12 ]

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

Comment by Marko Mäkelä [ 2020-08-26 ]

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 */

Generated at Thu Feb 08 07:46:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.