Description:
Checksum of InnoDB table varies when column is defined with a non-zero(INT) or non-empty space(VARCHAR) default value and a record has a NULL value on that column
While MyISAM table has consistent checksum and is not affected of such issue.
How to repeat:
mysql > SELECT @@version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment |
+-----------+------------------------------+
| 8.0.30 | MySQL Community Server - GPL |
+-----------+------------------------------+
1 row in set (0.00 sec)
-- For INT test :
mysql > -- INNODB
mysql > CREATE TABLE t1(test_col int) ENGINE = INNODB;
Query OK, 0 rows affected (0.02 sec)
mysql > INSERT INTO t1(test_col) VALUES(NULL);
Query OK, 1 row affected (0.00 sec)
mysql > CHECKSUM TABLE t1;
+--------+-----------+
| Table | Checksum |
+--------+-----------+
| db1.t1 | 205926545 |
+--------+-----------+
1 row in set (0.00 sec)
mysql > ALTER TABLE t1 MODIFY test_col int DEFAULT 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t1; -- no change in checksum
+--------+-----------+
| Table | Checksum |
+--------+-----------+
| db1.t1 | 205926545 |
+--------+-----------+
1 row in set (0.00 sec)
mysql > ALTER TABLE t1 MODIFY test_col int DEFAULT 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t1; -- change in checksum
+--------+------------+
| Table | Checksum |
+--------+------------+
| db1.t1 | 3036305396 |
+--------+------------+
1 row in set (0.00 sec)
mysql > ALTER TABLE t1 MODIFY test_col int DEFAULT 2;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t1; -- change in checksum
+--------+------------+
| Table | Checksum |
+--------+------------+
| db1.t1 | 2790258714 |
+--------+------------+
1 row in set (0.00 sec)
mysql > UPDATE t1 SET test_col = 9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql > CHECKSUM TABLE t1; -- change in checksum for data update
+--------+-----------+
| Table | Checksum |
+--------+-----------+
| db1.t1 | 193866875 |
+--------+-----------+
1 row in set (0.00 sec)
mysql > ALTER TABLE t1 MODIFY test_col int DEFAULT 3;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t1; -- no change in checksum since there is no more null value
+--------+-----------+
| Table | Checksum |
+--------+-----------+
| db1.t1 | 193866875 |
+--------+-----------+
1 row in set (0.00 sec)
mysql > -- MYISAM
mysql > UPDATE t1 set test_col = NULL;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql > ALTER TABLE t1 MODIFY COLUMN test_col int, ENGINE=MYISAM;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t1; -- same with table checksum for innodb table without or zero(0) default value
+--------+-----------+
| Table | Checksum |
+--------+-----------+
| db1.t1 | 205926545 |
+--------+-----------+
1 row in set (0.00 sec)
mysql > ALTER TABLE t1 MODIFY test_col int DEFAULT 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t1; -- no change in checksum, MYISAM has consistent checksum
+--------+-----------+
| Table | Checksum |
+--------+-----------+
| db1.t1 | 205926545 |
+--------+-----------+
1 row in set (0.00 sec)
mysql > ALTER TABLE t1 MODIFY test_col int DEFAULT 1;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t1; -- no change in checksum, MYISAM has consistent checksum
+--------+-----------+
| Table | Checksum |
+--------+-----------+
| db1.t1 | 205926545 |
+--------+-----------+
1 row in set (0.00 sec)
-- For VARCHAR test :
mysql > -- INNODB
mysql > CREATE TABLE t2(test_col varchar(9)) ENGINE = INNODB;
Query OK, 0 rows affected (0.02 sec)
mysql > INSERT INTO t2(test_col) VALUES(NULL);
Query OK, 1 row affected (0.01 sec)
mysql > CHECKSUM TABLE t2;
+--------+------------+
| Table | Checksum |
+--------+------------+
| db1.t2 | 4278190080 |
+--------+------------+
1 row in set (0.00 sec)
mysql > ALTER TABLE t2 MODIFY test_col varchar(9) DEFAULT '';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t2; -- no change in checksum
+--------+------------+
| Table | Checksum |
+--------+------------+
| db1.t2 | 4278190080 |
+--------+------------+
1 row in set (0.00 sec)
mysql > ALTER TABLE t2 MODIFY test_col varchar(9) DEFAULT 'a';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t2; -- change in checksum
+--------+------------+
| Table | Checksum |
+--------+------------+
| db1.t2 | 3897081411 |
+--------+------------+
1 row in set (0.00 sec)
mysql > ALTER TABLE t2 MODIFY test_col varchar(9) DEFAULT 'b';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t2; -- change in checksum
+--------+------------+
| Table | Checksum |
+--------+------------+
| db1.t2 | 1900146681 |
+--------+------------+
1 row in set (0.00 sec)
mysql > UPDATE t2 SET test_col = 'x';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql > CHECKSUM TABLE t2; -- change in checksum for data update
+--------+------------+
| Table | Checksum |
+--------+------------+
| db1.t2 | 2503485378 |
+--------+------------+
1 row in set (0.00 sec)
mysql > ALTER TABLE t2 MODIFY test_col varchar(9) DEFAULT 'c';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t2; -- no change in checksum since there is no more null value
+--------+------------+
| Table | Checksum |
+--------+------------+
| db1.t2 | 2503485378 |
+--------+------------+
1 row in set (0.00 sec)
mysql > -- MYISAM
mysql > UPDATE t2 set test_col = NULL;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql > ALTER TABLE t2 MODIFY COLUMN test_col varchar(9), ENGINE=MYISAM;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t2; -- same with table checksum for innodb table without or empty space default value
+--------+------------+
| Table | Checksum |
+--------+------------+
| db1.t2 | 4278190080 |
+--------+------------+
1 row in set (0.00 sec)
mysql > ALTER TABLE t2 MODIFY test_col varchar(9) DEFAULT '';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t2; -- no change in checksum, MYISAM has consistent checksum
+--------+------------+
| Table | Checksum |
+--------+------------+
| db1.t2 | 4278190080 |
+--------+------------+
1 row in set (0.00 sec)
mysql > ALTER TABLE t2 MODIFY test_col varchar(9) DEFAULT 'a';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t2; -- no change in checksum, MYISAM has consistent checksum
+--------+------------+
| Table | Checksum |
+--------+------------+
| db1.t2 | 4278190080 |
+--------+------------+
1 row in set (0.00 sec)
mysql > ALTER TABLE t2 MODIFY test_col varchar(9) DEFAULT 'b';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > CHECKSUM TABLE t2; -- no change in checksum, MYISAM has consistent checksum
+--------+------------+
| Table | Checksum |
+--------+------------+
| db1.t2 | 4278190080 |
+--------+------------+
1 row in set (0.00 sec)
Suggested fix:
Ensure that checksum table is consistent for InnoDB tables regardless of default value define and its value is NULL.
Or if the inconsistency is expected, please do mentioned such behavior in the documentation https://dev.mysql.com/doc/refman/8.0/en/checksum-table.html
