Exceptions and Exception Handling

This topic describes exceptions and exception handling in SingleStore Procedural SQL (PSQL) user-defined scalar-valued functions (UDFs) and stored procedures (SPs).

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 will always appear in the call stack displayed for an uncaught exception.

The keyword OTHERS can also be used as an exception_type. OTHERS will match 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 successful 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() statement. USER_EXCEPTION() is a function that takes a string argument. You can pass any string expression as the USER_EXCEPTION argument. This statement will raise an ER_USER_RAISE exception.

The RAISE keyword by itself can only be used inside of an EXCEPTION block, and it re-raises the currently caught exception.

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

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

    EXCEPTION
      WHEN ER_USER_RAISE THEN
        ROLLBACK;
        IF exception_message() = "retry" THEN
          i = sleep(1);
          CONTINUE;
        ELSIF exception_message() = "abort" THEN
          RAISE;
        END IF;
      WHEN OTHERS THEN
        ROLLBACK;
        RAISE user_exception("other exception");
        -- i = sleep(1);
    END;

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

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("abort");
  ELSIF i <= 3  THEN
    j = NULL; -- force an exception by assigning NULL to a NOT NULL type
  END IF;
  RAISE user_exception("retry");
END //
DELIMITER ;

This example 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, and it will always fail because j is declared as NOT NULL. That will generate a system exception, which will be caught at WHEN OTHERS.

Now, when we call attemptWithRetry(), you can see that either the abort user exception or the other exception user exception was raised. This may take anywhere from 0 to a few seconds depending on what time it is when the procedure is first called.

CALL attemptWithRetry();

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

CALL attemptWithRetry();

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