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.
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.