commit 491f7e36d2c5a5c700220b4cd7de33a9598e82f7 Author: Laurynas Biveinis Date: Thu May 25 12:19:01 2017 +0300 Fix bug 86260 / 1689736 (Assert on KILL'ing a stored routine invocation) sp_lex_instr::exec_core may return non-zero in case of both error and if the query was killed. [1] changed the check for statement reprepare error to assume that non-zero could have been returned only in case of error, choosing the wrong execution path for killed execution. Fix by restoring the check. [1]: commit d7b37d4 Author: Sreeharsha Ramanavarapu Date: Wed Feb 22 11:02:07 2017 +0530 Bug #25053286: USE VIEW WITH CONDITION IN PROCEDURE CAUSES INCORRECT BEHAVIOR Issue: ------ This problem occurs when a stored procedure contains a query with a view. While resolving the columns in the WHERE condition, find_field_in_view insists on creating a Item_direct_view_ref object every time an execution happens. This object is not destroyed at the end of the execution. In the next execution, a new object is created and will be appended to the free_list. Hence the size of the free_list is growing and the cleanup phase at the end of each execute takes increasingly longer time. Solution: --------- This is a regression due to the fix for BUG#19897405. Ideally the Item_direct_view_ref object (which is related to the view's column in WHERE clause) should be created at the beginning of every execution and destroyed at the end. This doesn't happen because the check related to the status (STMT_INITIALIZED_FOR_SP / STMT_EXECUTED) had been removed. This check has been re-introduced. Scenario 1 in BUG#19897405: -------------------------- a) SP is executed--> The view fields are resolved/fixed. b) FLUSH TABLE . c) SP is executed--> Triggers re-prepare--> Query arena state is not reset and remains as 'STMT_EXECUTED'. Previously created Item_direct_view_ref object is destroyed. The view fields are resolved/fixed using the execution mem_root. It creates a new Item_direct_view_ref object, this is destroyed at the end of the execution. d) SP is executed--> Server crashes while trying to access the resolved view columns allocated on the execution mem_root which was freed after execution(c). Solution to Scenario 1 in BUG#19897405: --------------------------------------- The root cause of the problem mentioned in BUG#19897405 is that when a re-prepare error is raised due to a FLUSH TABLE / DROP TABLE, the state of statement arena should be switched to STMT_INITIALIZED_FOR_SP. Without this, the statement is unaware that previously created Item_direct_view_ref have been destroyed after the table re-open. While it creates a new Item_direct_view_ref object, this is destroyed at the end of the execution. Hence the execution that follows will not have any Item_direct_view_ref to use. The fix here is to set the state to STMT_INITIALIZED_FOR_SP after the re-prepare error is raised. Problem 2 in BUG#19897405: -------------------------- a) SP is executed--> The view fields are resolved/fixed. b) SP is executed--> Gets re-prepare error. Destroys Item_direct_view_ref object. At the end "TABLE EXISTS" error is raised and state is set to EXECUTED c) DROP TABLE d) SP is executed--> Creates new item_direct_view_ref object. Destroys the object at the end of the statement. d) DROP TABLE e) SP is executed--> Server crashes while trying to access the resolved view columns allocated on the execution mem_root which was freed after execution. Solution to Scenario 2 in BUG#19897405: --------------------------------------- CREATE TABLE ... SELECT statement in an SP requires some special handling. The state is always set to STMT_INITIALIZED_FOR_SP in such a case. diff --git a/mysql-test/r/bug86260.result b/mysql-test/r/bug86260.result new file mode 100644 index 00000000000..1a16005df4d --- /dev/null +++ b/mysql-test/r/bug86260.result @@ -0,0 +1,18 @@ +# +# Bug 86260: Assert on KILL'ing a stored routine invocation +# +CREATE TABLE t1 (a INT); +CREATE FUNCTION f1() RETURNS INT +BEGIN +INSERT INTO t1 VALUES (1); +RETURN 1; +END| +SET DEBUG_SYNC= "sp_before_exec_core SIGNAL sp_ready WAIT_FOR sp_finish"; +SELECT f1(); +SET DEBUG_SYNC= "now WAIT_FOR sp_ready"; +KILL QUERY sp_con_id; +SET DEBUG_SYNC= "now SIGNAL sp_finish"; +ERROR 70100: Query execution was interrupted +SET DEBUG_SYNC= 'RESET'; +DROP FUNCTION f1; +DROP TABLE t1; diff --git a/mysql-test/t/bug86260.test b/mysql-test/t/bug86260.test new file mode 100644 index 00000000000..715663cb38a --- /dev/null +++ b/mysql-test/t/bug86260.test @@ -0,0 +1,44 @@ +--source include/have_debug_sync.inc +--source include/count_sessions.inc + +--echo # +--echo # Bug 86260: Assert on KILL'ing a stored routine invocation +--echo # + +CREATE TABLE t1 (a INT); + +DELIMITER |; + +CREATE FUNCTION f1() RETURNS INT +BEGIN + INSERT INTO t1 VALUES (1); + RETURN 1; +END| + +DELIMITER ;| + +--connect(con1,localhost,root) + +--connection default +--let $sp_con_id= `SELECT CONNECTION_ID()` +SET DEBUG_SYNC= "sp_before_exec_core SIGNAL sp_ready WAIT_FOR sp_finish"; +send SELECT f1(); + +--connection con1 +SET DEBUG_SYNC= "now WAIT_FOR sp_ready"; +--replace_result $sp_con_id sp_con_id +--eval KILL QUERY $sp_con_id +SET DEBUG_SYNC= "now SIGNAL sp_finish"; + +--connection default +--error ER_QUERY_INTERRUPTED +reap; + +disconnect con1; + +SET DEBUG_SYNC= 'RESET'; + +DROP FUNCTION f1; +DROP TABLE t1; + +--source include/wait_until_count_sessions.inc diff --git a/sql/sp_instr.cc b/sql/sp_instr.cc index 4657f57d66b..21dc9090074 100644 --- a/sql/sp_instr.cc +++ b/sql/sp_instr.cc @@ -30,6 +30,7 @@ #include "prealloced_array.h" #include "binlog.h" #include "item_cmpfunc.h" // Item_func_eq +#include "debug_sync.h" // DEBUG_SYNC #include #include @@ -408,6 +409,8 @@ bool sp_lex_instr::reset_lex_and_exec_core(THD *thd, } else { + DEBUG_SYNC(thd, "sp_before_exec_core"); + error= exec_core(thd, nextp); DBUG_PRINT("info",("exec_core returned: %d", error)); } @@ -467,7 +470,8 @@ bool sp_lex_instr::reset_lex_and_exec_core(THD *thd, */ bool reprepare_error= - error && thd->get_stmt_da()->mysql_errno() == ER_NEED_REPREPARE; + error && thd->is_error() + && thd->get_stmt_da()->mysql_errno() == ER_NEED_REPREPARE; bool is_create_table_select= thd->lex && thd->lex->sql_command == SQLCOM_CREATE_TABLE && thd->lex->select_lex && thd->lex->select_lex->item_list.elements > 0;