SIGNAL

The SIGNAL statement returns errors. The SIGNAL statement provides error information to a handler, an external party of the application, or a client. In addition, it provides control over error characteristics, including the error codes, SQLSTATE values, and messages.

Syntax and parameters

If you do not use a SIGNAL statement, use other methods to return errors. For example, you can deliberately reference a table that does not exist to cause the routine to return an error.

You do not need any privilege to execute SIGNAL statements.

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 clause in the SIGNAL statement indicates the error value to be returned. It can be an SQLSTATE value (a 5-character string literal) or a condition_name field that references a naming condition previously defined in DECLARE ... CONDITION. For more information, see DECLARE … CONDITION.

An SQLSTATE value can indicate an error, a warning, or “not found”. The first two characters of this value indicate the error class. Some signal values cause statements to terminate. For more information, see Effects of signals on handlers, cursors, and statements.

Do not use a SQLSTATE value that starts with “00” in the SIGNAL statement, because such a value indicates success and is invalid for sending an error signal. This rule applies to a SQLSTATE value no matter whether it is specified directly in the SIGNAL statement or specified in the naming condition referenced by the statement. If the value is invalid, a Bad SQLSTATE error occurs.

To signal a generic SQLSTATE value, use ‘45000’, which indicates an “unhandled user-defined exception”.

The SIGNAL statement can contain a SET clause, which is optional. This clause can contain multiple signal items separated with commas and located in a list of condition_information_item_name = simple_value_specification assignments.

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

You can use stored procedures or function parameters, stored program local variables declared by using the DECLARE statement, user-defined variables, system variables, or literals to specify valid simple_value_specification indicators.

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

In the following example, the stored procedure proc() signals an error or a warning based on the value of the 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 the value of pval is 0, proc() signals a warning, because the value of SQLSTATE starts with ‘01’. This warning does not terminate the procedure. After the procedure returns the result, you can run SHOW WARNINGS to check it.

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

If the value of pval is 2, proc() signals the same error, although in this case the SQLSTATE value is specified by using the named condition.

If pval is of any other value, proc() first signals a warning and then sets the message text and error code condition information items. This warning does not terminate the procedure. Therefore, proc() continues with the execution, and then signals an error. This error terminates the procedure. The message text and error code set by the warning are replaced by the values set by the error, which are returned along with the error message.

SIGNAL is usually used in stored programs. However, you can also use it as an extension beyond the handler context. For example, when you call the mysql client program, you can enter any of the following statements at the prompt:

SIGNAL SQLSTATE '66666';

CREATE TRIGGER trg BEFORE INSERT ON tbl
  FOR EACH ROW SIGNAL SQLSTATE '66666';

Execution rules of SIGNAL

SIGNAL is executed as per the following rules:

• If the SIGNAL statement indicates a specific SQLSTATE value, the value is used to indicate the specified condition. Here is an 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 within a scope applicable to the SIGNAL statement, and the condition must be defined by using a SQLSTATE value rather than an error code. If the specified condition does not fall within the scope of the SIGNAL statement, the “Undefined CONDITION” error is returned. Here is an 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 by using an error code rather than a SQLSTATE value, the error “SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE” is returned. The following statement returns such an error, because the naming condition is associated with the error code:

  DECLARE no_table_found CONDITION FOR 1051;
  SIGNAL no_table_found;
  

• If the condition with the specified name is declared multiple times in different scopes, the declaration within the local scope is applied.

  In the following example, if divisor is 0, the first SIGNAL statement is executed. The declaration of the innermost one_error condition applies and triggers SQLSTATE '22012'. If divisor is not 0, the second SIGNAL statement is executed. The declaration of the outermost one_error condition 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 an exception handler. In the following example, CALL proc() gets the DROP TABLE statement. No table named no_table_found exists, so the error handler is activated. The error handler destroys the original error (“no such table”) and uses SQLSTATE '99999' and An error occurred to report the error.

  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

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 for MYSQL_ERRNO, which is a MySQL extension.

The character set of character items is UTF-8. Condition information items cannot be assigned NULL in SIGNAL statements.

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

The error value obtained after SIGNAL execution is the SQLSTATE value raised by the SIGNAL statement and 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 in the output of the SHOW WARNINGS and SHOW ERRORS statements indicate the values of MYSQL_ERRNO and MESSAGE_TEXT, respectively.

To retrieve information from the diagnostics area, use the GET DIAGNOSTICS statement. For more information, see GET DIAGNOSTICS.

Effect of signals on handlers, cursors and statements

Signals of different classes have different effects on statement execution, as shown in the following table. You can determine the severity of an error based on its class. The purpose of the SIGNAL statement is to explicitly raise a user-generated error, so the corresponding signals are not ignored. The value of the sql_mode system variable is ignored, especially in strict mode.