Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4(EOL), 10.5, 10.6, 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
-
None
Description
CHECKSUM of a table with a virtual column may change, even though the contents and the structure remains visibly the same.
The first test case is seemingly deterministic, with checksum changing after restoration of mysqldump.
The second test case is highly non-deterministic (on unclear reasons), it shows how CHECKSUM changes after simple server restart, without dump restoration. It involves several features none of which I could remove, so I'm not sure it's the same problem, but it does involve virtual columns among others.
|
Test case with dump |
CREATE TABLE t (a INT, b INT AS (a)) ENGINE=MyISAM; |
INSERT INTO t (a) VALUES (1),(2); |
--echo # Before dump
|
SHOW CREATE TABLE t; |
SELECT * FROM t; |
CHECKSUM TABLE t; |
--exec $MYSQL_DUMP test --tables t > $MYSQL_TMP_DIR/t.dump
|
DROP TABLE t; |
--exec $MYSQL test < $MYSQL_TMP_DIR/t.dump
|
--echo # After dump
|
SHOW CREATE TABLE t; |
SELECT * FROM t; |
CHECKSUM TABLE t; |
|
|
DROP TABLE t; |
|
Results with mysqldump, 10.4 900c4d69 |
# Before dump
|
SHOW CREATE TABLE t; |
Table Create Table |
t CREATE TABLE `t` ( |
`a` int(11) DEFAULT NULL, |
`b` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
SELECT * FROM t; |
a b
|
1 1
|
2 2
|
CHECKSUM TABLE t; |
Table Checksum |
test.t 2865344526
|
# After dump |
SHOW CREATE TABLE t; |
Table Create Table |
t CREATE TABLE `t` ( |
`a` int(11) DEFAULT NULL, |
`b` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
SELECT * FROM t; |
a b
|
1 1
|
2 2
|
CHECKSUM TABLE t; |
Table Checksum |
test.t 1690939406
|
|
Test case with restart |
--source include/have_innodb.inc
|
|
|
CREATE TABLE t ( |
id int(11) NOT NULL AUTO_INCREMENT, |
va mediumint GENERATED ALWAYS AS (a), |
a mediumint NOT NULL DEFAULT 0, |
b char(1) DEFAULT NULL, |
PRIMARY KEY (id) |
) ENGINE=InnoDB WITH SYSTEM VERSIONING; |
|
|
INSERT INTO t (b) VALUES ('a'),('b'),('c'); |
UPDATE t SET b = 'x'; |
|
|
CHECKSUM TABLE t EXTENDED; |
|
|
--source include/restart_mysqld.inc
|
CHECKSUM TABLE t EXTENDED; |
--source include/restart_mysqld.inc
|
CHECKSUM TABLE t EXTENDED; |
--source include/restart_mysqld.inc
|
CHECKSUM TABLE t EXTENDED; |
|
|
DROP TABLE t; |
|
Results #1 with restart 10.4 900c4d69 |
CHECKSUM TABLE t EXTENDED; |
Table Checksum |
test.t 4236583688
|
# restart
|
CHECKSUM TABLE t EXTENDED; |
Table Checksum |
test.t 4236583688
|
# restart
|
CHECKSUM TABLE t EXTENDED; |
Table Checksum |
test.t 1358680303
|
# restart
|
CHECKSUM TABLE t EXTENDED; |
Table Checksum |
test.t 1358680303
|
|
Results #2 with restart |
CHECKSUM TABLE t EXTENDED; |
Table Checksum |
test.t 1428158860
|
# restart
|
CHECKSUM TABLE t EXTENDED; |
Table Checksum |
test.t 1428158860
|
# restart
|
CHECKSUM TABLE t EXTENDED; |
Table Checksum |
test.t 714221683
|
# restart
|
CHECKSUM TABLE t EXTENDED; |
Table Checksum |
test.t 1428158860
|
etc.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
After restoring a data dump, CHECKSUM of a table with a virtual column may change, even though the contents and the structure remains visibly the same.
{code:sql} CREATE TABLE t (a INT, b INT AS (a)) ENGINE=MyISAM; INSERT INTO t (a) VALUES (1),(2); --echo # Before dump SHOW CREATE TABLE t; SELECT * FROM t; CHECKSUM TABLE t; --exec $MYSQL_DUMP test --tables t > $MYSQL_TMP_DIR/t.dump DROP TABLE t; --exec $MYSQL test < $MYSQL_TMP_DIR/t.dump --echo # After dump SHOW CREATE TABLE t; SELECT * FROM t; CHECKSUM TABLE t; DROP TABLE t; {code} {code:sql|title=10.4 900c4d69} # Before dump SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t; a b 1 1 2 2 CHECKSUM TABLE t; Table Checksum test.t 2865344526 {code} {code:sql} # After dump SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t; a b 1 1 2 2 CHECKSUM TABLE t; Table Checksum test.t 1690939406 {code} |
CHECKSUM of a table with a virtual column may change, even though the contents and the structure remains visibly the same.
The first test case is seemingly deterministic, with checksum changing after restoration of mysqldump. The second test case is highly non-deterministic (on unclear reasons), it shows how CHECKSUM changes after simple server restart, without dump restoration. It involves several features none of which I could remove, so I'm not sure it's the same problem, but it does involve virtual columns among others. {code:sql|title=Test case with dump} CREATE TABLE t (a INT, b INT AS (a)) ENGINE=MyISAM; INSERT INTO t (a) VALUES (1),(2); --echo # Before dump SHOW CREATE TABLE t; SELECT * FROM t; CHECKSUM TABLE t; --exec $MYSQL_DUMP test --tables t > $MYSQL_TMP_DIR/t.dump DROP TABLE t; --exec $MYSQL test < $MYSQL_TMP_DIR/t.dump --echo # After dump SHOW CREATE TABLE t; SELECT * FROM t; CHECKSUM TABLE t; DROP TABLE t; {code} {code:sql|title=Results with mysqldump, 10.4 900c4d69} # Before dump SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t; a b 1 1 2 2 CHECKSUM TABLE t; Table Checksum test.t 2865344526 {code} {code:sql} # After dump SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t; a b 1 1 2 2 CHECKSUM TABLE t; Table Checksum test.t 1690939406 {code} {code:sql|title=Test case with restart} --source include/have_innodb.inc CREATE TABLE t ( id int(11) NOT NULL AUTO_INCREMENT, va mediumint GENERATED ALWAYS AS (a), a mediumint NOT NULL DEFAULT 0, b char(1) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB WITH SYSTEM VERSIONING; INSERT INTO t (b) VALUES ('a'),('b'),('c'); UPDATE t SET b = 'x'; CHECKSUM TABLE t EXTENDED; --source include/restart_mysqld.inc CHECKSUM TABLE t EXTENDED; --source include/restart_mysqld.inc CHECKSUM TABLE t EXTENDED; --source include/restart_mysqld.inc CHECKSUM TABLE t EXTENDED; DROP TABLE t; {code} {code:sql|title=Results #1 with restart 10.4 900c4d69} CHECKSUM TABLE t EXTENDED; Table Checksum test.t 4236583688 # restart CHECKSUM TABLE t EXTENDED; Table Checksum test.t 4236583688 # restart CHECKSUM TABLE t EXTENDED; Table Checksum test.t 1358680303 # restart CHECKSUM TABLE t EXTENDED; Table Checksum test.t 1358680303 {code} {code:sql|title=Results #2 with restart} CHECKSUM TABLE t EXTENDED; Table Checksum test.t 1428158860 # restart CHECKSUM TABLE t EXTENDED; Table Checksum test.t 1428158860 # restart CHECKSUM TABLE t EXTENDED; Table Checksum test.t 714221683 # restart CHECKSUM TABLE t EXTENDED; Table Checksum test.t 1428158860 {code} etc. |
| Priority | Minor [ 4 ] | Major [ 3 ] |
| Fix Version/s | 10.10 [ 27530 ] |
| Fix Version/s | 11.0 [ 28320 ] |
| Fix Version/s | 10.4 [ 22408 ] |
| Fix Version/s | 11.1 [ 28549 ] |