Description:
When a pre 8029 instant column exists on a non-system table with NULL columns in the mysql schema, the table will become corrupted post 8030+ upgrade.
I have observed this behavior on all versions 8030+ when upgrading from <= 8028.
How to repeat:
1. On 8.0.28, create two identical tables. One in the MySQL schema, another in the test schema.
CREATE TABLE `test`.`user_table` (
`id` int AUTO_INCREMENT primary key,
`insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user` varchar(50) NOT NULL,
`action` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `mysql`.`user_table` (
`id` int auto_increment primary key,
`insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user` varchar(50) NOT NULL,
`action` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
2. Insert 1 row in each table
INSERT INTO test.user_table(user, action) VALUES (current_user(),'logged in');
INSERT INTO mysql.user_table(user, action) VALUES (current_user(),'logged in');
3. Add an instant column to each table:
alter table test.user_table add column `uh_oh` tinyint(1) DEFAULT NULL, algorithm=instant;
alter table mysql.user_table add column `uh_oh` tinyint(1) DEFAULT NULL, algorithm=instant;
4. After the above you will notice the following. Table data is fine, and we have instant cols as expected:
mysql [localhost:8028] {msandbox} ((none)) > select * from information_schema.innodb_tables where INSTANT_COLS>0;
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
| 1063 | test/user_table | 33 | 8 | 2 | Dynamic | 0 | Single | 4 |
| 1064 | mysql/user_table | 33 | 8 | 3 | Dynamic | 0 | Single | 4 |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
2 rows in set (0.01 sec)
mysql [localhost:8028] {msandbox} ((none)) > select * from test.user_table;
+----+---------------------+--------------------+-----------+-------+
| id | insert_time | user | action | uh_oh |
+----+---------------------+--------------------+-----------+-------+
| 1 | 2023-11-03 02:14:19 | msandbox@localhost | logged in | NULL |
+----+---------------------+--------------------+-----------+-------+
1 row in set (0.00 sec)
mysql [localhost:8028] {msandbox} ((none)) > select * from mysql.user_table;
+----+---------------------+--------------------+-----------+-------+
| id | insert_time | user | action | uh_oh |
+----+---------------------+--------------------+-----------+-------+
| 1 | 2023-11-03 02:14:19 | msandbox@localhost | logged in | NULL |
+----+---------------------+--------------------+-----------+-------+
1 row in set (0.00 sec)
5. Now upgrade to any version > 8028. Here I used 8035.
6. Post upgrade, insert two rows:
INSERT INTO test.user_table(user, action) VALUES (current_user(),'ouch');
INSERT INTO mysql.user_table(user, action) VALUES (current_user(),'ouch');
7. After insert, read the table and you will notice the table in the mysql schema becomes corrupt - note the incomplete data. The user schema table is fine though
mysql [localhost:8035] {msandbox} ((none)) > INSERT INTO test.user_table(user, action) VALUES (current_user(),'ouch');
Query OK, 1 row affected (0.01 sec)
mysql [localhost:8035] {msandbox} ((none)) > INSERT INTO mysql.user_table(user, action) VALUES (current_user(),'ouch');
Query OK, 1 row affected (0.01 sec)
mysql [localhost:8035] {msandbox} ((none)) > select * from test.user_table;
+----+---------------------+--------------------+-----------+-------+
| id | insert_time | user | action | uh_oh |
+----+---------------------+--------------------+-----------+-------+
| 1 | 2023-11-03 02:14:19 | msandbox@localhost | logged in | NULL |
| 2 | 2023-11-03 02:16:41 | msandbox@localhost | ouch | NULL | <--- no corruption
+----+---------------------+--------------------+-----------+-------+
2 rows in set (0.00 sec)
mysql [localhost:8035] {msandbox} ((none)) > select * from mysql.user_table;
+----+---------------------+--------------------+--------------------+-------+
| id | insert_time | user | action | uh_oh |
+----+---------------------+--------------------+--------------------+-------+
| 1 | 2023-11-03 02:14:19 | msandbox@localhost | logged in | NULL |
| 2 | 2023-11-03 02:16:42 | m | sandbox@localhosto | NULL | <---- Corrupt row
+----+---------------------+--------------------+--------------------+-------+
2 rows in set (0.00 sec)
Suggested fix:
Although its prob not a best practice to create tables in the mysql schema, any non-datadict tables should be handled correctly should a instant column be added - it should not lead to corruption, esp since instant is the default algo. Prior to instant ddl changes in 8029, for example upgrade from 8027->8028, this did not occur.
Thanks!
Marc
Description: When a pre 8029 instant column exists on a non-system table with NULL columns in the mysql schema, the table will become corrupted post 8030+ upgrade. I have observed this behavior on all versions 8030+ when upgrading from <= 8028. How to repeat: 1. On 8.0.28, create two identical tables. One in the MySQL schema, another in the test schema. CREATE TABLE `test`.`user_table` ( `id` int AUTO_INCREMENT primary key, `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `user` varchar(50) NOT NULL, `action` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `mysql`.`user_table` ( `id` int auto_increment primary key, `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `user` varchar(50) NOT NULL, `action` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; 2. Insert 1 row in each table INSERT INTO test.user_table(user, action) VALUES (current_user(),'logged in'); INSERT INTO mysql.user_table(user, action) VALUES (current_user(),'logged in'); 3. Add an instant column to each table: alter table test.user_table add column `uh_oh` tinyint(1) DEFAULT NULL, algorithm=instant; alter table mysql.user_table add column `uh_oh` tinyint(1) DEFAULT NULL, algorithm=instant; 4. After the above you will notice the following. Table data is fine, and we have instant cols as expected: mysql [localhost:8028] {msandbox} ((none)) > select * from information_schema.innodb_tables where INSTANT_COLS>0; +----------+------------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+------------------+------+--------+-------+------------+---------------+------------+--------------+ | 1063 | test/user_table | 33 | 8 | 2 | Dynamic | 0 | Single | 4 | | 1064 | mysql/user_table | 33 | 8 | 3 | Dynamic | 0 | Single | 4 | +----------+------------------+------+--------+-------+------------+---------------+------------+--------------+ 2 rows in set (0.01 sec) mysql [localhost:8028] {msandbox} ((none)) > select * from test.user_table; +----+---------------------+--------------------+-----------+-------+ | id | insert_time | user | action | uh_oh | +----+---------------------+--------------------+-----------+-------+ | 1 | 2023-11-03 02:14:19 | msandbox@localhost | logged in | NULL | +----+---------------------+--------------------+-----------+-------+ 1 row in set (0.00 sec) mysql [localhost:8028] {msandbox} ((none)) > select * from mysql.user_table; +----+---------------------+--------------------+-----------+-------+ | id | insert_time | user | action | uh_oh | +----+---------------------+--------------------+-----------+-------+ | 1 | 2023-11-03 02:14:19 | msandbox@localhost | logged in | NULL | +----+---------------------+--------------------+-----------+-------+ 1 row in set (0.00 sec) 5. Now upgrade to any version > 8028. Here I used 8035. 6. Post upgrade, insert two rows: INSERT INTO test.user_table(user, action) VALUES (current_user(),'ouch'); INSERT INTO mysql.user_table(user, action) VALUES (current_user(),'ouch'); 7. After insert, read the table and you will notice the table in the mysql schema becomes corrupt - note the incomplete data. The user schema table is fine though mysql [localhost:8035] {msandbox} ((none)) > INSERT INTO test.user_table(user, action) VALUES (current_user(),'ouch'); Query OK, 1 row affected (0.01 sec) mysql [localhost:8035] {msandbox} ((none)) > INSERT INTO mysql.user_table(user, action) VALUES (current_user(),'ouch'); Query OK, 1 row affected (0.01 sec) mysql [localhost:8035] {msandbox} ((none)) > select * from test.user_table; +----+---------------------+--------------------+-----------+-------+ | id | insert_time | user | action | uh_oh | +----+---------------------+--------------------+-----------+-------+ | 1 | 2023-11-03 02:14:19 | msandbox@localhost | logged in | NULL | | 2 | 2023-11-03 02:16:41 | msandbox@localhost | ouch | NULL | <--- no corruption +----+---------------------+--------------------+-----------+-------+ 2 rows in set (0.00 sec) mysql [localhost:8035] {msandbox} ((none)) > select * from mysql.user_table; +----+---------------------+--------------------+--------------------+-------+ | id | insert_time | user | action | uh_oh | +----+---------------------+--------------------+--------------------+-------+ | 1 | 2023-11-03 02:14:19 | msandbox@localhost | logged in | NULL | | 2 | 2023-11-03 02:16:42 | m | sandbox@localhosto | NULL | <---- Corrupt row +----+---------------------+--------------------+--------------------+-------+ 2 rows in set (0.00 sec) Suggested fix: Although its prob not a best practice to create tables in the mysql schema, any non-datadict tables should be handled correctly should a instant column be added - it should not lead to corruption, esp since instant is the default algo. Prior to instant ddl changes in 8029, for example upgrade from 8027->8028, this did not occur. Thanks! Marc