Bug #61169 CREATE TABLE ... LIKE does not ignore DATA / INDEX DIRECTORY statements
Submitted: 13 May 2011 13:38 Modified: 22 May 2011 19:21
Reporter: Bojan Dardic Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.5.12, 5.5.15 OS:Any (CentOS 5.6, Mac OS X)
Assigned to: CPU Architecture:Any
Tags: create, data, directory, INDEX, table

[13 May 2011 13:38] Bojan Dardic
Description:
MySQL documention at http://dev.mysql.com/doc/refman/5.5/en/create-table.html describes that CREATE TABLE ... LIKE does not preserve DATA or INDEX DIRECTORY statements.

But when I try to use CREATE TABLE ... LIKE on MyISAM table that has DATA and INDEX DIRECTORY, I get error message: "ERROR 1105 (HY000): MyISAM table 'test456' is in use (most likely by a MERGE table). Try FLUSH TABLES."

This does not occur for MyISAM tables without DATA and INDEX DIRECTORY statements.

How to repeat:
[mysql]> CREATE TABLE `test123` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(255) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM DATA DIRECTORY='/mysql/data1/test/' INDEX DIRECTORY='/mysql/data2/test/';
Query OK, 0 rows affected (0.19 sec)

[mysql]> CREATE TABLE test456 LIKE test123;
ERROR 1105 (HY000): MyISAM table 'test456' is in use (most likely by a MERGE table). Try FLUSH TABLES.
[13 May 2011 14:32] Valeriy Kravchuk
Thank you for the bug report. Verified just as described on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.15-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tmi(c1 int primary key) engine=MyISAM data directory = '/tmp/data' index directory = '/tmp/index';
Query OK, 0 rows affected (0.08 sec)

mysql> create table tmi2 like tmi;
ERROR 1105 (HY000): MyISAM table 'tmi2' is in use (most likely by a MERGE table). Try FLUSH TABLES.
mysql> alter table tmi engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table tmi2 like tmi;
ERROR 1105 (HY000): MyISAM table 'tmi2' is in use (most likely by a MERGE table). Try FLUSH TABLES.
mysql> alter table tmi engine=InnoDB;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tmi engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table tmi2 like tmi;
Query OK, 0 rows affected (0.05 sec)
[22 May 2011 19:21] Dmitry Lenev
Hello!

This bug is duplicate of bug#52354 "'CREATE TABLE .. LIKE ... ' statements fail".

Updating it accordingly.