| Bug #67947 | continue handler not invoked in a stored function after a lock wait timeout | ||
|---|---|---|---|
| Submitted: | 19 Dec 2012 9:22 | Modified: | 14 Nov 2013 0:09 |
| Reporter: | Harald Wirths | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.0,5.1,5.5, 5.7 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | declare continue handler, lock wait timeout, stored function | ||
[24 Dec 2012 17:33]
Sveta Smirnova
Thank you for the report. Verified as described. If remove SELECT FOR UPDATE statement both calls to stored function execute fine.
[14 Nov 2013 0:09]
Paul DuBois
Noted in 5.7.3 changelog. Within a stored function or trigger, occurrence of an ER_LOCK_WAIT_TIMEOUT error caused failure to execute a condition handler.
[4 Dec 2013 11:05]
Laurynas Biveinis
mysql-server$ bzr log -r 6839
------------------------------------------------------------
revno: 6839
committer: Sivert Sorumgard <sivert.sorumgaard@oracle.com>
branch nick: trunk-16041903
timestamp: Fri 2013-11-01 10:41:05 +0100
message:
Bug#16041903 CONTINUE HANDLER NOT INVOKED IN A STORED
FUNCTION AFTER A LOCK WAIT TIMEOUT
Overview
--------
When the SQL layer is executing a sub statement (stored
function or trigger), some errors cannot be safely recovered
until we leave sub statement mode. This is the case for
e.g. ER_LOCK_WAIT_TIMEOUT. When this error is caught, the
handler does a callback to "mark_transaction_for_rollback()"
in the SQL layer, which makes the transaction be rolled back
implicitly. In "mark_transaction_for_rollback()", the flag
"THD::is_fatal_sub_stmt_error" is set. This flag is used to
effectievly disable the execution of a condition handler in
a stored function while handling the error. The flag is
reset when leaving sub statement mode, in
"restore_sub_statement_state()", if leaving the last level
of sub statements (i.e., nested sub statements are handled
by keeping the value of the flag when going to the previous
"stack frame").
The problem here is that the flag is set in
"mark_transaction_for_rollback()" even if the SQL layer is
not in sub statement mode. If this happens, and the
transaction afterwards calls a stored function with a
condition handler, the handler will be disabled since the
flag is already set when calling the function.
Suggested fix
-------------
The suggested fix is to set "is_fatal_sub_stmt_error"
in "mark_transaction_for_rollback()" only in the case where
the SQL layer is currently executing a sub statement
("in_sub_stmt==true"). If not in a sub statement,
setting the "is_fatal_sub_stmt_error" flag really has
no meaning. Additionally, when the flag is set when the SQL
layer is not actually in a sub statement, the flag is never
cleared since this happens only in
"THD::restore_sub_statement_state()" when leaving sub
statement mode.
[5 Oct 2014 8:35]
MySQL Verification Team
Followup bug: Bug 19683834 - SOME INNODB ERRORS CAUSES STORED FUNCTION AND TRIGGER HANDLERS TO BE IGNORED
[24 Apr 2015 10:46]
Laurynas Biveinis
commit aa1de7372846e3a37c87b7488d34426264d7da44
Author: Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com>
Date: Tue Jan 27 13:13:55 2015 +0530
Bug#20094067: BACKPORT BUG#19683834 TO 5.5 AND 5.6
Backporting the patch and the test case fixed as part
of BUG#16041903 and BUG#19683834 respectively.
Description: The problem occurs in a situation, where a modifying statement fails due to error 1205 ("Lock wait timeout exceeded; try restarting transaction"). Calling a stored function after rolling back the previous transaction will not invoke a declared continue handler. Any further call of the stored function delivers the expected result. This bug describes a similar situation to Bug #45609, but differs in calling 'rollback' before calling the stored function. Therefore I reuse the 'How to repeat' - Section of Bug #45609 applying the different sequence of calls. How to repeat: USE test; DROP FUNCTION IF EXISTS sp; DELIMITER // CREATE FUNCTION sp() RETURNS INT SQL SECURITY INVOKER DETERMINISTIC BEGIN DECLARE qty INT DEFAULT 0; DECLARE x INT DEFAULT 0; DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT i FROM t1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur; myloop: LOOP FETCH cur INTO x; IF done THEN LEAVE myloop; END IF; SET qty = qty + x; END LOOP myloop; CLOSE cur; RETURN qty; END // DELIMITER ; SET AUTOCOMMIT=0; CREATE TABLE t1 (i INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2),(3),(4),(5); COMMIT; mysql> SELECT sp(); +------+ | sp() | +------+ | 15 | +------+ 1 row in set (0,00 sec) open another connection to mysql: mysql> USE test; mysql> SET AUTOCOMMIT=0; mysql> SELECT * FROM t1 WHERE i=3 FOR UPDATE; +------+ | i | +------+ | 3 | +------+ 1 row in set (0,00 sec) this puts a lock on the appropriate row. then, on the first connection, try to issue an update command to get an deadlock: mysql> UPDATE t1 SET i=3 WHERE i=3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction rollback the transaction: mysql> ROLLBACK; Query OK, 0 rows affected (0,00 sec) call the stored function: mysql> SELECT sp(); ERROR 1329 (02000): No data - zero rows fetched, selected, or processed call the stored function again: mysql> SELECT sp(); +------+ | sp() | +------+ | 15 | +------+ 1 row in set (0,00 sec)