SIGNAL

Syntax

SIGNAL error_condition [SET error_property [, error_property] ...]

error_condition: SQLSTATE [VALUE] 'sqlstate_value'

error_property: error_property_name = <error_property_value>

error_property_name: CLASS_ORIGIN

Description

SIGNAL produces a custom error. This statement can be used anywhere, but is generally useful when used inside a Stored Program. When the error is produced, it can be catched by a HANDLER. If not, the current Stored Program, or the current statement, will terminate with the error.

Sometimes an error HANDLER just needs to SIGNAL the same error it received, optionally with some changes. Usually the RESIGNAL statement is the most convenient way to do this.

SIGNAL and RESIGNAL are both introduced in MariaDB 5.5.

error_condition can be an SQLSTATE value or a named error condition defined via DECLARE CONDITION. SQLSTATE is a string consisting of five characters. These codes are standard to ODBC and ANSI SQL. For customized errors, the recommended SQLSTATE is '45000'. For a list of SQLSTATE values used by MariaDB, see the MariaDB Error Codes page. The SQLSTATE can be read via the API method mysql_sqlstate().

While several error properties can be specified, only the following are set and can be read via the API:

• MYSQL_ERRNO is an error number between 1 and 65534. It can be read via mysql_errno().
• MESSAGE_TEXT is an error message with a maximum length of 128 characters. It can be read via mysql_error().

Variables can be used to specify these information, as well as character set conversions.

If not specified, MYSQL_ERRNO and MESSAGE_TEXT have default values which depend on the first two SQLSTATE characters:

• '00' means 'success' and can not be used with SIGNAL.
• For '01' class, default MYSQL_ERRNO is 1642 and default MESSAGE_TEXT is 'Unhandled user-defined warning condition'.
• For '02' class, default MYSQL_ERRNO is 1643 and default MESSAGE_TEXT is 'Unhandled user-defined not found condition'.
• For all other cases, including the '45000' value, default MYSQL_ERRNO is 1644 and default MESSAGE_TEXT is 'Unhandled user-defined exception condition'.

Examples

Here's what happens if SIGNAL is used in the client to generate errors:

MariaDB [(none)]> SIGNAL SQLSTATE '01000';
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1642 | Unhandled user-defined warning condition |
+---------+------+------------------------------------------+
1 row in set (0.06 sec)

MariaDB [(none)]> SIGNAL SQLSTATE '02000';
ERROR 1643 (02000): Unhandled user-defined not found condition

How to specify MYSQL_ERRNO and MESSAGE_TEXT properties:

MariaDB [(none)]> SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT='H
ello, world!';
ERROR 30001 (45000): Hello, world!

The following code shows how to use user variables, local variables and character set conversion with SIGNAL:

CREATE PROCEDURE test_error(x INT)
BEGIN
	DECLARE errno SMALLINT UNSIGNED DEFAULT 31001;
	SET @errmsg = 'Hello, world!';
	IF x = 1 THEN
		SIGNAL SQLSTATE '45000' SET
			MYSQL_ERRNO = errno,
			MESSAGE_TEXT = @errmsg;
	ELSE
		SIGNAL SQLSTATE '45000' SET
			MYSQL_ERRNO = errno,
			MESSAGE_TEXT = _utf8'Hello, world!';
	END IF;
END;

How to use named error conditions:

CREATE PROCEDURE test_error(n INT)
BEGIN
	DECLARE `too_big` CONDITION FOR SQLSTATE '45000';
	IF n > 10 THEN
		SIGNAL `too_big`;
	END IF;
END;

In this example, we'll define a HANDLER for an error code. When the error occurs, we SIGNAL a more informative error which makes sense for our procedure:

CREATE PROCEDURE test_error()
BEGIN
	DECLARE EXIT HANDLER
		FOR 1146
	BEGIN
		SIGNAL SQLSTATE '45000' SET
			MESSAGE_TEXT = 'Temporary tables not found; did you call init() procedure?';
	END;
	-- this will produce a 1146 error
	SELECT `c` FROM `temptab`;
END;