13.6.7.5 SIGNAL Statement
SIGNAL condition_value
[SET signal_information_item
[, signal_information_item] ...]
condition_value: {
SQLSTATE [VALUE] sqlstate_value
| condition_name
}
signal_information_item:
condition_information_item_name = simple_value_specification
condition_information_item_name: {
CLASS_ORIGIN
| SUBCLASS_ORIGIN
| MESSAGE_TEXT
| MYSQL_ERRNO
| CONSTRAINT_CATALOG
| CONSTRAINT_SCHEMA
| CONSTRAINT_NAME
| CATALOG_NAME
| SCHEMA_NAME
| TABLE_NAME
| COLUMN_NAME
| CURSOR_NAME
}
condition_name, simple_value_specification:
(see following discussion)
SIGNAL
is the way to “return” an error. SIGNAL
provides error information to a handler, to an outer portion of the application, or to the client. Also, it provides control over the error's characteristics (error number, SQLSTATE
value, message). Without SIGNAL
, it is necessary to resort to workarounds such as deliberately referring to a nonexistent table to cause a routine to return an error.
No privileges are required to execute the SIGNAL
statement.
To retrieve information from the diagnostics area, use the GET DIAGNOSTICS
statement (see Section 13.6.7.3, “GET DIAGNOSTICS Statement”). For information about the diagnostics area, see Section 13.6.7.7, “The MySQL Diagnostics Area”.
The condition_value
in a SIGNAL
statement indicates the error value to be returned. It can be an SQLSTATE
value (a 5-character string literal) or a condition_name
that refers to a named condition previously defined with DECLARE ... CONDITION
(see Section 13.6.7.1, “DECLARE ... CONDITION Statement”).
An SQLSTATE
value can indicate errors, warnings, or “not found.” The first two characters of the value indicate its error class, as discussed in Signal Condition Information Items. Some signal values cause statement termination; see Effect of Signals on Handlers, Cursors, and Statements.
The SQLSTATE
value for a SIGNAL
statement should not start with '00'
because such values indicate success and are not valid for signaling an error. This is true whether the SQLSTATE
value is specified directly in the SIGNAL
statement or in a named condition referred to in the statement. If the value is invalid, a Bad SQLSTATE
error occurs.
To signal a generic SQLSTATE
value, use '45000'
, which means “unhandled user-defined exception.”
The SIGNAL
statement optionally includes a SET
clause that contains multiple signal items, in a list of condition_information_item_name
= simple_value_specification
assignments, separated by commas.
Each condition_information_item_name
may be specified only once in the SET
clause. Otherwise, a Duplicate condition information item
error occurs.
Valid simple_value_specification
designators can be specified using stored procedure or function parameters, stored program local variables declared with DECLARE
, user-defined variables, system variables, or literals. A character literal may include a _charset
introducer.
For information about permissible condition_information_item_name
values, see Signal Condition Information Items.
The following procedure signals an error or warning depending on the value of pval
, its input parameter:
CREATE PROCEDURE p (pval INT)
BEGIN
DECLARE specialty CONDITION FOR SQLSTATE '45000';
IF pval = 0 THEN
SIGNAL SQLSTATE '01000';
ELSEIF pval = 1 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'An error occurred';
ELSEIF pval = 2 THEN
SIGNAL specialty
SET MESSAGE_TEXT = 'An error occurred';
ELSE
SIGNAL SQLSTATE '01000'
SET MESSAGE_TEXT = 'A warning occurred', MYSQL_ERRNO = 1000;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'An error occurred', MYSQL_ERRNO = 1001;
END IF;
END;
If pval
is 0, p()
signals a warning because SQLSTATE
values that begin with '01'
are signals in the warning class. The warning does not terminate the procedure, and can be seen with SHOW WARNINGS
after the procedure returns.
If pval
is 1, p()
signals an error and sets the MESSAGE_TEXT
condition information item. The error terminates the procedure, and the text is returned with the error information.
If pval
is 2, the same error is signaled, although the SQLSTATE
value is specified using a named condition in this case.
If pval
is anything else, p()
first signals a warning and sets the message text and error number condition information items. This warning does not terminate the procedure, so execution continues and p()
then signals an error. The error does terminate the procedure. The message text and error number set by the warning are replaced by the values set by the error, which are returned with the error information.
SIGNAL
is typically used within stored programs, but it is a MySQL extension that it is permitted outside handler context. For example, if you invoke the mysql client program, you can enter any of these statements at the prompt:
SIGNAL SQLSTATE '77777';
CREATE TRIGGER t_bi BEFORE INSERT ON t
FOR EACH ROW SIGNAL SQLSTATE '77777';
CREATE EVENT e ON SCHEDULE EVERY 1 SECOND
DO SIGNAL SQLSTATE '77777';
SIGNAL
executes according to the following rules:
If the SIGNAL
statement indicates a particular SQLSTATE
value, that value is used to signal the condition specified. Example:
CREATE PROCEDURE p (divisor INT)
BEGIN
IF divisor = 0 THEN
SIGNAL SQLSTATE '22012';
END IF;
END;
If the SIGNAL
statement uses a named condition, the condition must be declared in some scope that applies to the SIGNAL
statement, and must be defined using an SQLSTATE
value, not a MySQL error number. Example:
CREATE PROCEDURE p (divisor INT)
BEGIN
DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
IF divisor = 0 THEN
SIGNAL divide_by_zero;
END IF;
END;
If the named condition does not exist in the scope of the SIGNAL
statement, an Undefined CONDITION
error occurs.
If SIGNAL
refers to a named condition that is defined with a MySQL error number rather than an SQLSTATE
value, a SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE
error occurs. The following statements cause that error because the named condition is associated with a MySQL error number:
DECLARE no_such_table CONDITION FOR 1051;
SIGNAL no_such_table;
If a condition with a given name is declared multiple times in different scopes, the declaration with the most local scope applies. Consider the following procedure:
CREATE PROCEDURE p (divisor INT)
BEGIN
DECLARE my_error CONDITION FOR SQLSTATE '45000';
IF divisor = 0 THEN
BEGIN
DECLARE my_error CONDITION FOR SQLSTATE '22012';
SIGNAL my_error;
END;
END IF;
SIGNAL my_error;
END;
If divisor
is 0, the first SIGNAL
statement executes. The innermost my_error
condition declaration applies, raising SQLSTATE
'22012'
.
If divisor
is not 0, the second SIGNAL
statement executes. The outermost my_error
condition declaration applies, raising SQLSTATE
'45000'
.
For information about how the server chooses handlers when a condition occurs, see Section 13.6.7.6, “Scope Rules for Handlers”.
Signals can be raised within exception handlers:
CREATE PROCEDURE p ()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SIGNAL SQLSTATE VALUE '99999'
SET MESSAGE_TEXT = 'An error occurred';
END;
DROP TABLE no_such_table;
END;
CALL p()
reaches the DROP TABLE
statement. There is no table named no_such_table
, so the error handler is activated. The error handler destroys the original error (“no such table”) and makes a new error with SQLSTATE
'99999'
and message An error occurred
.
The following table lists the names of diagnostics area condition information items that can be set in a SIGNAL
(or RESIGNAL
) statement. All items are standard SQL except MYSQL_ERRNO
, which is a MySQL extension. For more information about these items see Section 13.6.7.7, “The MySQL Diagnostics Area”.
Item Name Definition
--------- ----------
CLASS_ORIGIN VARCHAR(64)
SUBCLASS_ORIGIN VARCHAR(64)
CONSTRAINT_CATALOG VARCHAR(64)
CONSTRAINT_SCHEMA VARCHAR(64)
CONSTRAINT_NAME VARCHAR(64)
CATALOG_NAME VARCHAR(64)
SCHEMA_NAME VARCHAR(64)
TABLE_NAME VARCHAR(64)
COLUMN_NAME VARCHAR(64)
CURSOR_NAME VARCHAR(64)
MESSAGE_TEXT VARCHAR(128)
MYSQL_ERRNO SMALLINT UNSIGNED
The character set for character items is UTF-8.
It is illegal to assign NULL
to a condition information item in a SIGNAL
statement.
A SIGNAL
statement always specifies an SQLSTATE
value, either directly, or indirectly by referring to a named condition defined with an SQLSTATE
value. The first two characters of an SQLSTATE
value are its class, and the class determines the default value for the condition information items:
Class =
'00'
(success)Illegal.
SQLSTATE
values that begin with'00'
indicate success and are not valid forSIGNAL
.Class =
'01'
(warning)MESSAGE_TEXT = 'Unhandled user-defined warning condition'; MYSQL_ERRNO = ER_SIGNAL_WARN
Class =
'02'
(not found)MESSAGE_TEXT = 'Unhandled user-defined not found condition'; MYSQL_ERRNO = ER_SIGNAL_NOT_FOUND
Class >
'02'
(exception)MESSAGE_TEXT = 'Unhandled user-defined exception condition'; MYSQL_ERRNO = ER_SIGNAL_EXCEPTION
For legal classes, the other condition information items are set as follows:
CLASS_ORIGIN = SUBCLASS_ORIGIN = '';
CONSTRAINT_CATALOG = CONSTRAINT_SCHEMA = CONSTRAINT_NAME = '';
CATALOG_NAME = SCHEMA_NAME = TABLE_NAME = COLUMN_NAME = '';
CURSOR_NAME = '';
The error values that are accessible after SIGNAL
executes are the SQLSTATE
value raised by the SIGNAL
statement and the MESSAGE_TEXT
and MYSQL_ERRNO
items. These values are available from the C API:
mysql_sqlstate()
returns theSQLSTATE
value.mysql_errno()
returns theMYSQL_ERRNO
value.mysql_error()
returns theMESSAGE_TEXT
value.
At the SQL level, the output from SHOW WARNINGS
and SHOW ERRORS
indicates the MYSQL_ERRNO
and MESSAGE_TEXT
values in the Code
and Message
columns.
To retrieve information from the diagnostics area, use the GET DIAGNOSTICS
statement (see Section 13.6.7.3, “GET DIAGNOSTICS Statement”). For information about the diagnostics area, see Section 13.6.7.7, “The MySQL Diagnostics Area”.
Signals have different effects on statement execution depending on the signal class. The class determines how severe an error is. MySQL ignores the value of the sql_mode
system variable; in particular, strict SQL mode does not matter. MySQL also ignores IGNORE
: The intent of SIGNAL
is to raise a user-generated error explicitly, so a signal is never ignored.
In the following descriptions, “unhandled” means that no handler for the signaled SQLSTATE
value has been defined with DECLARE ... HANDLER
.
Class =
'00'
(success)Illegal.
SQLSTATE
values that begin with'00'
indicate success and are not valid forSIGNAL
.Class =
'01'
(warning)The value of the
warning_count
system variable goes up.SHOW WARNINGS
shows the signal.SQLWARNING
handlers catch the signal.Warnings cannot be returned from stored functions because the
RETURN
statement that causes the function to return clears the diagnostic area. The statement thus clears any warnings that may have been present there (and resetswarning_count
to 0).Class =
'02'
(not found)NOT FOUND
handlers catch the signal. There is no effect on cursors. If the signal is unhandled in a stored function, statements end.Class >
'02'
(exception)SQLEXCEPTION
handlers catch the signal. If the signal is unhandled in a stored function, statements end.Class =
'40'
Treated as an ordinary exception.