Bug #52354 'CREATE TABLE .. LIKE ... ' statements fail
Submitted: 25 Mar 2010 8:50 Modified: 16 Jun 2011 19:54
Reporter: Libing Song Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:mysql-6.0-codebase-bugfixing, 5.6.99 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression
Triage: Triaged: D3 (Medium)

[25 Mar 2010 8:50] Libing Song
Description:
Cannot use 'CREATE TABLE .. LIKE t1' statement to create tables if t1 creates with 'INDEX DIRECTORY' option.

The error is "Error 1105 MyISAM table 't2' is in use (most likely by a MERGE table). Try FLUSH TABLES."

How to repeat:
CREATE TABLE t1(c1 INT) INDEX DIRECTORY='/tmp/';
CREATE TABLE t2 LIKE t1;
[25 Mar 2010 9:37] Sveta Smirnova
Thank you for the report.

Verified with different error:

CREATE TABLE t1(c1 INT) INDEX DIRECTORY='/tmp/'' failed: 1: Can't create/write to file '/tmp/t1.MYI' (Errcode: 17)

In version 4.1 I could create  second table.
[25 Mar 2010 9:52] Libing Song
I tested it on the last mysql-6.0-codebase-bugfixing. its version is 60014.

CREATE TABLE t1(c1 INT) INDEX DIRECTORY='/tmp/'' failed: 1: Can't create/write to file '/tmp/t1.MYI' (Errcode: 17)

Do you have the enough privileges ?
[25 Mar 2010 19:54] Sveta Smirnova
Libing,

thank you for noticing this error.

Verified as described with error message like in initial description. Not repeatable with 5.1 and 5.0
[2 Jun 2010 15:13] Mattias Jonsson
Note that in 5.1 it does not create the table with DATA/INDEX DIRECTORY, which it seems to try in 5.5.
[22 May 2011 19:22] Dmitry Lenev
Bug #61169 "CREATE TABLE ... LIKE does not ignore DATA / INDEX DIRECTORY statements" was marked as duplicate of this bug.
[16 Jun 2011 19:54] Paul Dubois
Noted in 5.5.14, 5.6.3 changelogs.

CREATE TABLE ... LIKE for a MyISAM table definition that included an
DATA DIRECTORY or INDEX DIRECTORY table option failed, instead of
creating a table with those option omitted as documented. 

CHANGESET - http://lists.mysql.com/commits/138963