Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.18, 10.0(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
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
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 */ |
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.
Warnings:test.t1 2947131221test.t2 2947131221test.t3 691918331test.t4 2947131221