MySQL Bugs: #18811: kill "create temporary ... select" and mysql "repair by sorting"

Bug #18811	kill "create temporary ... select" and mysql "repair by sorting"
Submitted:	5 Apr 2006 14:38	Modified:	15 Jul 2008 0:19
Reporter:	Chris Moore	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	4.0.31-BK, 4.0.28-BK, 4.0.26-standard-log	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When you kill a "create temporary ... select" query, mysql shows the state of the query as "repair by sorting" for a period before it drops the connection. During this period, table locks on the connection are held. I believe this time is wasted and that the table can be immediately dropped because the connection will be closed anyways (thus dropping the temporary table).

How to repeat:
Create a table with a large amount of data. Enough to cause at least several minutes of processing time in doing a "create temporary table tmp select * from large_table". Kill the query 3/4 of the way through and do a "show processlist". It should show up as "Repair by sorting".

Suggested fix:
Check if the table is a temporary before repairing it. If it's temporary, just drop it and abort the connection.

Thank you for a problem report. Please, send your my.cnf file content and a complete test case, with CREATE TABLE for that large_table and some way to put a needed amount of data into it. Please, also try to repeat with a newer version, 4.0.26.

I was not able to repeat the behaviour you described with 4.0.27, default server variables values and large_table with 200K of rows. I've got:

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

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+-----------------------
-------------------------------+
| Id | User | Host      | db   | Command | Time | State | Info
                               |
+----+------+-----------+------+---------+------+-------+-----------------------
-------------------------------+
|  3 | root | localhost | test | Query   | 0    | NULL  | show processlist
                               |
|  4 | root | localhost | test | Killed  | 2    | end4  | create temporary table
 ttbg select t1.* from tbg2 t1 |
+----+------+-----------+------+---------+------+-------+-----------------------
-------------------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  3 | root | localhost | test | Query   | 0    | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

Hence the questions.

I have no means to test this on different versions of MySQL, currently. If I ever get to the latest release and see this happening, I guess I'll resubmit. Thanks.

Sorry for a long delay with this report. I am still unable to repeat the behaviour you described... What state the CREATE TEMPORARY TABLE ... query is in when you KILL it? 

I am not able to repeat if it is killed while in "Sending data" state.

Looks like it's still doing it in 4.0.26 for me. Perhaps it is because it's creating the temp table with keys that it thinks it needs to repair the indexes before exiting.

| 2777381 | root | 172.16.1.20:35227 | my_db | Query   | 189  | Sending data | create temporary table otmp0 (key(contact_id), key(domain_tld_id)) /* label */ select contacts.conta |

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

| 2777381 | root | 172.16.1.20:35227 | my_db | Killed  | 206  | Repair by sorting | create temporary table otmp0 (key(contact_id), key(domain_tld_id)) /* label */ select contacts.conta |

Verified with 4.0.28-BK on Linux. To demonstrate "hanging" in "Repair by sorting" state you need to create large enough temporary table with KEYS (!):

mysql> select count(*) from tbg2;
+----------+
| count(*) |
+----------+
|   262144 |
+----------+
1 row in set (0.01 sec)

mysql> desc tbg2;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| c1    | int(11)   |      |     | 0       |       |
| c2    | char(200) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create temporary table ttbig2 (key(c1), key(c2)) select t1.* from tbg2 t
1, tbg2 t2;
ERROR 1053: Server shutdown in progress

And, in the other session:

mysql> show processlist;
+----+------+-----------+------+---------+------+--------------+----------------
--------------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State        | Info
                                                                    |
+----+------+-----------+------+---------+------+--------------+----------------
--------------------------------------------------------------------+
|  4 | root | localhost | NULL | Query   | 0    | NULL         | show processlis
t                                                                   |
|  6 | root | localhost | test | Query   | 2    | Sending data | create temporar
y table ttbig2 (key(c1), key(c2)) select t1.* from tbg2 t1, tbg2 t2 |
+----+------+-----------+------+---------+------+--------------+----------------
--------------------------------------------------------------------+
2 rows in set (0.00 sec)

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

mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------+-----------
-------------------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State             | Info
                                                                         |
+----+------+-----------+------+---------+------+-------------------+-----------
-------------------------------------------------------------------------+
|  4 | root | localhost | NULL | Query   | 0    | NULL              | show proce
sslist                                                                   |
|  6 | root | localhost | test | Killed  | 4    | Repair by sorting | create tem
porary table ttbig2 (key(c1), key(c2)) select t1.* from tbg2 t1, tbg2 t2 |
+----+------+-----------+------+---------+------+-------------------+-----------
-------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  4 | root | localhost | NULL | Query   | 0    | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.01 sec)

For a really large temporary table it can take some time...

The bug is still repeatable with 4.0.31-BK. But it is NOT repeatable with latest 5.0.46-BK. So, looks like 4.0-specific bug.

Not repeatable on 5.1.21-BK.

Won't be fixed in extended maintenance version.