Description:
With a running query, a KILL QUERY will nicely kill this. When issuing a KILL QUERY to an idle connection though, the outcome seems unpredictable when it comes to the query following in the previously idle connection. Tested on 32-bit Linux (partly) and Windows. Not all seems to reproduce on Linux, but I have done limited etsting.
1) If a connection has a KILL QUERY sent to it, when idle, and a SELET SLEEP() query is subsequently issued, the query isn't sleeping and immediately returns, with no errors and the usual resulting value 1.
2) If a query that just returns some constant is issued, then it executes normally and will not return any error either and will return the usual result.
3) A sequence of statements issued as a multi-statement clause will hang for ever.
4) If a query is issued that contains a valid from clause, it will return error 1317 (Query was interupted). This is sort-of reasonable, but only sort-of, as I did NOT KILL this query, I killed no query at all as the connection was idle.
5) If a procedure is called that contains 1 or more queries like in 2 above, then the what is returned seems mostly to be the first resultset, follwed by a 1317 error.
6) If a procedure is called, that hasn't been called before in the session, you get, for some reason, error 1457, which looks like a serious problem.
7) If a procedure contains a handler for error 1317, this is sometimes not used and a 1317 will be returned all the same.
8) A show databases command (or a call to mysql_list_dbs()) will return error 1317.
9) A SLEEP query that is running inside a procedure will receive a KILL QUERY alright and stop, but any 1317 handler will be ignored, and the procedure returns with the return value from the SLEEP() as well as error 1317. A normal query in a procedure that is interupted by a KILL QUERY will be handled as expected by a HANDLER.
There are several problems listed here, but I created just 1 bug, as I sense there is a common cause. At least, the way to reproduce it is very similar, and error 1317 seems to be all over the place.
How to repeat:
Using the MySQL commandline:
- Open a connection using the mysql commandline tool. Call this connection 1.
- Open a second connection using the mysql commandline tool. Call this connection 2.
From Connection 2:
- Issue: show processlist.
- In the processlist, identify connection 1.
- Issue KILL QUERY <thread is of connection 1 as above>
The different cases then, using connection 1:
1)
mysql> select sleep(100);
+------------+
| sleep(100) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
-- Note execution time above!
2)
mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
3)
mysql> delimiter //
mysql> select 1; select 2; select 3;//
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
-- Nothing else happens. The connection hangs.
4)
mysql> select c1 from t1;
ERROR 1317 (70100): Query execution was interrupted
5)
The procedure looks like this:
create procedure p5()
begin
select 1;
end//
And in connection 2 then, after the usual KILL QUERY from another session:
mysql> call p5;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
ERROR 1317 (70100): Query execution was interrupted
6)
Exit and reconnect connection 2, and execute the same as above in case 5:
mysql> call p5;
ERROR 1457 (HY000): Failed to load routine test.p5. The table mysql.proc is miss
ing, corrupt, or contains bad data (internal code -2)
7)
The procedure looks like this:
create procedure p7()
begin
DECLARE continue handler for 1317 begin end;
select 1;
end//
And again from connection 2:
mysql> call p7;
ERROR 1317 (70100): Query execution was interrupted
8)
mysql> show databases;
ERROR 1317 (70100): Query execution was interrupted
9)
The procedures looks like this:
create procedure p9()
begin
declare continue handler for 1317 begin end;
select sleep(100);
end//
create procedure p92()
begin
DECLARE continue handler for 1317 begin end;
-- This will run for a long time.
select COUNT(*) FROM t1 as t1_1, t1 as t1_2, t1 as t1_3, t1 as t1_4,
t1 as t1_5, t1 as t1_6, t1 as t1_7, t1 as t1_8, t1 as t1_9,
t1 as t1_10, t1 as t1_11, t1 as t1_12, t1 as t1_13;
end//
In connection 2 execute:
call p9();
In connection 1 do a KILL QUERY on connection 2:
mysql> call p9;
+------------+
| sleep(100) |
+------------+
| 1 |
+------------+
1 row in set (2.08 sec)
ERROR 1317 (70100): Query execution was interrupted
And the the same with the other procedure, killing it while running it:
mysql> call p92;
Empty set (1.22 sec)
Query OK, 0 rows affected (1.24 sec)
Summary
=======
I must point out again that none of these examples, with the exception of example 9, was done with concurrent execution of the KILL QUERY statement and the statement in the example above. Instead, first the KILL QUERY statement was executed (from a different connection. If KILL QUERY is issued from the same connection, nothing happens.
Suggested fix:
Four things:
- Cases 3 and 6 above are unreasonable and needs fixing, somehow.
- Case 7 needs investigation. I am not sure what was interupted, but it should not work like this. Also note the similarity to case 9.
- Documentation should cover how this should work better. As far as I can see, there is little on this in the docs, what it says current is *KILL QUERY terminates the statement that the connection is currently executing, but leaves the connection itself intact.* which obviously isn't true, i.e. the stament that is killed in the examples I list above is executed AFTER the KILL QUERY (with the exception of case 9 above that is).
- Determine if a KILL QUERY really should kill the subsequent query if a connection is idle. I think not. And if it should be like that, it should be documented.
