RESIGNAL

You can use the RESIGNAL statement to pass error condition information that is available during the execution of a condition handler in a stored procedure or a compound statement within a function, trigger, or event.

Syntax and parameters

The RESIGNAL statement may change some or all of the error condition information before passing it. RESIGNAL is related with SIGNAL. SIGNAL can raise a condition, while RESIGNAL can only make corresponding modifications to existing error messages.

You do not need any privilege 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

}

The definitions and rules of condition_value and signal_information_item for RESIGNAL are the same as those for SIGNAL. For example, condition_value can be an SQLSTATE value that indicates an error, a warning, or “not found”. For more information, see SIGNAL.

Both the condition_value and SET clauses in the RESIGNAL syntax are optional. You can use the RESIGNAL statement as follows:

• Simple RESIGNAL:

  RESIGNAL;
  

• RESIGNAL with new signal information:

  RESIGNAL SET signal_information_item [, signal_information_item] ...;
  

• RESIGNAL with condition values and possible new signal information:

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

The preceding usage will cause the following changes in the diagnostics and condition areas:

• One diagnostics area contains one or more condition areas.

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

For the stacked diagnostics area, when the handler takes control, it pushes the diagnostics area to the top of the stack. Then, two diagnostics areas will be present during the execution of the handler:

• The first (current) diagnostics area that starts from the copy of the last diagnostics area. This area will be overridden by the first statement of the handler that changes the current diagnostics area.

• The last (stacked) diagnostics area that contains the condition area that is set before the handler takes control.

  Note

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

The current context of RESIGNAL, no matter in which form, must be a conditional handler. Otherwise, the RESIGNAL statement is illegal, and an error is reported when the handler is inactive. Here is an example:

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

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

Simple RESIGNAL

The meaning of a simple RESIGNAL is “to pass the error information without any modifications”. You can use RESIGNAL to restore the previous diagnostics area and make it the current diagnostics area. In other words, it will pop up the diagnostics 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 execution of the DROP TABLE tbl1 statement fails. The diagnostics area stack is as follows:

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

Then the program proceeds to the EXIT handler. It first pushes the diagnostics 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 time, the first (current) and second (stacked) diagnostics areas have the same content. Then, the first diagnostics area can be modified by the statement executed in the handler.

Usually, a procedural statement clears the first diagnostics area, except BEGIN. The SET statement can clear the area, perform operations, and generate the “Succeeded” result. The diagnostics area stack now looks like this:

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

At this time, if @x = 0, the RESIGNAL statement will pop the diagnostics area stack. The current diagnostics area is as follows:

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

If @x is not 0, the handler ends, which means that the current diagnostics area is no longer required and has been processed. Therefore, it can be discarded, and the stacked diagnostics area then becomes the current diagnostics area again. The diagnostics area stack now looks like this:

DA 1. ERROR 0000 (00000): Successful operation

The above example shows that the execution of the handler does not necessarily destroy information about the condition that triggers the handler.

RESIGNAL with new signal information

The RESIGNAL statement with the SET clause provides new signal information. Such a statement passes the error by change. The syntax is as follows:

RESIGNAL SET signal_information_item [, signal_information_item] ...;

Similar to a simple RESIGNAL statement, a RESIGNAL statement with new signal information pops the diagnostics area stack to make the original information disappear. Unlike in a simple RESIGNAL statement, the content specified in the SET clause changes.

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 diagnostics area stack is as follows:

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

The RESIGNAL SET MYSQL_ERRNO = 5 statement generates a stack and changes the error number, as shown below:

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

The RESIGNAL statement can change any or all signal information items so that the style of the first condition area in the diagnostics area changes.

RESIGNAL with condition values and optional new signal information

A RESIGNAL statement with condition values indicates to push a condition to the current diagnostics area. If a SET clause exists, it also changes the error message.

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

This form of RESIGNAL statement restores the previous diagnostics area and makes it the current diagnostics area. In other words, it pops the diagnostics area stack, which is the same as a simple RESIGNAL statement. However, it also changes the diagnostics area based on condition values 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 the example, if RESIGNAL occurs, the current condition area is different from that in the previous example. A condition is added instead of replacing the existing condition because a condition value is used.

The RESIGNAL statement contains a condition value (SQLSTATE '45000'). Therefore, it adds a new condition area, resulting in the diagnostics area stack as follows:

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

The result of CALL proc() and SHOW ERRORS for this example is as follows:

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