|
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.
|