Bug #30701 Kill a RENAME does not allow waiting SELECTS to proceed
Submitted: 29 Aug 2007 17:08 Modified: 18 Jun 2008 18:21
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0.45-community OS:Linux (FC4)
Assigned to: CPU Architecture:Any
Tags: qc

[29 Aug 2007 17:08] Dave Pullin
Description:
Clients issue SQL statements in this order

1. A very long running SELECT on table TEMP
2. A RENAME TABLE of TEMP
3. Another SELECT on table TEMP
4  KILL of the PID for #2 (the RENAME).

Before the KILL, process 2 and 3 are both 'waiting for table'
(#3 is waiting because of the RENAME, which is waiting because of the #1).

After the KILL, #3 continues to 'wait for table' but there is no reason for it to do so.

How to repeat:
Create a table 'temp' that can used to create an extremely slow select.
I do it this way starting from a general purpose table that contains the numbers 0 to 2^32

drop  table if exists temp,temp1,temp2;
create table temp select * from numbers;
create table temp1 select * from numbers;

start the slow select:

select count(*) from temp as a, temp as b, temp as c
 where a.number+b.number+c.number = 0

on another thread (or console session), start the rename

rename table temp to temp2,temp1 to temp

on another thread, start another slow query

select count(*) from temp as a, temp as b, temp as c
 where a.number+b.number+c.number = 0

on another thread

show processlist;
mysql> show processlist;
+-----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------------------+
| Id  | User | Host      | db   | Command | Time | State             | Info                                                                                       |
+-----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------------------+
| 103 | root | localhost | test | Query   |   79 | Sending data      | select count(*) from temp as a, temp as b, temp as c
 where a.number+b.number+c.number = 0 | 
| 105 | root | localhost | test | Query   |   44 | Waiting for table | select count(*) from temp as a, temp as b, temp as c
 where a.number+b.number+c.number = 0 | 
| 108 | root | localhost | test | Query   |    0 | NULL              | show processlist                                                                           | 
| 110 | root | localhost | test | Query   |   61 | Waiting for table | rename table temp to temp2,temp1 to temp                                                   | 
+-----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------------------+

Select the pid for the RENAME (in my case it is 110).

KILL 110

mysql> kill 110
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+-----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------------------+
| Id  | User | Host      | db   | Command | Time | State             | Info                                                                                       |
+-----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------------------+
| 103 | root | localhost | test | Query   |  132 | Sending data      | select count(*) from temp as a, temp as b, temp as c
 where a.number+b.number+c.number = 0 | 
| 105 | root | localhost | test | Query   |   97 | Waiting for table | select count(*) from temp as a, temp as b, temp as c
 where a.number+b.number+c.number = 0 | 
| 108 | root | localhost | test | Query   |    0 | NULL              | show processlist                                                                           | 
+-----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Show processlist again after a while to give MySQL a chance to spot that the second SELECT should not be waiting.

mysql> show processlist;
+-----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------------------+
| Id  | User | Host      | db   | Command | Time | State             | Info                                                                                       |
+-----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------------------+
| 103 | root | localhost | test | Query   |  259 | Sending data      | select count(*) from temp as a, temp as b, temp as c
 where a.number+b.number+c.number = 0 | 
| 105 | root | localhost | test | Query   |  224 | Waiting for table | select count(*) from temp as a, temp as b, temp as c
 where a.number+b.number+c.number = 0 | 
| 108 | root | localhost | test | Query   |    0 | NULL              | show processlist                                                                           | 
+-----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

mysql> 

and again

mysql> show processlist;
+-----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------------------+
| Id  | User | Host      | db   | Command | Time | State             | Info                                                                                       |
+-----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------------------+
| 103 | root | localhost | test | Query   |  412 | Sending data      | select count(*) from temp as a, temp as b, temp as c
 where a.number+b.number+c.number = 0 | 
| 105 | root | localhost | test | Query   |  377 | Waiting for table | select count(*) from temp as a, temp as b, temp as c
 where a.number+b.number+c.number = 0 | 
| 108 | root | localhost | test | Query   |    0 | NULL              | show processlist                                                                           | 
+-----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> 

After 5 miuutes the second query is still 'waiting for table'.

Suggested fix:
after a KILL, the server should re-evaluate whether the conditions for waiting are still valid.
[27 Sep 2007 10:29] Sveta Smirnova
test case

Attachment: bug30701.test (application/octet-stream, text), 1.58 KiB.

[27 Sep 2007 10:29] Sveta Smirnova
Thank you for the report.

Verified as described using attached test case.
[27 Nov 2007 11:02] Konstantin Osipov
The reason the second SELECT blocks is that RENAME has already marked the tables of the first SELECT for FLUSH, and they can not be flushed until the first SELECT has completed. While a table is marked for flush from the table cache, no other DML statement can use this table.
This will be fixed in 6.0 by WL#3726.
[28 Nov 2007 12:38] Dmitry Lenev
One of issues mentioned in bug #31479 is the same as issue raised in this bug report.
[18 Jun 2008 10:41] Konstantin Osipov
Please re-verify against bzr_mysql-6.0-3726
Thank you.
[18 Jun 2008 18:21] Sveta Smirnova
Bug is not repeatable with mysql-6.0-3726 tree.