MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
RESIGNAL [condition_value
] [SETsignal_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
: { CLASS_ORIGIN | SUBCLASS_ORIGIN | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME }condition_name
,simple_value_specification
: (see following discussion)
RESIGNAL
passes on the error
condition information that is available during execution of a
condition handler within a compound statement inside a stored
procedure or function, trigger, or event.
RESIGNAL
may change some or all
information before passing it on.
RESIGNAL
is related to
SIGNAL
, but instead of
originating a condition as SIGNAL
does, RESIGNAL
relays existing
condition information, possibly after modifying it.
RESIGNAL
makes it possible to
both handle an error and return the error information.
Otherwise, by executing an SQL statement within the handler,
information that caused the handler's activation is destroyed.
RESIGNAL
also can make some
procedures shorter if a given handler can handle part of a
situation, then pass the condition “up the line” to
another handler.
No privileges are required to execute the
RESIGNAL
statement.
All forms of RESIGNAL
require
that the current context be a condition handler. Otherwise,
RESIGNAL
is illegal and a
RESIGNAL when handler not active
error
occurs.
To retrieve information from the diagnostics area, use the
GET DIAGNOSTICS
statement (see
Section 13.6.7.3, “GET DIAGNOSTICS Statement”). For information about the
diagnostics area, see Section 13.6.7.7, “The MySQL Diagnostics Area”.
For condition_value
and
signal_information_item
, the
definitions and rules are the same for
RESIGNAL
as for
SIGNAL
. For example, the
condition_value
can be an
SQLSTATE
value, and the value can indicate
errors, warnings, or “not found.” For additional
information, see Section 13.6.7.5, “SIGNAL Statement”.
The RESIGNAL
statement takes
condition_value
and
SET
clauses, both of which are optional.
This leads to several possible uses:
These use cases all cause changes to the diagnostics and condition areas:
A diagnostics area contains one or more condition areas.
A condition area contains condition information items,
such as the SQLSTATE
value,
MYSQL_ERRNO
, or
MESSAGE_TEXT
.
The maximum number of condition areas in a diagnostics area is
determined by the value of the
max_error_count
system
variable. See
Diagnostics Area-Related System Variables.
A simple RESIGNAL
alone means
“pass on the error with no change.” It restores
the last diagnostics area and makes it the current diagnostics
area. That is, it “pops” the diagnostics area
stack.
Within a condition handler that catches a condition, one use
for RESIGNAL
alone is to
perform some other actions, and then pass on without change
the original condition information (the information that
existed before entry into the handler).
Example:
DROP TABLE IF EXISTS xx; delimiter // CREATE PROCEDURE p () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET @error_count = @error_count + 1; IF @a = 0 THEN RESIGNAL; END IF; END; DROP TABLE xx; END// delimiter ; SET @error_count = 0; SET @a = 0; CALL p();
Suppose that the DROP TABLE xx
statement
fails. The diagnostics area stack looks like this:
DA 1. ERROR 1051 (42S02): Unknown table 'xx'
Then execution enters the EXIT
handler. It
starts by pushing a diagnostics area to the top of the stack,
which now looks like this:
DA 1. ERROR 1051 (42S02): Unknown table 'xx' DA 2. ERROR 1051 (42S02): Unknown table 'xx'
At this point, the contents of the first (current) and second (stacked) diagnostics areas are the same. The first diagnostics area may be modified by statements executing subsequently within the handler.
Usually a procedure statement clears the first diagnostics
area. BEGIN
is an exception, it does not
clear, it does nothing. SET
is not an
exception, it clears, performs the operation, and produces a
result of “success.” The diagnostics area stack
now looks like this:
DA 1. ERROR 0000 (00000): Successful operation DA 2. ERROR 1051 (42S02): Unknown table 'xx'
At this point, if @a = 0
,
RESIGNAL
pops the diagnostics
area stack, which now looks like this:
DA 1. ERROR 1051 (42S02): Unknown table 'xx'
And that is what the caller sees.
If @a
is not 0, the handler simply ends,
which means that there is no more use for the current
diagnostics area (it has been “handled”), so it
can be thrown away, causing the stacked diagnostics area to
become the current diagnostics area again. The diagnostics
area stack looks like this:
DA 1. ERROR 0000 (00000): Successful operation
The details make it look complex, but the end result is quite useful: Handlers can execute without destroying information about the condition that caused activation of the handler.
RESIGNAL
with a
SET
clause provides new signal information,
so the statement means “pass on the error with
changes”:
RESIGNAL SETsignal_information_item
[,signal_information_item
] ...;
As with RESIGNAL
alone, the
idea is to pop the diagnostics area stack so that the original
information goes out. Unlike
RESIGNAL
alone, anything
specified in the SET
clause changes.
Example:
DROP TABLE IF EXISTS xx; delimiter // CREATE PROCEDURE p () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET @error_count = @error_count + 1; IF @a = 0 THEN RESIGNAL SET MYSQL_ERRNO = 5; END IF; END; DROP TABLE xx; END// delimiter ; SET @error_count = 0; SET @a = 0; CALL p();
Remember from the previous discussion that
RESIGNAL
alone results in a
diagnostics area stack like this:
DA 1. ERROR 1051 (42S02): Unknown table 'xx'
The RESIGNAL SET MYSQL_ERRNO = 5
statement
results in this stack instead, which is what the caller sees:
DA 1. ERROR 5 (42S02): Unknown table 'xx'
In other words, it changes the error number, and nothing else.
The RESIGNAL
statement can
change any or all of the signal information items, making the
first condition area of the diagnostics area look quite
different.
RESIGNAL
with a condition value
means “push a condition into the current diagnostics
area.” If the SET
clause is present,
it also changes the error information.
RESIGNALcondition_value
[SETsignal_information_item
[,signal_information_item
] ...];
This form of RESIGNAL
restores
the last diagnostics area and makes it the current diagnostics
area. That is, it “pops” the diagnostics area
stack, which is the same as what a simple
RESIGNAL
alone would do.
However, it also changes the diagnostics area depending on the
condition value or signal information.
Example:
DROP TABLE IF EXISTS xx; delimiter // CREATE PROCEDURE p () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET @error_count = @error_count + 1; IF @a = 0 THEN RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=5; END IF; END; DROP TABLE xx; END// delimiter ; SET @error_count = 0; SET @a = 0; SET @@max_error_count = 2; CALL p(); SHOW ERRORS;
This is similar to the previous example, and the effects are
the same, except that if
RESIGNAL
happens, the current
condition area looks different at the end. (The reason the
condition adds to rather than replaces the existing condition
is the use of a condition value.)
The RESIGNAL
statement includes
a condition value (SQLSTATE '45000'
), so it
adds a new condition area, resulting in a diagnostics area
stack that looks like this:
DA 1. (condition 2) ERROR 1051 (42S02): Unknown table 'xx' (condition 1) ERROR 5 (45000) Unknown table 'xx'
The result of CALL
p()
and SHOW ERRORS
for this example is:
mysql>CALL p();
ERROR 5 (45000): Unknown table 'xx' mysql>SHOW ERRORS;
+-------+------+----------------------------------+ | Level | Code | Message | +-------+------+----------------------------------+ | Error | 1051 | Unknown table 'xx' | | Error | 5 | Unknown table 'xx' | +-------+------+----------------------------------+
All forms of RESIGNAL
require
that the current context be a condition handler. Otherwise,
RESIGNAL
is illegal and a
RESIGNAL when handler not active
error
occurs. For example:
mysql>CREATE PROCEDURE p () RESIGNAL;
Query OK, 0 rows affected (0.00 sec) mysql>CALL p();
ERROR 1645 (0K000): RESIGNAL when handler not active
Here is a more difficult example:
delimiter // CREATE FUNCTION f () RETURNS INT BEGIN RESIGNAL; RETURN 5; END// CREATE PROCEDURE p () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @a=f(); SIGNAL SQLSTATE '55555'; END// delimiter ; CALL p();
RESIGNAL
occurs within the
stored function f()
. Although
f()
itself is invoked within the context of
the EXIT
handler, execution within
f()
has its own context, which is not
handler context. Thus, RESIGNAL
within
f()
results in a “handler not
active” error.
In MySQL 5.5, handler scope rules are less developed.
f()
is considered to execute within handler
context and RESIGNAL
within
f()
is legal.