Exception Types

UDFs and SPs raise exceptions when errors occur during execution. Unhandled exceptions generate an exception message and a stack trace that is returned to the client.

Two kinds of exceptions can be raised:

• An internal exception, which is a predefined exception generated by SingleStore

• A user-defined exception, which is defined by the user in a UDF or SP

The following sections describe each kind of exception and how to handle exceptions in your UDF or SP.

Exception Handling Syntax

If you want to catch an exception, you can use a Statement Block containing an exception handling section beginning with the EXCEPTION keyword. Here is the syntax for statement blocks that can use EXCEPTION:

statement_block:
  [ DECLARE variable_list ] [ ...n ]
  BEGIN
    statement_list
  [ EXCEPTION
    when_clause_list ]
  END;

when_clause_list:
  when_clause [ ...n ]

when_clause:
  WHEN exception_type THEN statement_list

exception_type

An exception_type is the name of a built-in server error type. There is currently no published list of all the different error names, but the error name always appears in the call stack displayed for an uncaught exception.

The keyword OTHERS can also be used as an exception_type. OTHERS matches all exceptions.

statement_list

A list of one or more statements terminated by semicolons.

Internal Exceptions

Internal exceptions are defined by SingleStore, and they are raised when a function or procedure errors under a wide variety of conditions. These exception types encompass SQL syntax errors, insertion errors, connection errors, and so on.

Here are some possible internal exceptions:

Consider the following example, where table t contains two entries and id is defined as a PRIMARY KEY:

CREATE TABLE t(id INT PRIMARY KEY, str VARCHAR(255));
CREATE TABLE t_errors(dt DATETIME, id INT, str VARCHAR(255));

INSERT t VALUES (0, "foo"), (1, "bar");

The following procedure attempts to insert a row with a duplicate primary key, which raises the ER_DUP_ENTRY exception. This exception triggers the procedure’s exception-handling behavior, where the row with a duplicate primary key is written to a t_errors table instead:

DELIMITER //
CREATE OR REPLACE PROCEDURE insert_handle_exception(
  id INT, str VARCHAR(255)) AS
  BEGIN
    START TRANSACTION;
    INSERT INTO t VALUES (1, str);
    COMMIT;

    EXCEPTION
      WHEN ER_DUP_ENTRY THEN
        ROLLBACK;
        START TRANSACTION;
        INSERT INTO t_errors VALUES (current_timestamp(), id, str);
        COMMIT;
  END //
DELIMITER ;

When insert_handle_exception() is called, the error-handling behavior successfully inserts the contents of the row, and the current time, into the t_errors table instead:

CALL insert_handle_exception(1, "baz");

Query OK, 0 rows affected (0.26 sec)

SELECT * FROM t_errors;

+---------------------+------+------+
| dt                  | id   | str  |
+---------------------+------+------+
| 2017-09-26 22:33:22 |    1 | baz  |
+---------------------+------+------+

SELECT * FROM t;

+----+------+
| id | str  |
+----+------+
|  0 | foo  |
|  1 | bar  |
+----+------+

The use of START TRANSACTION, COMMIT, and ROLLBACK above is not really necessary in this simple example, but it does illustrate how you can use transaction control statements in conjunction with exception handling. For example, it may be necessary to roll back a transaction when you catch an exception, to make sure partial work is not committed.

User-Generated Exceptions

Users can generate their own exceptions through the RAISE USER_EXCEPTION(<str>) statement. USER_EXCEPTION() is a function that takes a string as an argument. The RAISE statement raises an ER_USER_RAISE exception.

The RAISE keyword by itself can be used inside an EXCEPTION block to re-raise the currently caught exception.

When in an EXCEPTION block, the EXCEPTION_MESSAGE() function can be used to obtain the message that was passed into USER_EXCEPTION for the currently caught exception. An example that shows a possible use of EXCEPTION_MESSAGE() follows.

In the following example, the attempt() procedure raises three possible exceptions:

• A user exception with message "rollback" (when i<=7).

• A user exception with message "retry" (when i<=3).

• A bad null typecast (when 3 < i < 7).

The attemptWithRetry() procedure calls attempt() and does the following:

• On receiving a user exception with a "retry" error message, retries the call to attempt().

• On receiving a user exception with a "rollback" error message, re-raises the exception.

• On receiving a different type of error (the null typecast in this example), raises a user exception with the message "other exception".

DELIMITER //
CREATE OR REPLACE PROCEDURE attempt() AS
DECLARE i INT; j INT NOT NULL = 0;
BEGIN
  i = now() % 10;
  IF i >= 7 THEN
    RAISE user_exception("rollback"); -- line 6
  ELSIF i <= 3  THEN -- line 7
    j = NULL; -- line 8 force an exception by assigning NULL to a NOT NULL type
  END IF;
  RAISE user_exception("retry"); -- line 10
END //
DELIMITER ;

DELIMITER //
CREATE OR REPLACE PROCEDURE attemptWithRetry() AS
DECLARE i INT;
BEGIN
  WHILE true LOOP
    BEGIN
      START TRANSACTION;
      CALL attempt(); -- line 7

    EXCEPTION
      WHEN ER_USER_RAISE THEN -- line 10
        ROLLBACK; -- line 11
        IF REGEXP_INSTR(EXCEPTION_MESSAGE(), "retry") THEN
          i = sleep(1);
          CONTINUE;
        ELSIF REGEXP_INSTR(EXCEPTION_MESSAGE(), "rollback") THEN
          RAISE;
        END IF;
      WHEN OTHERS THEN
        ROLLBACK; -- line 19
        RAISE user_exception("other exception"); -- line 20
    END;

    COMMIT;
    EXIT;
  END LOOP;
END //
DELIMITER ;

The attemptWithRetry() procedure illustrates the use of OTHERS to catch exceptions not explicitly named in a WHEN clause. When i <= 3 (about 30% of the time) in attempt(), the assignment j = NULL is tried; this assignment always fails because j is declared as NOT NULL. This failure generates a system exception, which is caught with WHEN OTHERS.

When attemptWithRetry() is called, the expected results are:

• A user exception with the message "rollback".

• A user exception with the message "other exception".

• Either of these exceptions may display a call stack showing multiple retries.

Call attemptWithRetry() multiple times to see the different results.

In the following call of attemptWithRetry(), attempt() tries the invalid NULL assignment and a system failure is generated which is caught with OTHERS. A user exception with the message "other exception" is raised and returned to the client.

CALL attemptWithRetry();

ERROR 2242 (HY000): Unhandled exception
Type: ER_USER_RAISE
Message: other exception
Callstack:
  #0 Line 7 in `db`.`attemptWithRetry`