SIGNAL

The SIGNAL statement is a way to "return" an error. SIGNAL provides error information to the handler, the application, or the client. It also provides control over the characteristics of the error (error code, SQLSTATE value, and message).

Syntax and parameter description

If SIGNAL is not used, you must use another method to return an error, such as intentionally referencing a nonexistent table to cause the routine to return an error.

No privileges are required to execute SIGNAL statements.

The syntax for 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
  
}

In the SIGNAL statement, condition_value specifies the error value to return. It can be an SQLSTATE value (a 5-character string literal) or a condition_name, which refers to a named condition previously defined using DECLARE ... CONDITION (see DECLARE ... CONDITION).

The SQLSTATE value can indicate an error, a warning, or a "not found" condition. The first two characters of the value indicate its error category. Some signal values cause the statement to terminate; see Impact of SIGNAL on Handlers, Cursors, and Statements.

The SQLSTATE value in a SIGNAL statement should not start with '00', because such values indicate success and are invalid for signaling an error, whether specified directly in the SIGNAL statement or referenced in a named condition within the statement. If an invalid value is used, a Bad SQLSTATE error occurs.

To signal a generic SQLSTATE value, use "45000", which means "unhandled user-defined exception".

The SIGNAL statement can include an optional SET clause, which can contain multiple signal items and is specified in a list of condition_information_item_name = simple_value_specification items separated by commas.

Each condition_information_item_name can be specified only once in the SET clause. Otherwise, an error Duplicate condition information item occurs.

You can use stored procedure or function parameters, stored program local variables declared with DECLARE, user-defined variables, system variables, or literals to specify valid simple_value_specification indicators.

For information about condition_information_item_name values, see Signal condition information items.

In the following example, the stored procedure proc signals an error or warning based on the value of pval (its input parameter).

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() signals a warning because an SQLSTATE value starting with '01' indicates a warning category. The warning does not terminate the procedure and can be viewed after the procedure returns using SHOW WARNINGS.

If pval is 1, proc() signals an error and sets the MESSAGE_TEXT condition information item. The error terminates the procedure and returns the text with the error message.

If pval is 2, the same error signal is issued, although in this case the SQLSTATE value is specified using a named condition.

If pval is any other value, proc() 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 then proc() signals an error. The error terminates the procedure. The message text and error number set by the warning are replaced by the error values, which are returned along with the error message.

SIGNAL is typically used in stored programs, but as an extended feature, it can also be used outside of handler contexts. For example, when calling 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 for SIGNAL

The execution rules for SIGNAL are as follows:

If the SIGNAL statement specifies a particular SQLSTATE value, that value is used to indicate the specified condition. Example:
```
CREATE PROCEDURE proc(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 a scope applicable to the SIGNAL statement, and the condition must be defined using an SQLSTATE value instead of an error code. If the specified condition does not exist in the scope of the SIGNAL statement, an error "Undefined CONDITION" occurs. 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 the SIGNAL statement references a named condition defined using an error code instead of an SQLSTATE value, an error "SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE" occurs. 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 condition with the specified name is declared multiple times in different scopes, the declaration with the local scope is applied.

In the following example, if divisor is 0, the first SIGNAL statement is executed. The innermost one_error condition declaration applies and triggers SQLSTATE '22012'. If divisor is not 0, the second SIGNAL statement is executed. The outermost one_error condition declaration applies and triggers SQLSTATE '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 exception handlers. In the following example, CALL proc() executes the DROP TABLE statement. Since there is no table named no_table_found, the error handler is activated. The error handler overrides the original error ("no such table") and displays an error using SQLSTATE '99999' and the message "An 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 information items

For standard SQL, the following table lists the names of diagnostic area condition information items that can be set in the SIGNAL (or RESIGNAL) statement, where MYSQL_ERRNO is an extended item.

Item Name	Data Type
MESSAGE_TEXT	VARCHAR(64)
MYSQL_ERRNO	SMALLINT UNSIGNED

The character set for character items is UTF-8. Condition information items cannot assign NULL to the SIGNAL statement.

The SIGNAL statement always specifies the SQLSTATE value by directly or indirectly referencing the condition defined by the SQLSTATE value. The first two characters of the SQLSTATE value are its category, which determines the default value of the condition information item, as shown in the following table.

SQLSTATE Value Category	Description
Class = `'00'` (success)	Invalid. `SQLSTATE` values starting with '00' indicate success and are invalid for `SIGNAL`.
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 SIGNAL is the SQLSTATE value caused by the SIGNAL statement and the MESSAGE_TEXT and MYSQL_ERRNO items. These values can be obtained from the following C API:

mysql_sqlstate() returns the SQLSTATE value.
mysql_errno() returns the MYSQL_ERRNO value.
mysql_error() returns the MESSAGE_TEXT value.

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 signals 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, so signals are never ignored. The value of the sql_mode system variable is ignored, especially in strict mode.

SQLSTATE Value Category	Description
Class = `'00'` (success)	Invalid. `SQLSTATE` values starting with '00' indicate success and are invalid for `SIGNAL`.
Class = `'01'` (warning)	`SHOW WARNINGS` displays the signal. `SQLWARNING` handlers capture the signal. However, since the `RETURN` statement that causes the function to return clears the diagnostic area, alerts cannot be returned from stored functions. This statement clears any existing alerts (and resets `warning_count` to 0).
Class = `'02'` (not found)	`NOT FOUND` handlers capture the signal. It has no effect on cursors. If the signal is not handled in a stored function (i.e., no handler is defined using `DECLARE ... HANDLER` for the issued `SQLSTATE` value), the statement ends.
Class > `'02'` (exception)	`SQLEXCEPTION` handlers capture the signal. If the signal is not handled in a stored function (i.e., no handler is defined using `DECLARE ... HANDLER` for the issued `SQLSTATE` value), the statement ends.
Class = `'40'`	Treated as a general exception.

Syntax and parameter description​

Execution rules for SIGNAL​

Signal condition information items​

Impact of signals on handlers, cursors, and statements​

Contents

Syntax and parameter description

Execution rules for SIGNAL

Signal condition information items

Impact of signals on handlers, cursors, and statements