The RESIGNAL statement is used to pass error condition information available during the execution of a conditional handler in compound statements within stored procedures or functions, triggers, or events.
Syntax and parameters of RESIGNAL
RESIGNAL may modify some or all of the information before passing on the error condition. RESIGNAL is related to SIGNAL; SIGNAL can originate a condition, whereas RESIGNAL can only make corresponding modifications based on the existing error information.
Executing the RESIGNAL statement does not require any privileges.
The syntax for 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 definition and rules of RESIGNAL are the same as those of SIGNAL. For example, condition_value can be an SQLSTATE value that indicates an error, warning, or "not found". For more information, see SIGNAL.
The condition_value and SET clauses in the RESIGNAL syntax are optional. Usage examples include:
Individual
RESIGNALRESIGNAL;RESIGNAL: contains information about the new signal.RESIGNAL SET signal_information_item [, signal_information_item] ...;RESIGNAL: contains conditional values and potential new signal information:RESIGNAL condition_value [SET signal_information_item [, signal_information_item] ...];
All of the preceding usage methods will cause the following changes to the diagnostics and conditions section:
A diagnostic section contains one or more conditional sections.
The condition section contains condition items, such as
SQLSTATEvalues orMESSAGE_TEXT.
For the stack's diagnostic area, when a handler gains control, it pushes the diagnostic area to the top of the stack. Therefore, there are two diagnostic areas during the execution of a handler:
The replica of the first (current) diagnostic area, which is also the last diagnostic area, serves as the starting point. It will be overwritten by the first statement in the current diagnostic area modified by the processor.
The last diagnostic section (of the stack), which contains the condition areas set before the handler takes control.
Note
The maximum number of regions in the condition area within the diagnostic area is determined by the value of the
max_error_countsystem variable.
All forms of RESIGNAL require the current context to be a condition handler. Otherwise, RESIGNAL is illegal and an error occurs when the handler is inactive. Example:
obclient> CREATE PROCEDURE p1() RESIGNAL;
Query OK, 0 rows affected
obclient> CALL p1();
ERROR 1645 (0K000): RESIGNAL when handler not active
Individual RESIGNAL
The simple meaning of RESIGNAL is "pass the error without making any changes". Using only RESIGNAL can restore the previous diagnostic area and make it the current diagnostic area. That is to say, it "pops up" the stack of the diagnostic area.
An example is as follows:
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 the DROP TABLE tbl1 statement fails. The diagnostic area stack is as follows:
DA 1. ERROR 1051 (42S02): Unknown table 'tbl1'
Then, the EXIT handler is executed. 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 contents of the first (current) and second (stack) diagnostic areas are the same. The content of the first diagnostic area can be modified later through statements executed in the handler.
Typically, a procedural statement clears the first diagnostic area (except for BEGIN). However, a SET statement can clear the diagnostic area, 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'
In this case, if @x = 0, RESIGNAL will pop up the stack in the diagnostic area. The diagnostic area now looks like this:
DA 1. ERROR 1051 (42S02): Unknown table 'tbl1'
If @x is not 0, the handler terminates. This means the current diagnostic region is no longer useful (it has been "processed") and can be discarded, allowing the stack's diagnostic region to become the current diagnostic region again. The current diagnostic region stack is as follows:
DA 1. ERROR 0000 (00000): Successful operation
The preceding example demonstrates that the execution of a handler does not destroy information related to the conditions that activated it.
RESIGNAL with new signal information
The RESIGNAL statement with a SET clause provides new signal information, so the meaning of this statement is "to change the error being propagated". The syntax is as follows:
RESIGNAL SET signal_information_item [, signal_information_item] ...;
Like a standalone RESIGNAL, it pops up the stack in the diagnostic area, causing the original information to disappear. Unlike a standalone RESIGNAL, all contents specified in the SET clause will be modified.
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 the DROP TABLE tbl1 statement fails. The stack in the diagnostic area is as follows:
DA 1. ERROR 1051 (42S02): Unknown table 'tbl1'
The RESIGNAL SET MYSQL_ERRNO = 5 statement creates 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, thereby modifying the style of the first conditional area in the diagnostic region.
RESIGNAL with conditional values and optional new signal information
The RESIGNAL clause with a condition pushes the condition to the current diagnostic area. If a SET clause is present, it also modifies the error message.
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 up" the diagnostic area stack, just like a standalone RESIGNAL would. However, it also changes the diagnostic area based on condition values or signal information.
An example is as follows:
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 the previous examples. Because a condition value is used, a new condition is added rather than replacing the existing one.
The RESIGNAL statement contains a condition value (SQLSTATE '45000'), so it adds a new condition area, resulting in the following stack for the diagnostic area:
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:
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' |
+-------+------+----------------------------------+