RESIGNAL

The RESIGNAL statement is used to pass error condition information that is available during the execution of a compound statement in a stored procedure or function, trigger, or event.

Syntax and parameter description of RESIGNAL

RESIGNAL may change part or all of the information before passing the error condition information. RESIGNAL is related to SIGNAL, which can initiate a condition, while RESIGNAL can only make corresponding modifications based on existing error information.

No permissions are required to execute the RESIGNAL statement.

The syntax of the RESIGNAL statement is as follows:

RESIGNAL [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

}

For condition_value and signal_information_item, the definitions and rules of RESIGNAL are the same as those of SIGNAL. For example, condition_value can be an SQLSTATE value, which can indicate an error, a warning, or a "not found" condition. For more information, see SIGNAL.

In the syntax of RESIGNAL, both condition_value and the SET clause are optional. They can be used in the following ways:

• A standalone RESIGNAL

  RESIGNAL;

• A RESIGNAL with new signal information:

  RESIGNAL SET signal_information_item [, signal_information_item] ...;

• A RESIGNAL with a condition value and possibly new signal information:

  RESIGNAL condition_value
  [SET signal_information_item [, signal_information_item] ...];

All of the above usages will result in the following changes in the diagnostic and condition areas:

• A diagnostic area contains one or more condition areas.

• A condition area contains condition information items, such as an SQLSTATE value or MESSAGE_TEXT.

For a diagnostic area in a stack, when the handler gains control, it will push the diagnostic area to the top of the stack. Therefore, during the execution of the handler, there are two diagnostic areas:

• The first (current) diagnostic area, which is a copy of the last diagnostic area at the beginning, will be overwritten by the first statement executed by the handler.

• The last (stack) diagnostic area, which contains the condition area set before the handler gains control.

info

The maximum number of condition areas in a diagnostic area is determined by the value of the max_error_count system variable.

All forms of RESIGNAL require that the current context is a condition handler. Otherwise, RESIGNAL is invalid and an error will occur when the handler is inactive. Here is an example:

CREATE PROCEDURE p1() RESIGNAL;
Query OK, 0 rows affected

CALL p1();
ERROR 1645 (0K000): RESIGNAL when handler not active

Standalone RESIGNAL

A standalone RESIGNAL means "pass the error without any changes," and using RESIGNAL alone can be used to restore the previous diagnostic area and make it the current diagnostic area. In other words, it will "pop" the diagnostic area stack.

Here is an example:

DROP TABLE IF EXISTS tbl1;

delimiter //

CREATE PROCEDURE proc()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @err_count = @err_count + 1;
    IF @x = 0 THEN RESIGNAL; END IF;
  END;
  DROP TABLE tbl1;
END//

delimiter ;

SET @err_count = 0;

SET @x = 0;

CALL proc();

Assume that the DROP TABLE tbl1 statement fails. The diagnostic area stack is as follows:

DA 1. ERROR 1051 (42S02): Unknown table 'tbl1'

Then, the execution enters the EXIT handler. It first pushes the diagnostic area to the top of the stack, as follows:

DA 1. ERROR 1051 (42S02): Unknown table 'tbl1'
DA 2. ERROR 1051 (42S02): Unknown table 'tbl1'

At this point, the content of the first (current) and second (stack) diagnostic areas is the same. The content of the first diagnostic area can be modified by the statements executed in the handler.

Usually, a procedure statement will clear the first diagnostic area (except for BEGIN). The SET statement can clear, perform an operation, and produce a "success" result. The current diagnostic area stack is as follows:

DA 1. ERROR 0000 (00000): Successful operation
DA 2. ERROR 1051 (42S02): Unknown table 'tbl1'

At this point, if @x = 0, the RESIGNAL statement will pop the diagnostic area stack, and the current diagnostic area is as follows:

DA 1. ERROR 1051 (42S02): Unknown table 'tbl1'

If @x is not 0, the handler will end, which means that the current diagnostic area is no longer useful (it has been "processed") and can be discarded, causing the diagnostic area in the stack to become the current one again. The current diagnostic area stack is as follows:

DA 1. ERROR 0000 (00000): Successful operation

The above example shows that the execution of the handler does not destroy the information related to the condition that activated the handler.

RESIGNAL with new signal information

A RESIGNAL statement with a SET clause provides new signal information. Therefore, the meaning of this statement is "pass the error with changes," and the syntax is as follows:

RESIGNAL SET signal_information_item [, signal_information_item] ...;

Like a standalone RESIGNAL, this statement pops the diagnostic area stack, causing the original information to disappear. Unlike a standalone RESIGNAL, the content specified in the SET clause will be changed.

Here is an example:

DROP TABLE IF EXISTS tbl1;

delimiter //

CREATE PROCEDURE proc()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @err_count = @err_count + 1;
    IF @x = 0 THEN RESIGNAL SET MYSQL_ERRNO = 5; END IF;
  END;
  DROP TABLE tbl1;
END//

delimiter ;

SET @err_count = 0;

SET @x = 0;

CALL proc();

Assume that the DROP TABLE tbl1 statement fails. The diagnostic area stack is as follows:

DA 1. ERROR 1051 (42S02): Unknown table 'tbl1'

The RESIGNAL SET MYSQL_ERRNO = 5 statement will generate a stack and change the error number, as follows:

DA 1. ERROR 5 (42S02): Unknown table 'tbl1'

The RESIGNAL statement can change any or all signal information items, changing the style of the first condition area in the diagnostic area.

RESIGNAL with a condition value and optional new signal information

A RESIGNAL statement with a condition value indicates "push the condition to the current diagnostic area." If a SET clause is present, it will also change the error information.

RESIGNAL condition_value
[SET signal_information_item [, signal_information_item] ...];

This form of RESIGNAL restores the previous diagnostic area and makes it the current diagnostic area. In other words, it "pops" the diagnostic area stack, which is the same as a standalone RESIGNAL. However, it will also change the diagnostic area based on the condition value or signal information.

Here is an example:

DROP TABLE IF EXISTS tbl1;

delimiter //

CREATE PROCEDURE proc()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @err_count = @err_count + 1;
    IF @x = 0 THEN RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=5; END IF;
  END;
  DROP TABLE tbl1;
END//

delimiter ;

SET @err_count = 0;

SET @x = 0;

CALL proc();

SHOW ERRORS;

In this example, if RESIGNAL occurs, the current condition area is different from the previous example. Since a condition value is used, a new condition is added instead of replacing the existing condition.

The RESIGNAL statement includes a condition value (SQLSTATE '45000'), so it adds a new condition area, resulting in the following diagnostic area stack:

DA 1. (condition 2) ERROR 1051 (42S02): Unknown table 'tbl1'
(condition 1) ERROR 5 (45000) Unknown table 'tbl1'

The results of CALL proc() and SHOW ERRORS in this example are as follows:

CALL proc();
ERROR 5 (45000): Unknown table 'xx'
SHOW ERRORS;
+-------+------+----------------------------------+
| Level | Code | Message                          |
+-------+------+----------------------------------+
| Error | 1051 | Unknown table 'xx'               |
| Error |    5 | Unknown table 'xx'               |
+-------+------+----------------------------------+