Bug #52354 'CREATE TABLE .. LIKE ... ' statements fail

• Submitted: 25 Mar 2010 8:50
• Modified: 16 Jun 2011 19:54
• Reporter: Libing Song
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: mysql-6.0-codebase-bugfixing, 5.6.99
• OS: Any
• CPU Architecture: Any
• Tags: regression

[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