Description:
When adding a column to an empty table InnoDB should skip INSTANT DDL. I don't think there is a real benefit here to using INSTANT as inplace will be just as fast. The side effect here is that when using INSTANT it burns a row version which contributes to the max number of instant row versions a innodb table can have(64).
In isolation this should be ok, but If a user is applying frequent DDL changes this can contribute to hitting the 64 column limit on an existing table.
This is a also a common method of creating new tables for online schema change tools or tools where schema changes may be versioned and incrementally deployed e.g. create table and apply incremental changes on top of them.
How to repeat:
mysql [localhost:8035] {msandbox} (test) > select @@version,@@build_id;
+-----------+------------------------------------------+
| @@version | @@build_id |
+-----------+------------------------------------------+
| 8.0.35 | 6d2f8b8d1160e6ff611567f97b9c5eba916143aa |
+-----------+------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost:8035] {msandbox} (test) > create table test(id int);
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8035] {msandbox} (test) > select * from information_schema.innodb_tables where name like 'test/%';
+----------+-----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+-----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| 1064 | test/test | 33 | 4 | 2 | Dynamic | 0 | Single | 0 | 0 |
+----------+-----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.01 sec)
mysql [localhost:8035] {msandbox} (test) > alter table test add column name varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:8035] {msandbox} (test) > select * from information_schema.innodb_tables where name like 'test/%';
+----------+-----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+-----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| 1064 | test/test | 33 | 5 | 2 | Dynamic | 0 | Single | 0 | 1 |
+----------+-----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.00 sec)
Suggested fix:
Revert to INPLACE instead of INSTANT for tables which contain no data.
Description: When adding a column to an empty table InnoDB should skip INSTANT DDL. I don't think there is a real benefit here to using INSTANT as inplace will be just as fast. The side effect here is that when using INSTANT it burns a row version which contributes to the max number of instant row versions a innodb table can have(64). In isolation this should be ok, but If a user is applying frequent DDL changes this can contribute to hitting the 64 column limit on an existing table. This is a also a common method of creating new tables for online schema change tools or tools where schema changes may be versioned and incrementally deployed e.g. create table and apply incremental changes on top of them. How to repeat: mysql [localhost:8035] {msandbox} (test) > select @@version,@@build_id; +-----------+------------------------------------------+ | @@version | @@build_id | +-----------+------------------------------------------+ | 8.0.35 | 6d2f8b8d1160e6ff611567f97b9c5eba916143aa | +-----------+------------------------------------------+ 1 row in set (0.00 sec) mysql [localhost:8035] {msandbox} (test) > create table test(id int); Query OK, 0 rows affected (0.00 sec) mysql [localhost:8035] {msandbox} (test) > select * from information_schema.innodb_tables where name like 'test/%'; +----------+-----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | +----------+-----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | 1064 | test/test | 33 | 4 | 2 | Dynamic | 0 | Single | 0 | 0 | +----------+-----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ 1 row in set (0.01 sec) mysql [localhost:8035] {msandbox} (test) > alter table test add column name varchar(20); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost:8035] {msandbox} (test) > select * from information_schema.innodb_tables where name like 'test/%'; +----------+-----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | +----------+-----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | 1064 | test/test | 33 | 5 | 2 | Dynamic | 0 | Single | 0 | 1 | +----------+-----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ 1 row in set (0.00 sec) Suggested fix: Revert to INPLACE instead of INSTANT for tables which contain no data.