Bug #15011 error handler for mysql errno in nested block not activated
Submitted: 17 Nov 2005 11:12 Modified: 2 Feb 2006 18:26
Reporter: Guy Harrison Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.17-BK OS:Linux (Linux)
Assigned to: Bugs System CPU Architecture:Any

[17 Nov 2005 11:12] Guy Harrison
Description:
If I have a handler in an "outer" block and a handler on the same error code in an inner block I would expect the inner handler to take precedence for errors that occur in the inner block.  However, the "outer" handler appears to activated in precedence to the inner handler.  

Eg:

BEGIN
   DECLARE CONTINUE HANDLER FOR ....
   BEGIN
       DECLARE CONTINUE HANDLER FOR ....
       StatementX
   END;
END;

If StatementX has an error the 2nd handler should be invoked, shouldn't it?  But it's the first handler that actually fires. 

  
   

How to repeat:
DELIMITER $$ ;

DROP TABLE IF EXISTS ht$$

CREATE TABLE ht (c1 INT PRIMARY KEY)$$

INSERT INTO ht VALUES(1)$$

DROP PROCEDURE IF EXISTS  handler_test$$

CREATE PROCEDURE handler_test() DETERMINISTIC
BEGIN

  DECLARE CONTINUE HANDLER FOR 1062
    SELECT 'Outer handler invoked';

  SELECT version();

  BEGIN
    DECLARE CONTINUE HANDLER FOR 1062
      SELECT 'Inner handler invoked';

    INSERT INTO ht VALUES(1);
  END;
END$$

CALL handler_test()$$

mysql> source scope_error.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.11 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-----------------------------+
| version()                   |
+-----------------------------+
| 5.0.17-nightly-20051114-log |
+-----------------------------+
1 row in set (0.00 sec)

+-----------------------+
| Outer handler invoked |
+-----------------------+
| Outer handler invoked |
+-----------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)
[20 Nov 2005 13:10] Valeriy Kravchuk
Verified just as described on 5.0.17-BK (ChangeSet@1.1969, 2005-11-17 15:08:49+01:00, ...) on Linux:

mysql> delimiter $$
mysql> CREATE TABLE ht (c1 INT PRIMARY KEY)$$

Query OK, 0 rows affected (0,01 sec)

mysql> INSERT INTO ht VALUES(1)$$

Query OK, 1 row affected (0,01 sec)

mysql> DROP PROCEDURE IF EXISTS  handler_test$$
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> CREATE PROCEDURE handler_test() DETERMINISTIC
    -> BEGIN
    ->
    ->   DECLARE CONTINUE HANDLER FOR 1062
    ->     SELECT 'Outer handler invoked';
    ->
    ->   SELECT version();
    ->
    ->   BEGIN
    ->     DECLARE CONTINUE HANDLER FOR 1062
    ->       SELECT 'Inner handler invoked';
    ->
    ->     INSERT INTO ht VALUES(1);
    ->   END;
    -> END$$

Query OK, 0 rows affected (0,02 sec)

mysql> CALL handler_test()$$
+-----------+
| version() |
+-----------+
| 5.0.17    |
+-----------+
1 row in set (0,00 sec)

+-----------------------+
| Outer handler invoked |
+-----------------------+
| Outer handler invoked |
+-----------------------+
1 row in set (0,00 sec)

Query OK, 0 rows affected (0,00 sec)
[25 Jan 2006 15:46] Per-Erik Martin
This only happens for handlers of explicit mysql error numbers.
[25 Jan 2006 16:42] Per-Erik Martin
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=5&mail=47213
[1 Feb 2006 14:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2009
[1 Feb 2006 15:21] Per-Erik Martin
Pushed to bk 5.0.19
[2 Feb 2006 18:26] Mike Hillyer
Documented in 5.0.19 changelog:

     <listitem>
        <para>
          When multiple handlers are created for the same MySQL error
          number within nested blocks, the outermost handler took
          precedence. (Bug #15011)
        </para>
      </listitem>
[15 Feb 2006 16:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2662