Description:
When running with innodb_file_per_table=0, and a table is altered after creation, subsequent SHOW CREATE TABLE commands will output an explicit TABLESPACE `innodb_system` option, when it should not be set as it was never provided explicitly with the initial CREATE TABLE or any subsequent ALTER TABLE.
Based on code examination and debugging with GDB, this seems to be because the explicit_tablespace option in the data dictionary is being set incorrectly during the ALTER TABLE command.
How to repeat:
mysql [localhost:8032] {msandbox} (test) > create table t (i int not null, primary key (i));
Query OK, 0 rows affected (0.14 sec)
# TABLESPACE option is not present, as expected:
mysql [localhost:8032] {msandbox} (test) > show create table t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`i` int NOT NULL,
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql [localhost:8032] {msandbox} (test) > alter table t add j int not null;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
# TABLESPACE option is present, but should not be:
mysql [localhost:8032] {msandbox} (test) > show create table t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`i` int NOT NULL,
`j` int NOT NULL,
PRIMARY KEY (`i`)
) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
Suggested fix:
Do not set the TABLESPACE option unless actually explicitly set by the user.
Description: When running with innodb_file_per_table=0, and a table is altered after creation, subsequent SHOW CREATE TABLE commands will output an explicit TABLESPACE `innodb_system` option, when it should not be set as it was never provided explicitly with the initial CREATE TABLE or any subsequent ALTER TABLE. Based on code examination and debugging with GDB, this seems to be because the explicit_tablespace option in the data dictionary is being set incorrectly during the ALTER TABLE command. How to repeat: mysql [localhost:8032] {msandbox} (test) > create table t (i int not null, primary key (i)); Query OK, 0 rows affected (0.14 sec) # TABLESPACE option is not present, as expected: mysql [localhost:8032] {msandbox} (test) > show create table t \G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `i` int NOT NULL, PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql [localhost:8032] {msandbox} (test) > alter table t add j int not null; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 # TABLESPACE option is present, but should not be: mysql [localhost:8032] {msandbox} (test) > show create table t \G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `i` int NOT NULL, `j` int NOT NULL, PRIMARY KEY (`i`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) Suggested fix: Do not set the TABLESPACE option unless actually explicitly set by the user.