Description:
As the documentation (https://dev.mysql.com/doc/refman/8.0/en/create-table-like.html) mentions:
"CREATE TABLE ... LIKE does not preserve any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table, or any foreign key definitions."
So it works that way for a regular table:
mysql > create table test1 (id int auto_increment primary key, a varchar(100)) DATA DIRECTORY = '/tmp/msb_8_0_23/data2';
Query OK, 0 rows affected (0.02 sec)
mysql > CREATE TABLE test1_NEW LIKE test1;
Query OK, 0 rows affected (0.02 sec)
mysql > select FILE_NAME,TABLESPACE_NAME from information_schema.files where FILE_NAME like '%db1/test%';
+--------------------------------------------------------+-----------------+
| FILE_NAME | TABLESPACE_NAME |
+--------------------------------------------------------+-----------------+
| ./db1/test1_NEW.ibd | db1/test1_NEW |
| /tmp/msb_8_0_23/data2/db1/test1.ibd | db1/test1 |
+--------------------------------------------------------+-----------------+
2 rows in set (0.00 sec)
However, when I try the same with partitioned table, the copy table does have the data directory option.
How to repeat:
CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
PARTITION BY LIST(YEAR(adate))
(
PARTITION p1999 VALUES IN (1995, 1999, 2003)
DATA DIRECTORY = '/tmp/msb_8_0_23/data2',
PARTITION p2000 VALUES IN (1996, 2000, 2004)
DATA DIRECTORY = '/tmp/msb_8_0_23/data2',
PARTITION p2001 VALUES IN (1997, 2001, 2005)
DATA DIRECTORY = '/tmp/msb_8_0_23/data2',
PARTITION p2002 VALUES IN (1998, 2002, 2006)
DATA DIRECTORY = '/tmp/msb_8_0_23/data2'
);
mysql > CREATE TABLE th_NEW LIKE th;
Query OK, 0 rows affected (0.08 sec)
mysql > show create table th_NEW\G
*************************** 1. row ***************************
Table: th_NEW
Create Table: CREATE TABLE `th_NEW` (
`id` int DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`adate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (year(`adate`))
(PARTITION p1999 VALUES IN (1995,1999,2003) DATA DIRECTORY = '/tmp/msb_8_0_23/data2/' ENGINE = InnoDB,
PARTITION p2000 VALUES IN (1996,2000,2004) DATA DIRECTORY = '/tmp/msb_8_0_23/data2/' ENGINE = InnoDB,
PARTITION p2001 VALUES IN (1997,2001,2005) DATA DIRECTORY = '/tmp/msb_8_0_23/data2/' ENGINE = InnoDB,
PARTITION p2002 VALUES IN (1998,2002,2006) DATA DIRECTORY = '/tmp/msb_8_0_23/data2/' ENGINE = InnoDB) */
1 row in set (0.00 sec)
mysql > select FILE_NAME,TABLESPACE_NAME from information_schema.files where FILE_NAME like '%db1/th%';
+-----------------------------------------------------------------+--------------------+
| FILE_NAME | TABLESPACE_NAME |
+-----------------------------------------------------------------+--------------------+
| /tmp/msb_8_0_23/data2/db1/th#p#p1999.ibd | db1/th#p#p1999 |
| /tmp/msb_8_0_23/data2/db1/th#p#p2000.ibd | db1/th#p#p2000 |
| /tmp/msb_8_0_23/data2/db1/th#p#p2001.ibd | db1/th#p#p2001 |
| /tmp/msb_8_0_23/data2/db1/th#p#p2002.ibd | db1/th#p#p2002 |
| /tmp/msb_8_0_23/data2/db1/th_NEW#p#p1999.ibd | db1/th_NEW#p#p1999 |
| /tmp/msb_8_0_23/data2/db1/th_NEW#p#p2000.ibd | db1/th_NEW#p#p2000 |
| /tmp/msb_8_0_23/data2/db1/th_NEW#p#p2001.ibd | db1/th_NEW#p#p2001 |
| /tmp/msb_8_0_23/data2/db1/th_NEW#p#p2002.ibd | db1/th_NEW#p#p2002 |
+-----------------------------------------------------------------+--------------------+
8 rows in set (0.00 sec)
Suggested fix:
Best to have the behavior consistent regardless of partitioning, but at least change the documentation.
