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:
None 
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

[19 Dec 2012 9:22] Harald Wirths
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)
[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.