The SIGNAL statement is a way to "return" an error. It provides error information to handlers, external applications, or clients. Furthermore, it offers control over error characteristics (error code, SQLSTATE value, message).
Syntax and parameters
If SIGNAL is not used, other methods must be employed to return an error, such as deliberately referencing a non-existent table to cause the routine to return an error.
Executing the SIGNAL statement does not require any privileges.
The syntax of the SIGNAL statement is as follows:
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: {
MESSAGE_TEXT
| MYSQL_ERRNO
}
The condition_value in the SIGNAL statement indicates the error value to be returned. It can be an SQLSTATE value (a five-character string literal) or a condition_name that references a named condition previously defined using DECLARE ... CONDITION (see DECLARE ... CONDITION.
SQLSTATE values can indicate errors, warnings, or "not found". The first two characters of the value represent its error category. Some signal values cause statements to terminate; see Impact of signals on handlers, cursors, and statements.
The SQLSTATE value of a SIGNAL statement must not start with '00', as such values indicate success and are invalid for raising an error signal. This applies whether the SQLSTATE value is specified directly in the SIGNAL statement or in a named condition referenced by the statement. If the value is invalid, a Bad SQLSTATE error occurs.
To signal a general SQLSTATE value, use "45000", which means "unhandled user-defined exception".
The SIGNAL statement can contain an optional SET clause, which consists of multiple signal items separated by commas and listed in the condition_information_item_name = simple_value_specification format.
Each condition_information_item_name can only be specified once in the SET clause. Otherwise, the error Duplicate condition information item will occur.
You can use stored procedure or function parameters, local program variables declared with DECLARE, user-defined variables, system variables, or literals to specify valid simple_value_specification indicators.
For information about the condition_information_item_name value, see Signal condition items.
In the following example, the stored procedure proc generates an error or alert signal based on the value of its input parameter pval.
CREATE PROCEDURE proc(pval INT)
BEGIN
DECLARE psign 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 psign
SET MESSAGE_TEXT = 'An error occurred';
ELSE
SIGNAL SQLSTATE '01000'
SET MESSAGE_TEXT = 'A warning occurred', MYSQL_ERRNO = 1001;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'An error occurred', MYSQL_ERRNO = 1002;
END IF;
END;
If pval is 0, proc() issues a warning signal because SQLSTATE values starting with '01' indicate warning categories. This warning does not terminate the procedure, and it can be viewed after the procedure returns using the SHOW WARNINGS statement.
If pval is 1, proc() raises an error signal and sets the MESSAGE_TEXT condition item. The error terminates the procedure and returns a text containing the error message.
If pval is 2, the same error signal is raised, even though in this case the SQLSTATE value is specified using named conditions.
If pval is anything other than this, proc() first issues a warning signal and sets the message text and error number condition items. This alert does not terminate the procedure, so execution continues, after which proc() issues an error signal. This error terminates the procedure. The message text and error number set by the alert are replaced with the misassigned values, which are returned along with the error message.
SIGNAL is typically used within stored programs, but as an extension, it allows you to use it outside the context of a handler. For example, when you call the mysql client program, you can enter the following statement at the prompt:
SIGNAL SQLSTATE '66666';
CREATE TRIGGER trg BEFORE INSERT ON tbl
FOR EACH ROW SIGNAL SQLSTATE '66666';
Execution rules of SIGNAL
The execution rules for SIGNAL are as follows:
If the
SIGNALstatement specifies a particularSQLSTATEvalue, that value indicates the specified condition. Example:CREATE PROCEDURE proc(divisor INT) BEGIN IF divisor = 0 THEN SIGNAL SQLSTATE '22012'; END IF; END;If a named condition is used in a
SIGNALstatement, it must be declared within the scope applicable to thatSIGNALstatement, and must be defined using anSQLSTATEvalue rather than an error code. If the specified condition does not exist within the scope of theSIGNALstatement, an "Undefined CONDITION" error is raised. Example:CREATE PROCEDURE proc(divisor INT) BEGIN DECLARE div_by_zero CONDITION FOR SQLSTATE '22012'; IF divisor = 0 THEN SIGNAL div_by_zero; END IF; END;If
SIGNALrefers to a named condition defined using an error code rather than anSQLSTATEvalue, the error "SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE" is reported. The following statement causes this error because the named condition is associated with an error code:DECLARE no_table_found CONDITION FOR 1051; SIGNAL no_table_found;If a conditional statement with a specified name is declared multiple times in different scopes, the application uses the declaration from the local scope.
In the following example, if
divisoris 0, the firstSIGNALstatement is executed. The innermostone_errorcondition is applicable and triggersSQLSTATE '22012'. Ifdivisoris not 0, the secondSIGNALstatement is executed. The outermostone_errorcondition is applicable and triggersSQLSTATE '45000'.CREATE PROCEDURE proc(divisor INT) BEGIN DECLARE one_error CONDITION FOR SQLSTATE '45000'; IF divisor = 0 THEN BEGIN DECLARE one_error CONDITION FOR SQLSTATE '22012'; SIGNAL one_error; END; END IF; SIGNAL one_error; END;Signals can be raised in an exception handler. In the following example,
CALL proc()fetches theDROP TABLEstatement. Since no table namedno_table_foundexists, the error handler is activated. The error handler destroys the original error ("no such table") and displays an error usingSQLSTATE '99999'andAn error occurred.CREATE PROCEDURE proc() BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT = 'An error occurred'; END; DROP TABLE no_table_found; END;
Signal condition items
For standard SQL, the following table lists the names of diagnostic area condition items that can be set in a SIGNAL (or RESIGNAL) statement. Note that MYSQL_ERRNO is an extended item.
Information Item |
Data type |
|---|---|
| MESSAGE_TEXT | VARCHAR(64) |
| MYSQL_ERRNO | SMALLINT UNSIGNED |
The character set for character items is UTF-8. The SIGNAL statement cannot be assigned a NULL value for the condition information item.
The SIGNAL statement always specifies the SQLSTATE value directly or indirectly by referencing a condition defined by an SQLSTATE value. The first two characters of an SQLSTATE value are its category, which determines the default value for the condition information items, as shown in the following table.
Categories of SQLSTATE Values |
Description |
|---|---|
Class = '00' (success) |
Invalid. It must not start with '00'. SQLSTATE A value of indicates success and the operation is correct. SIGNAL Invalid. |
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 |
The error value obtained after executing the SIGNAL statement is the SQLSTATE value triggered by the SIGNAL statement and the MESSAGE_TEXT and MYSQL_ERRNO items. These values can be obtained using the following C API:
mysql_sqlstate()returns anSQLSTATEvalue.mysql_errno()returns aMYSQL_ERRNOvalue.mysql_error()returns theMESSAGE_TEXTvalue.
At the SQL level, the Code and Message columns output by SHOW WARNINGS and SHOW ERRORS indicate the values of MYSQL_ERRNO and MESSAGE_TEXT.
To retrieve information from the diagnostic area, use the GET DIAGNOSTICS statement. For more information, see GET DIAGNOSTICS.
Impact of signal pairs on handlers, cursors, and statements
The category of a signal has different impacts on statement execution, as shown in the following table. The category determines the severity of the error. The purpose of SIGNAL is to explicitly raise user-generated errors; therefore, signals are never ignored. The value of the sql_mode system variable is ignored, especially in strict mode.
Categories of SQLSTATE Values |
Description |
|---|---|
Class = '00' (success) |
Invalid. It must not start with '00'. SQLSTATE A value of indicates success and the operation is correct. SIGNAL Invalid. |
Class = '01' (warning) |
SHOW WARNINGS Display signals. SQLWARNING The handler catches the signal. Because the function returned due to the RETURN This statement clears the diagnostic area, so alerts cannot be returned from stored functions. It clears any existing alerts (and sets the alert count to zero). warning_count(Reset to 0). |
Class = '02' (not found) |
NOT FOUND The handler catches the signal. The cursor is not affected. If the signal is not handled in the stored function (that is, the SIGINT keyword is not used), DECLARE ... HANDLER Defined Issued SQLSTATE handler), the statement ends. |
Class > '02' (exception) |
SQLEXCEPTION The handler catches the signal. If the signal is not handled in the stored function (that is, no DECLARE ... HANDLER Defined Issued SQLSTATE handler), the statement ends. |
Class = '40' |
A regular exception. |