Bug #86589 CREATE LIKE ignores innodb_file_per_table

• Submitted: 5 Jun 2017 20:53
• Modified: 23 Oct 2017 18:00
• Reporter: Sivert Sørumgård
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.2
• OS: Any
• CPU Architecture: Any

[5 Jun 2017 20:53] Sivert Sørumgård

Description:
From Ingo Struewing: 
The server system variable 'innodb_file_per_table' does not affect the storage location of a table, created as CREATE TABLE ... LIKE. Instead, if t1 is in the system tablespace, t3 will also be created in the system tablespace, regardless of innodb_file_per_table.

How to repeat:
--let $MYSQLD_DATADIR = `SELECT @@datadir`
CREATE DATABASE db1;
SET @@global.innodb_file_per_table=OFF;
CREATE TABLE db1.t1 (c1 INT) ENGINE=InnoDB;
SET @@global.innodb_file_per_table=ON;
CREATE TABLE db1.t2 (c1 INT) ENGINE=InnoDB;
CREATE TABLE db1.t3 LIKE db1.t1;
CREATE TABLE db1.t4 (c1 INT) ENGINE=InnoDB;
--list_files $MYSQLD_DATADIR/db1/
DROP DATABASE db1;

result:

CREATE DATABASE db1;
SET @@global.innodb_file_per_table=OFF;
CREATE TABLE db1.t1 (c1 INT) ENGINE=InnoDB;
SET @@global.innodb_file_per_table=ON;
CREATE TABLE db1.t2 (c1 INT) ENGINE=InnoDB;
CREATE TABLE db1.t3 LIKE db1.t1;
CREATE TABLE db1.t4 (c1 INT) ENGINE=InnoDB;
t2.ibd
t4.ibd
DROP DATABASE db1;

Suggested fix:
Create the new table in the system tablespace or a file per table tablespace depending on the innodb_file_per_table setting.

[5 Jun 2017 20:56] Sivert Sørumgård

Posted by developer:
 
Introduced in WL#9525.

[23 Oct 2017 18:00] Paul DuBois

Posted by developer:
 
Fixed in 8.0.4, 9.0.0 changelogs.

For InnoDB tables, CREATE TABLE ... LIKE did not respect the
innodb_file_per_table system variable setting, and SHOW CREATE TABLE
displayed a TABLESPACE clause even when the user specified no
explicit tablespace during table creation.