| 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: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.6.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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]
MySQL Verification Team
Hello Mitsuru, Thank you for the bug report and test case. Verified as described. Thanks, Umesh
[12 Jun 2014 10:19]
MySQL Verification Team
// 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
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.