Description:
The server does not always report an error when a query was interrupted with KILL QUERY statement. In fact, it alsmost never returns an error.
mysql> drop function f1|
Query OK, 0 rows affected (0.00 sec)
mysql> create function f1() returns int begin declare a int; while 1 do select sleep(1) into a; end while; return 0; end|
Query OK, 0 rows affected (0.00 sec)
mysql> select f1()|
+------+
| f1() |
+------+
| NULL |
+------+
1 row in set (19.05 sec)
mysql> drop table if exists t1;
-> |
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 (a) values (f1()), (f1()), (f1());
Query OK, 3 rows affected (8.48 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- issue KILL QUERY for this query from another connection
-- watch the output:
mysql> select * from t1;
-- as one can see, there is not only no error message, the query did not in
-- fact get fully interrupted (In MySQL, INSERT statement ignores KILL
and tries to run till the end - but the point is that stored function
did not ignore it and returned an error, but this error was ignored
and INSERT continued execution).
To see that a stored function actually does return an error if it was killed one may try the following test case:
mysql> create trigger trg before insert on t1 for each row set new.a=f1();
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 (a) values (1);
-- kill the query from another connection
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from t1;
+------+
| a |
+------+
| NULL |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)
-- trigger execution fails and nothing is inserted into t1:
How to repeat:
See description. Use the statements from the description in one connection
and KILL QUERY statement from another to verify the described behaviour
Suggested fix:
Ensure that KILL QUERY statement causes an error to be produced if execution of a statement was really interrupted.
Every place in the server that aborts in case the connection was killed should be patched to report ER_QUERY_WAS_INTERRUPTED in case thd->killed is set and the query decides to abort.
Note, that the error should be set only if there it is not set yet - since we may check for thd->killed in more than one place in execution stack. An easy way to verify that there is no error in the stack yet is to check thd->net.report_error.
Description: The server does not always report an error when a query was interrupted with KILL QUERY statement. In fact, it alsmost never returns an error. mysql> drop function f1| Query OK, 0 rows affected (0.00 sec) mysql> create function f1() returns int begin declare a int; while 1 do select sleep(1) into a; end while; return 0; end| Query OK, 0 rows affected (0.00 sec) mysql> select f1()| +------+ | f1() | +------+ | NULL | +------+ 1 row in set (19.05 sec) mysql> drop table if exists t1; -> | Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> create table t1 (a int); Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 (a) values (f1()), (f1()), (f1()); Query OK, 3 rows affected (8.48 sec) Records: 3 Duplicates: 0 Warnings: 0 -- issue KILL QUERY for this query from another connection -- watch the output: mysql> select * from t1; -- as one can see, there is not only no error message, the query did not in -- fact get fully interrupted (In MySQL, INSERT statement ignores KILL and tries to run till the end - but the point is that stored function did not ignore it and returned an error, but this error was ignored and INSERT continued execution). To see that a stored function actually does return an error if it was killed one may try the following test case: mysql> create trigger trg before insert on t1 for each row set new.a=f1(); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 (a) values (1); -- kill the query from another connection ERROR 1317 (70100): Query execution was interrupted mysql> select * from t1; +------+ | a | +------+ | NULL | | NULL | | NULL | +------+ 3 rows in set (0.00 sec) -- trigger execution fails and nothing is inserted into t1: How to repeat: See description. Use the statements from the description in one connection and KILL QUERY statement from another to verify the described behaviour Suggested fix: Ensure that KILL QUERY statement causes an error to be produced if execution of a statement was really interrupted. Every place in the server that aborts in case the connection was killed should be patched to report ER_QUERY_WAS_INTERRUPTED in case thd->killed is set and the query decides to abort. Note, that the error should be set only if there it is not set yet - since we may check for thd->killed in more than one place in execution stack. An easy way to verify that there is no error in the stack yet is to check thd->net.report_error.