MariaDB/MySQL Error HANDLERs: advanced uses

This article explains some advanced uses for MySQL and MariaDB’s error HANDLERs. Some information is in MySQL documentation, but it’s rather sparse. Also, while MySQL manual is probably the best manual ever, I’ve found a major bug in errors documentation. So, these use cases are not so obvious. At least they weren’t for me, so I had to test them.

Notes about the examples in this article

All the examples are tested on MariaDB 10.0.3, but I’m sure that there is not difference in MySQL 5.6, and there should be no difference in any 5.* version.

In these examples we need to produce an error to test the server’s behavior. We’ll always use the same method: we’ll declare a `cur` cursor and we’ll try to CLOSE it without OPENing it – this causes a 1326 error ‘Cursor is not open’.

SELECTs are used to show what path the execution takes.

Fight fire with fire

When an error happens within a CONTINUE HANDLER (not an EXIT HANDLER, though), another HANDLER can be triggered. However, this can not lead to infinite loops (at least from my tests).

USE `test`;
DROP PROCEDURE IF EXISTS `sp1`;
DELIMITER ||
CREATE PROCEDURE `sp1`()
BEGIN
    DECLARE `cur` CURSOR FOR
        SELECT 1 LIMIT 0;
    
    DECLARE CONTINUE HANDLER
        FOR 1326
    BEGIN
        SELECT 'cursor not open';
        -- no loop here
        CALL `sp1`();
    END;
    
    DECLARE CONTINUE HANDLER
        FOR 1146
    BEGIN
        SELECT 'table doesnt exist';
        CLOSE `cur`;
    END;
    
    DECLARE CONTINUE HANDLER
        FOR 1456
    BEGIN
        SELECT 'recursion not allowed';
        SELECT 1 FROM `not-exists`;
    END;
    
    CALL `sp1`();
END;
||
DELIMITER ;
CALL `sp1`();

Output:

MariaDB [test]> CALL `sp1`();
+-----------------------+
| recursion not allowed |
+-----------------------+
| recursion not allowed |
+-----------------------+
1 row in set (0.01 sec)

+--------------------+
| table doesnt exist |
+--------------------+
| table doesnt exist |
+--------------------+
1 row in set (0.51 sec)

+-----------------+
| cursor not open |
+-----------------+
| cursor not open |
+-----------------+
1 row in set (1.02 sec)

ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth vari
able) was exceeded for routine sp1

Inner blocks

I’m not sure that this feature is widely known, but a Stored Program can contain nested BEGIN..END blocks. Each block can contain its own declared objects (local variables, named error conditions, cursors, handlers) and inherit or override declared objects from the outer blocks. In inner blocks, EXIT HANDLER means “exit from the current block”, not from the whole program.

This example shows a Procedure which declares a HANDLER for 1326 error and an inner block. The inner block overrides that HANDLER, and triggers the error. Only the inner HANDLER is used.

DROP PROCEDURE IF EXISTS `p1`;
DELIMITER ||
CREATE PROCEDURE `p1`()
BEGIN
    DECLARE `cur` CURSOR FOR
        SELECT 1 LIMIT 0;
    
    DECLARE EXIT HANDLER
        FOR 1326
    BEGIN
        SELECT 'outer handler';
    END;
    
    BEGIN
        DECLARE EXIT HANDLER
            FOR 1326
        BEGIN
            SELECT 'inner handler';
        END;
        SELECT 'p1 handler';
        CLOSE `cur`;
    END;
    
    -- error is not RESIGNAL'd, so execution continues
    SELECT 'after begin..end';
END;
||
DELIMITER ;
CALL `p1`();

Output:

MariaDB [test]> CALL `p1`();
+------------+
| p1 handler |
+------------+
| p1 handler |
+------------+
1 row in set (0.40 sec)

+---------------+
| inner handler |
+---------------+
| inner handler |
+---------------+
1 row in set (1.01 sec)

+------------------+
| after begin..end |
+------------------+
| after begin..end |
+------------------+
1 row in set (1.67 sec)

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

Nested HANDLERs

HANDLERs contain a BEGIN..END block, too (or a DO clause: semantically there is no difference). In this block, the same rules apply. So HANDLERs can be nested.

In the following example, when p1() outmost block produces an error the execution enters the outer HANDLER. When the outer HANDLER produces an error, the execution enters the inner HANDLER.

DROP PROCEDURE IF EXISTS `p1`;
DELIMITER ||
CREATE PROCEDURE `p1`()
BEGIN
    DECLARE `cur` CURSOR FOR
        SELECT 1 LIMIT 0;
    
    -- can be both CONTINUE or EXIT
    DECLARE EXIT HANDLER
        FOR 1326
    BEGIN
        DECLARE CONTINUE HANDLER
            FOR 1326
        BEGIN
            SELECT 'inner handler';
        END;
        SELECT 'outer handler';
        CLOSE `cur`;
    END;
    SELECT 'p1';
    CLOSE `cur`;
END;
||
DELIMITER ;
CALL `p1`();

Output:

MariaDB [test]> CALL `p1`();
+----+
| p1 |
+----+
| p1 |
+----+
1 row in set (0.02 sec)

+---------------+
| outer handler |
+---------------+
| outer handler |
+---------------+
1 row in set (0.52 sec)

+---------------+
| inner handler |
+---------------+
| inner handler |
+---------------+
1 row in set (1.00 sec)

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

Caller and callee’s HANDLERs precedence

If a Stored Program calls or triggers another Stored Program, the HANDLERs in the callee program have the precedence over caller’s HANDLERs. This happens even if the caller has a HANDLER for the specific error number (usually high precedence) and the callee has a HANDLER for a whole error class (usually low precedence).

Of course this only applies if the callee has a HANDLER for the proper error class, or the proper SQLSTATE.

USE test;
DROP PROCEDURE IF EXISTS `callee`;
DROP PROCEDURE IF EXISTS `caller`;
DELIMITER ||
CREATE PROCEDURE `callee`()
BEGIN
    DECLARE `cur` CURSOR FOR
        SELECT 1 LIMIT 0;
    
    DECLARE CONTINUE HANDLER
        FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
    BEGIN
        SELECT 'callee handler';
    END;
    
    CLOSE `cur`;
END;
 
CREATE PROCEDURE `caller`()
BEGIN
    DECLARE CONTINUE HANDLER
        FOR 1326
    BEGIN
        SELECT 'caller handler';
    END;
    
    CALL `callee`();
END;
||
DELIMITER ;
CALL `caller`();

Output:

MariaDB [test]> CALL `caller`();
+----------------+
| callee handler |
+----------------+
| callee handler |
+----------------+
1 row in set (0.01 sec)

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

Cannot LEAVE a HANDLER

While MySQL documentation clearly states this “limitation”, I’ve seen people trying to use the LEAVE statement to exit a HANDLER block. This can’t be done in the most obvious way, but if you want to exit a whole Procedure or Trigger, then you can do something like this:

USE test;
DROP PROCEDURE IF EXISTS `sp1`;
DELIMITER ||
CREATE PROCEDURE `sp1`()
`lbl_proc`:
BEGIN
    DECLARE found_error BOOLEAN DEFAULT FALSE;
    DECLARE `cur` CURSOR FOR
        SELECT 1 LIMIT 0;
    
    DECLARE CONTINUE HANDLER
        FOR 1326
    `lbl_handler`:
    BEGIN
        SELECT 'cursor not open';
        -- exit procedure
        SET found_error = TRUE;
        LEAVE `lbl_handler`;
    END;
    
    CLOSE `cur`;
    IF found_error IS TRUE THEN
        LEAVE `lbl_proc`;
    END IF;
    SELECT 'go on...';
END;
||
DELIMITER ;
CALL `sp1`();

To leave a Function, of course you can use RETURN.

CONDITIONs must be local

A final note about the named error conditions. They must be local: a CONDITION defined in the caller program does not exist in the callee program.

So, the following example does not work:

-- doesn't work
USE test;
DROP PROCEDURE IF EXISTS `callee`;
DROP PROCEDURE IF EXISTS `caller`;
DELIMITER ||
CREATE PROCEDURE `callee`()
BEGIN
    DECLARE `cur` CURSOR FOR
        SELECT 1 LIMIT 0;
    
    DECLARE CONTINUE HANDLER
        FOR `cursor_not_open`
    BEGIN
        SELECT 'callee handler';
    END;
    
    CLOSE `cur`;
END;
 
CREATE PROCEDURE `caller`()
BEGIN
    DECLARE CONDITION `cursor_not_open` FOR 1326;
    
    CALL `callee`();
END;
||
DELIMITER ;

Output:

ERROR 1319 (42000): Undefined CONDITION: cursor_not_open

EDIT: Suppress errors and warnings

Sometimes you don’t want to take a particular action when an error occurs – sometimes you just want to suppress the error, so that the user doesn’t see it. The typical case is when you execute CREATE TABLE IF NOT EXISTS or DROP TABLE IF EXISTS: you know that the table may (not) exist, it’s ok for you, so you don’t want to see a 1050 or 1051 wanings. The HANDLER to suppress an error is:

DECLARE CONTINUE HANDLER FOR 1051
BEGIN END;

Enjoy!

About these ads

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s