Bug #72985 CREATE TABLE LIKE produce a table with wrong tablespace location.
Submitted: 12 Jun 2014 9:28 Modified: 12 Jun 2014 10:14
Reporter: Mitsuru Oka Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.6.19 OS:Any
Assigned to: CPU Architecture:Any

[12 Jun 2014 9:28] Mitsuru Oka
Description:
CREATE TABLE LIKE for partitioned table with DATA DIRECTORY option will produce table with wrong tablespace location.

Please review "How to repeat" section. Original partitioned table pt1 contains a partition p00 with tablespace location /usr/local/tmp/data. Then, table pt2 which is created by "CREATE TABLE pt2 LIKE pt1" statement will contains partition p00 with tablespace location /usr/local/tmp/data/pt1#P#p00#TMP#. 

How to repeat:
# mysql -uroot -p test

mysql> CREATE TABLE `pt1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `time` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
PARTITION BY RANGE (id)
(PARTITION p00 VALUES LESS THAN (100000000) ENGINE = InnoDB,
 PARTITION p01 VALUES LESS THAN (200000000) ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN (300000000) ENGINE = InnoDB,
 PARTITION pX VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (10.08 sec)

mysql> ALTER TABLE pt1 REORGANIZE PARTITION p00 INTO(
  PARTITION p00 VALUES LESS THAN (100000000) DATA DIRECTORY '/usr/local/tmp/data' );
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pt1\G
*************************** 1. row ***************************
       Table: pt1
Create Table: CREATE TABLE `pt1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `time` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p00 VALUES LESS THAN (100000000) DATA DIRECTORY = '/usr/local/tmp/data' ENGINE = InnoDB,
 PARTITION p01 VALUES LESS THAN (200000000) ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN (300000000) ENGINE = InnoDB,
 PARTITION pX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> create table pt2 like pt1;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table pt2\G
*************************** 1. row ***************************
       Table: pt2
Create Table: CREATE TABLE `pt2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `time` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p00 VALUES LESS THAN (100000000) DATA DIRECTORY = '/usr/local/tmp/data/pt1#P#p00#TMP#' ENGINE = InnoDB,
 PARTITION p01 VALUES LESS THAN (200000000) ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN (300000000) ENGINE = InnoDB,
 PARTITION pX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

# find /usr/local/tmp/data -ls
2147656824    0 drwxrwx---   7 mysql    mysql         106  6月 12 17:28 /usr/local/tmp/data
2147656598    0 drwxrwx---   2 mysql    mysql          26  6月 12 17:25 /usr/local/tmp/data/test
2147656599   96 -rw-rw----   1 mysql    mysql       98304  6月 12 17:25 /usr/local/tmp/data/test/pt1#P#p00.ibd
   812    0 drwxrwx---   3 mysql    mysql          17  6月 12 17:28 /usr/local/tmp/data/pt1#P#p00#TMP#
2147656494    0 drwxrwx---   2 mysql    mysql          26  6月 12 17:28 /usr/local/tmp/data/pt1#P#p00#TMP#/test
2147656495  128 -rw-rw----   1 mysql    mysql       98304  6月 12 17:28 /usr/local/tmp/data/pt1#P#p00#TMP#/test/pt2#P#p00.ibd

Suggested fix:
A partitioned table created by CREATE TABLE LIKE statement should contains same tablespace location as an original table.
[12 Jun 2014 9:40] Mitsuru Oka
Correction:
> A partitioned table created by CREATE TABLE LIKE statement should contains same tablespace location as an original table.

According to MySQL 5.6 documentation (https://dev.mysql.com/doc/refman/5.6/en/create-table.html), 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.
[12 Jun 2014 10:14] Umesh Shastry
Hello Mitsuru,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh
[12 Jun 2014 10:19] Umesh Shastry
// confirmed on 5.6.17/19

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  CREATE TABLE `pt1` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `time` bigint(20) unsigned NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB
    -> PARTITION BY RANGE (id)
    -> (PARTITION p00 VALUES LESS THAN (100000000) ENGINE = InnoDB,
    ->  PARTITION p01 VALUES LESS THAN (200000000) ENGINE = InnoDB,
    ->  PARTITION p02 VALUES LESS THAN (300000000) ENGINE = InnoDB,
    ->  PARTITION pX VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (0.72 sec)

mysql> \! mkdir -p /tmp/data111
mysql> ALTER TABLE pt1 REORGANIZE PARTITION p00 INTO(
    ->   PARTITION p00 VALUES LESS THAN (100000000) DATA DIRECTORY '/tmp/data111' );
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  show create table pt1\G
*************************** 1. row ***************************
       Table: pt1
Create Table: CREATE TABLE `pt1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `time` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p00 VALUES LESS THAN (100000000) DATA DIRECTORY = '/tmp/data111' ENGINE = InnoDB,
 PARTITION p01 VALUES LESS THAN (200000000) ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN (300000000) ENGINE = InnoDB,
 PARTITION pX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.03 sec)

mysql> create table pt2 like pt1;
Query OK, 0 rows affected (0.21 sec)

mysql> show create table pt2\G
*************************** 1. row ***************************
       Table: pt2
Create Table: CREATE TABLE `pt2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `time` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p00 VALUES LESS THAN (100000000) DATA DIRECTORY = '/tmp/data111/pt1#P#p00#TMP#' ENGINE = InnoDB,
 PARTITION p01 VALUES LESS THAN (200000000) ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN (300000000) ENGINE = InnoDB,
 PARTITION pX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.01 sec)

mysql> \! ls -l /tmp/data111
total 8
drwxrwx--- 3 root root 4096 Jun 14 06:22 pt1#P#p00#TMP#
drwxrwx--- 2 root root 4096 Jun 14 06:21 test
mysql> \! ls -l /tmp/data111/test
total 96
-rw-rw---- 1 root root 98304 Jun 14 06:21 pt1#P#p00.ibd