Bug #20662 Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT with locked tables
Submitted: 23 Jun 2006 14:23 Modified: 16 May 2007 19:25
Reporter: Ingo Strüwing Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S1 (Critical)
Version:4.0, 4.1, 5.0, 5.1 OS:Linux (Linux)
Assigned to: Dmitry Lenev CPU Architecture:Any
Tags: rt_q1_2007

[23 Jun 2006 14:23] Ingo Strüwing
Description:
A statement "CREATE TABLE IF NOT EXISTS t1 AS SELECT * FROM t2" produces an infinite loop when both tables are read locked.

The loop happens in check_locks() called by thr_lock() by thr_multi_lock() by mysql_lock_tables() by create_table_from_items() by select_create::prepare().

      for (data=lock->read.data ; data ; data=data->next)
      {
	if ((int) data->type == (int) TL_READ_NO_INSERT)
	  count++;
      }

The problem is that the lock data references itself. The check loop doesn't find the end of the list.

'found_errors' is 1 at this moment and the error log contains:
Warning: prev link 1 didn't point at previous lock at read: read lock with no write locks

How to repeat:
BUILD/compile-pentium-debug-max

CREATE TABLE t1 (c1 int);
CREATE TABLE t2 (c1 int);
LOCK TABLE t1 READ, t2 READ;
CREATE TABLE IF NOT EXISTS t1 AS SELECT * FROM t2;
[23 Jun 2006 15:24] Valeriy Kravchuk
Thank you for a bug report. Verified just as described:

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.12-beta-debug |
+-------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (c1 int);
EQuery OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t2 (c1 int);
DQuery OK, 0 rows affected (0.01 sec)

mysql> LOCK TABLE t1 READ, t2 READ;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS t1 AS SELECT * FROM t2;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show warnings\G

While this session is hanging, from another one:

mysql> show processlist;
+----+-----------------+-----------+------+---------+------+----------------+---------------------------------------------------+
| Id | User            | Host      | db   | Command | Time | State          | Info                                              |
+----+-----------------+-----------+------+---------+------+----------------+---------------------------------------------------+
|  1 | event_scheduler | localhost | NULL | Connect |  401 | Suspended      | NULL                                              |
|  4 | root            | localhost | test | Query   |   99 | closing tables | CREATE TABLE IF NOT EXISTS t1 AS SELECT * FROM t2 |
|  5 | root            | localhost | test | Query   |    0 | NULL           | show processlist                                  |
+----+-----------------+-----------+------+---------+------+----------------+---------------------------------------------------+
3 rows in set (0.00 sec)

The "offending" thread can not be KILLed as well.
[5 Dec 2006 12:54] 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
[1 Mar 2007 21:09] Konstantin Osipov
Review comments were sent by email.
[10 May 2007 10:15] 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/26429

ChangeSet@1.2472, 2007-05-10 14:15:42+04:00, dlenev@mockturtle.local +15 -0
  Proposed patch for:
    Bug #20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT
                with locked tables"
    Bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers"
    Bug #24738 "CREATE TABLE ... SELECT is not isolated properly"
    Bug #24508 "Inconsistent results of CREATE TABLE ... SELECT when
                temporary table exists"
   
  Deadlock occured when one tried to execute CREATE TABLE IF NOT
  EXISTS ... SELECT statement under LOCK TABLES which held
  read lock on target table.
  Attempt to execute the same statement for already existing
  target table with triggers caused server crashes.
  Also concurrent execution of CREATE TABLE ... SELECT statement
  and other statements involving target table suffered from
  various races (some of which might've led to deadlocks).
  Finally, attempt to execute CREATE TABLE ... SELECT in case
  when a temporary table with same name was already present
  led to the insertion of data into this temporary table and
  creation of empty non-temporary table.
   
  All above problems stemmed from the old implementation of CREATE
  TABLE ... SELECT in which we created, opened and locked target
  table without any special protection in a separate step and not
  with the rest of tables used by this statement.
  This underminded deadlock-avoidance approach used in server
  and created window for races. It also excluded target table
  from prelocking causing problems with trigger execution.
    
  The patch solves these problems by implementing new approach to
  handling of CREATE TABLE ... SELECT for base tables.
  We try to open and lock table to be created at the same time as
  the rest of tables used by this statement. If such table does not
  exist at this moment we create and place in the table cache special
  placeholder for it which prevents its creation or any other usage
  by other threads.
  
  We still use old approach for creation of temporary tables.
  
  Also note that we decided to postpone introduction of some tests
  for concurrent behaviour of CREATE TABLE ... SELECT till 5.1.
  The main reason for this is absence in 5.0 ability to set @@debug
  variable at runtime, which can be circumvented only by using several
  test files with individual .opt files. Since the latter is likely
  to slowdown test-suite unnecessary we chose not to push this tests
  into 5.0, but run them manually for this version and later push
  their optimized version into 5.1
[11 May 2007 16:34] 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/26513

ChangeSet@1.2472, 2007-05-11 20:33:13+04:00, dlenev@mockturtle.local +15 -0
  Fix for:
    Bug #20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT
                with locked tables"
    Bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers"
    Bug #24738 "CREATE TABLE ... SELECT is not isolated properly"
    Bug #24508 "Inconsistent results of CREATE TABLE ... SELECT when
                temporary table exists"
   
  Deadlock occured when one tried to execute CREATE TABLE IF NOT
  EXISTS ... SELECT statement under LOCK TABLES which held
  read lock on target table.
  Attempt to execute the same statement for already existing
  target table with triggers caused server crashes.
  Also concurrent execution of CREATE TABLE ... SELECT statement
  and other statements involving target table suffered from
  various races (some of which might've led to deadlocks).
  Finally, attempt to execute CREATE TABLE ... SELECT in case
  when a temporary table with same name was already present
  led to the insertion of data into this temporary table and
  creation of empty non-temporary table.
   
  All above problems stemmed from the old implementation of CREATE
  TABLE ... SELECT in which we created, opened and locked target
  table without any special protection in a separate step and not
  with the rest of tables used by this statement.
  This underminded deadlock-avoidance approach used in server
  and created window for races. It also excluded target table
  from prelocking causing problems with trigger execution.
    
  The patch solves these problems by implementing new approach to
  handling of CREATE TABLE ... SELECT for base tables.
  We try to open and lock table to be created at the same time as
  the rest of tables used by this statement. If such table does not
  exist at this moment we create and place in the table cache special
  placeholder for it which prevents its creation or any other usage
  by other threads.
  
  We still use old approach for creation of temporary tables.
  
  Also note that we decided to postpone introduction of some tests
  for concurrent behaviour of CREATE TABLE ... SELECT till 5.1.
  The main reason for this is absence in 5.0 ability to set @@debug
  variable at runtime, which can be circumvented only by using several
  test files with individual .opt files. Since the latter is likely
  to slowdown test-suite unnecessary we chose not to push this tests
  into 5.0, but run them manually for this version and later push
  their optimized version into 5.1
[11 May 2007 17:51] 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/26523

ChangeSet@1.2510, 2007-05-11 21:51:03+04:00, dlenev@mockturtle.local +17 -0
  Fix for:
    Bug #20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT
                with locked tables"
    Bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers"
    Bug #24738 "CREATE TABLE ... SELECT is not isolated properly"
    Bug #24508 "Inconsistent results of CREATE TABLE ... SELECT when
                temporary table exists"
  
  Deadlock occured when one tried to execute CREATE TABLE IF NOT
  EXISTS ... SELECT statement under LOCK TABLES which held
  read lock on target table.
  Attempt to execute the same statement for already existing
  target table with triggers caused server crashes.
  Also concurrent execution of CREATE TABLE ... SELECT statement
  and other statements involving target table suffered from
  various races (some of which might've led to deadlocks).
  Finally, attempt to execute CREATE TABLE ... SELECT in case
  when a temporary table with same name was already present
  led to the insertion of data into this temporary table and
  creation of empty non-temporary table.
   
  All above problems stemmed from the old implementation of CREATE
  TABLE ... SELECT in which we created, opened and locked target
  table without any special protection in a separate step and not
  with the rest of tables used by this statement.
  This underminded deadlock-avoidance approach used in server
  and created window for races. It also excluded target table
  from prelocking causing problems with trigger execution.
  
  The patch solves these problems by implementing new approach to
  handling of CREATE TABLE ... SELECT for base tables.
  We try to open and lock table to be created at the same time as
  the rest of tables used by this statement. If such table does not
  exist at this moment we create and place in the table cache special
  placeholder for it which prevents its creation or any other usage
  by other threads.
  We still use old approach for creation of temporary tables.
  
  Note that we have separate fix for 5.0 since there we use slightly
  different less intrusive approach.
[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:25] Paul DuBois
Noted in 5.0.42, 5.1.19 changelogs.

Deadlock occurred for attempts to execute CREATE TABLE IF NOT EXISTS
... SELECT when LOCK TABLES had been used to acquire a read lock on
the target table.
[22 Oct 2007 17:03] Bugs System
Pushed into 5.1.23-beta
[22 Oct 2007 17:05] Bugs System
Pushed into 5.0.52