| 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: | |
| 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 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
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;