Bug #24738 create .. select is not isolated properly
Submitted: 30 Nov 2006 22:28 Modified: 16 May 2007 19:32
Reporter: Andrei Elkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.14 OS:Any
Assigned to: Dmitry Lenev CPU Architecture:Any
Tags: rt_q1_2007

[30 Nov 2006 22:28] Andrei Elkin
Description:
to allow modification and DROPping the new table before select's part of
the query will start.

Consider the following:
connection create;                              connection abuser;
create table n3 (id int primary key) \          
                                                drop table n3; // OK
                        select * from o1;
// ERROR 1146 (42S02): Table 'test.n3' 
// doesn't exist

Rather sofisticated objection imo, indeed there were no table `n3' and I was going
to create it.

How to repeat:
1. visual check gets there is no look for new table at create_table_from_items
   since mysql_create_table and till mysql_lock_tables.
2. we need either some high level of concurrency with create .. select connection , e.g many droppers of new table 

I caught the warning at description at the first execution of the following
(no loop to catch the exact line, which must be of connection create)

connect (create,localhost,root,,);
connect (drop0,localhost,root,,);
connect (drop1,localhost,root,,);
connect (drop2,localhost,root,,);
connect (drop3,localhost,root,,);
connect (drop4,localhost,root,,);
connect (drop5,localhost,root,,);
connect (drop6,localhost,root,,);
connect (drop7,localhost,root,,);
drop table if exists old, new;
create table old (n int);

 connection drop0;
 send drop table if exists new;
 connection drop1;
 send drop table if exists new;
 connection drop2;
 send drop table if exists new;
 connection drop3;
 send drop table if exists new;

 connection create;
 send create table new select * from old;

 connection drop4;
 send drop table if exists new;
 connection drop5;
 send drop table if exists new;
 connection drop6;
 send drop table if exists new;
 connection drop7;
 send drop table if exists new;

 connection drop0;
 reap; 
 connection drop1;
 reap;
 connection drop2;
 reap;
 connection drop3;
 reap;

 connection create;
 reap;

 connection drop4;
 reap; 
 connection drop5;
 reap;
 connection drop6;
 reap;
 connection drop7;
 reap;

 connection drop0;
 send drop table if exists new;
 connection drop1;
 send drop table if exists new;
 connection drop2;
 send drop table if exists new;
 connection drop3;
 send drop table if exists new;

 connection create;
 send create table new select * from old;

 connection drop4;
 send drop table if exists new;
 connection drop5;
 send drop table if exists new;
 connection drop6;
 send drop table if exists new;
 connection drop7;
 send drop table if exists new;

 connection drop0;
 reap; 
 connection drop1;
 reap;
 connection drop2;
 reap;
 connection drop3;
 reap;

 connection create;
 reap;

 connection drop4;
 reap; 
 connection drop5;
 reap;
 connection drop6;
 reap;
 connection drop7;
 reap;

 connection drop0;
 send drop table if exists new;
 connection drop1;
 send drop table if exists new;
 connection drop2;
 send drop table if exists new;
 connection drop3;
 send drop table if exists new;

 connection create;
 send create table new select * from old;

 connection drop4;
 send drop table if exists new;
 connection drop5;
 send drop table if exists new;
 connection drop6;
 send drop table if exists new;
 connection drop7;
 send drop table if exists new;

 connection drop0;
 reap; 
 connection drop1;
 reap;
 connection drop2;
 reap;
 connection drop3;
 reap;

 connection create;
 reap;

 connection drop4;
 reap; 
 connection drop5;
 reap;
 connection drop6;
 reap;
 connection drop7;
 reap;

Suggested fix:
lock_and_wait_for_table_name before mysql_create_table and 
unlock_table_name after mysql_lock_tables.
[30 Nov 2006 22:53] Bugs System
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/16260

ChangeSet@1.2374, 2006-12-01 00:52:50+02:00, aelkin@dsl-hkibras-fe30f900-107.dhcp.inet.fi +1 -0
  Bug #24738 create .. select is not isolated properly
  
  The query constists of two separate parts: create new and insert selected.
  The new table can be modified of dropped by an external connection before
  the second inserting part acquires the lock for new table.
  There are several at least undesired things:
  
   1. The user can end up to see
       ERROR 1146 (42S02): Table 'new_table' does not exists.
   2. new table can be DROPed by external connection, and to footprint the fact in binlog,
      whereas CREATE..SELECT would end with the error and did not leave anything
      in binlog. Eventually slave will stop because DROP won't find the new table on slave;
  
   many others.
    
  Fixed with lock_and_wait_for_table_name before mysql_create_table and 
  unlock_table_name after mysql_lock_tables at  create_table_from_items.
[5 Dec 2006 12:55] Dmitry Lenev
ChangeSet@1.2342, 2006-12-05 15:44:34+03:00, dlenev@mockturtle.local +12 -0
  Proposed patch for bugs:
    #20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT
          with locked tables"
    #20903 "Crash when using CREATE TABLE .. SELECT and triggers"
    #24738  "CREATE TABLE ... SELECT is not isolated properly"

http://lists.mysql.com/commits/16454
[25 Apr 2007 23:40] Konstantin Osipov
Approved the second patch by email.
[16 May 2007 13:48] Bugs System
Pushed into 5.0.42
[16 May 2007 13:49] Bugs System
Pushed into 5.1.19-beta
[16 May 2007 19:32] Paul Dubois
Noted in 5.0.42, 5.1.19 changelogs.

Concurrent execution of CREATE TABLE ... SELECT and other statements
involving the target table suffered from various race conditions,
some of which might have led to deadlocks.