MySQL Bugs: #23667: CREATE TABLE LIKE is not isolated from alteration by other connections

Bug #23667	CREATE TABLE LIKE is not isolated from alteration by other connections
Submitted:	26 Oct 2006 14:33	Modified:	7 Jun 2007 16:35
Reporter:	Andrei Elkin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0,5.1	OS:	Any
Assigned to:	Dmitry Lenev	CPU Architecture:	Any
Tags:	rt_q1_2007

Description:
There can 
  come up a warning
  ERROR 6 (HY000): Error on delete of './test/pt1.MYI' (Errcode: 2)
  or 
  possibly crash 
  and always
  wrong record in binlog.
 
when one connection is doing CREATE TABLE pt1 LIKE other
and another intrudes doing DROP table pt1; CREATE table pt1.

To catch the case see description.

How to repeat:
connection create;
create temporary table tt1 (a int); /* tt1 might be persistent too, though i did                          not check */
/* now set a breakpoint with gdb after ha_create_table() to stop in the middle
of */
create table pt1 like tt1;  // we are hanging having pt1 created
/* (gdb) call sleep(1), step by step (n) to yield in favor of the following */

connection abuser;
show create table pt1;  // returns create table pt1 (a int)
drop table pt1;
create table pt1;       // => the WARNING can show up

connection create;
show create table pt1;  // BINLOG problem: returns abuser's version instead of planned tt1 copy

Suggested fix:
name lock pt1 before ha_create_table(pt1) is called.

Bug #18950 create table like does not obtain LOCK_open can relate.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16771

ChangeSet@1.2318, 2006-12-11 17:22:16+03:00, petr@outpost.site +1 -0
  Bug #23667 "CREATE TABLE LIKE is not isolated from alteration by other connections"
  5.0 version of the fix (should probably go to 4.1 as well).
  5.1 fix should be different (due to changes which come with introduction of
  RBR)

See http://lists.mysql.com/commits/19127 for 5.1 version of the patch.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26987

ChangeSet@1.2527, 2007-05-18 17:25:43+04:00, dlenev@mockturtle.local +9 -0
  Tentative patch changing how ALTER TABLE implementation handles table
  locking and invalidation in the most general case (non-temporary table
  nd not simple RENAME or ENABLE/DISABLE KEYS or partitioning command).
  
  See comment for sql/sql_table.cc for more information.
  
  These changes are prerequisite for 5.1 version of fix for bug #23667
  "CREATE TABLE LIKE is not isolated from alteration by other connections"

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27024

ChangeSet@1.2527, 2007-05-19 10:49:56+04:00, dlenev@mockturtle.local +9 -0
  Patch changing how ALTER TABLE implementation handles table locking
  and invalidation in the most general case (non-temporary table and
  not simple RENAME or ENABLE/DISABLE KEYS or partitioning command).
  
  See comment for sql/sql_table.cc for more information.
  
  These changes are prerequisite for 5.1 version of fix for bug #23667
  "CREATE TABLE LIKE is not isolated from alteration by other connections"

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27083

ChangeSet@1.2530, 2007-05-21 20:22:48+04:00, dlenev@mockturtle.local +14 -0
  5.1 version of tentative fix for:
    Bug #23667 "CREATE TABLE LIKE is not isolated from alteration
                by other connections"
    Bug #18950 "CREATE TABLE LIKE does not obtain LOCK_open"
  As well as:
    Bug #25578 "CREATE TABLE LIKE does not require any privileges
                on source table".
  
  The first and the second bugs resulted in various errors and wrong
  binary log order when one tried to execute concurrently CREATE TABLE LIKE
  statement and DDL statements on source table or DML/DDL statements on its
  target table.
  
  The problem was caused by incomplete protection/table-locking against
  concurrent statements implemented in mysql_create_like_table() routine.
  We solve it by simply implementing such protection in proper way.
  Most of actual work for 5.1 was already done by fix for bug 20662 and
  preliminary patch changing locking locking in ALTER TABLE.
  
  The third bug allowed user who didn't have any privileges on table create
  its copy and therefore circumvent privilege check for SHOW CREATE TABLE.
  
  This patch solves this problem by adding privilege check, which was missing.
  
  Finally it also removes some duplicated code from mysql_create_like_table()
  and thus fixes bug #26869 "TABLE_LIST::table_name_length inconsistent with
  TABLE_LIST::table_name".
  
  Questions for reviewers are marked by QQ.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27126

ChangeSet@1.2490, 2007-05-22 17:43:40+04:00, dlenev@mockturtle.local +7 -0
  5.0 version of fix for:
   Bug #23667 "CREATE TABLE LIKE is not isolated from alteration
               by other connections"
   Bug #18950 "CREATE TABLE LIKE does not obtain LOCK_open"
  As well as:
   Bug #25578 "CREATE TABLE LIKE does not require any privileges
               on source table".
  
  The first and the second bugs resulted in various errors and wrong
  binary log order when one tried to execute concurrently CREATE TABLE LIKE
  statement and DDL statements on source table or DML/DDL statements on its
  target table.
  
  The problem was caused by incomplete protection/table-locking against
  concurrent statements implemented in mysql_create_like_table() routine.
  We solve it by simply implementing such protection in proper way (see
  comment for sql_table.cc for details).
  
  The third bug allowed user who didn't have any privileges on table create
  its copy and therefore circumvent privilege check for SHOW CREATE TABLE.
  
  This patch solves this problem by adding privilege check, which was missing.
  
  Finally it also removes some duplicated code from mysql_create_like_table().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27204

ChangeSet@1.2490, 2007-05-23 15:22:13+04:00, dlenev@mockturtle.local +7 -0
  5.0 version of fix for:
   Bug #23667 "CREATE TABLE LIKE is not isolated from alteration
               by other connections"
   Bug #18950 "CREATE TABLE LIKE does not obtain LOCK_open"
  As well as:
   Bug #25578 "CREATE TABLE LIKE does not require any privileges
               on source table".
  
  The first and the second bugs resulted in various errors and wrong
  binary log order when one tried to execute concurrently CREATE TABLE LIKE
  statement and DDL statements on source table or DML/DDL statements on its
  target table.
  
  The problem was caused by incomplete protection/table-locking against
  concurrent statements implemented in mysql_create_like_table() routine.
  We solve it by simply implementing such protection in proper way (see
  comment for sql_table.cc for details).
  
  The third bug allowed user who didn't have any privileges on table create
  its copy and therefore circumvent privilege check for SHOW CREATE TABLE.
  
  This patch solves this problem by adding privilege check, which was missing.
  
  Finally it also removes some duplicated code from mysql_create_like_table().
  
  Note that, altough tests covering concurrency-related aspects of CREATE TABLE
  LIKE behaviour will only be introduced in 5.1, they were run manually for
  this patch as well.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27205

ChangeSet@1.2530, 2007-05-23 15:26:16+04:00, dlenev@mockturtle.local +14 -0
  5.1 version of fix for:
    Bug #23667 "CREATE TABLE LIKE is not isolated from alteration
                by other connections"
    Bug #18950 "CREATE TABLE LIKE does not obtain LOCK_open"
  As well as:
    Bug #25578 "CREATE TABLE LIKE does not require any privileges
                on source table".
  
  The first and the second bugs resulted in various errors and wrong
  binary log order when one tried to execute concurrently CREATE TABLE LIKE
  statement and DDL statements on source table or DML/DDL statements on its
  target table.
  
  The problem was caused by incomplete protection/table-locking against
  concurrent statements implemented in mysql_create_like_table() routine.
  We solve it by simply implementing such protection in proper way.
  Most of actual work for 5.1 was already done by fix for bug 20662 and
  preliminary patch changing locking in ALTER TABLE.
  
  The third bug allowed user who didn't have any privileges on table create
  its copy and therefore circumvent privilege check for SHOW CREATE TABLE.
  
  This patch solves this problem by adding privilege check, which was missing.
  
  Finally it also removes some duplicated code from mysql_create_like_table()
  and thus fixes bug #26869 "TABLE_LIST::table_name_length inconsistent with
  TABLE_LIST::table_name".

Pushed into 5.0.44

Pushed into 5.1.20-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.44 and 5.1.20 changelogs.