Exceptions and Exception Handling
This topic describes exceptions and exception handling in MemSQL Procedural SQL (MPSQL) user-defined scalar-valued functions (UDFs) and stored procedures (SPs).
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 MemSQL
- 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
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 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.
OTHERS can also be used as an
OTHERS will match all exceptions.
A list of one or more statements terminated by semicolons.
Internal exceptions are defined by MemSQL, 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.
Consider the following example, where table
t contains two entries
id is defined as a
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
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 ;
insert_handle_exception() is called, the error-handling behavior
successful inserts the contents of the row, and the current time,
t_errors table instead:
memsql> CALL insert_handle_exception(1, "baz"); Query OK, 0 rows affected (0.26 sec)
memsql> SELECT * FROM t_errors; +---------------------+------+------+ | dt | id | str | +---------------------+------+------+ | 2017-09-26 22:33:22 | 1 | baz | +---------------------+------+------+
memsql> SELECT * FROM t; +----+------+ | id | str | +----+------+ | 0 | foo | | 1 | bar | +----+------+
The use of
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.
Users can generate their own exceptions through the
USER_EXCEPTION() is a function that
takes a string argument. You can pass any string expression as
USER_EXCEPTION argument. This
statement will raise an
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
can be used to get the message that was passed into
for the currently caught exception. Here’s an example that shows
a possible use of
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
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.
memsql> CALL attemptWithRetry(); ERROR 2242 (HY000): Unhandled exception Type: ER_USER_RAISE Message: abort Callstack: #0 Line 7 in `trades`.`attemptWithRetry` memsql> CALL attemptWithRetry(); ERROR 2242 (HY000): Unhandled exception Type: ER_USER_RAISE Message: other exception Callstack: #0 Line 7 in `trades`.`attemptWithRetry`