If you are interested to learn about the MySQL Cursor
This article will cover how to use SIGNAL and RESIGNAL statements for raising error conditions inside stored programs.
MySQL SIGNAL Statement
The SIGNAL query is a mechanism used to return a warning or error message appearing during the execution of a stored program, such as stored procedure, trigger or event, or stored function. This statement provides error information to an error handler, the outer portion of an application, or the client. It also provides control over error characteristics such as error number, SQLSTATE, value, and message in stored procedures. The SIGNAL statement does not require any privileges for their execution.
MySQL provides RESIGNAL statement for raising a warning or error condition similar to the SIGNAL statement in terms of functionality and syntax, except that:
- The RESIGNAL statement must be used within an error or warning handler themselves. Otherwise, MySQL generates an error message: RESIGNAL when the handler is not active.
- The RESIGNAL statement can be used without any attributes, even the SQLSTATE value or attributes as in the SIGNAL statement.
If we use only RESIGNAL statement in the stored program, all attributes are the same as those passed to the condition handler.
Syntax
The following is the basic syntax to use the SIGNAL statement:
SIGNAL SQLSTATE | condition_name; SET condition_information_item_name1 = value1, condition_information_item_name1 = value2, etc;
Here, the SQLSTATE or a condition_name declared by the DECLARE CONDITION statement indicates the error value to be returned. It is to note that the SIGNAL statement must have an SQLSTATE value or a named condition defined with an SQLSTATE value.13.8M187SQL CREATE TABLE. The SQLSTATE consists of five alphanumeric characters. We do not use the SQLSTATE code with ’00’ because it indicates success, which is not valid for raising an error. A Bad SQLSTATE error is found when the value is invalid. If we want to catch-all error handling, we must assign the SQLSTATE code ‘45000’, which means an unhandled user-defined exception. The optional SET clause is used to provide the caller with information. If there is a need for returning multiple condition information item names, it is required to use a comma operator to separate each name/value pair. The condition_information_item_name can be any of the following and must be specified only once in the SET clause. Otherwise, it will return a duplicate condition information item error.
- CLASS_ORIGIN
- MESSAGE_TEXT
- MYSQL_ERRNO
- CONSTRAINT_NAME
- SCHEMA_NAME
- TABLE_NAME
- CURSOR_NAME, etc.
Let us understand SIGNAL implementation with an example.
Example
Here we will first create a procedure named add Student. This procedure first counts the total students with the input student id that we pass in the stored procedure. Second, it checks them in the table, and if the number of students is not 1, it will return an error with SQLSTATE 45000 along with the message student id does not exist in the student_info table. We will notice that 45000 is a generic SQLSTATE for an unhandled user-defined exception.
Here is the complete procedure code:
CREATE PROCEDURE addStudent( IN stud_id INT, IN stud_name VARCHAR(35), IN subject VARCHAR(25), IN marks INT, IN phone VARCHAR(15) ) BEGIN DECLARE C INT; SELECT COUNT(student_id) INTO C FROM student_info WHERE student_id = stud_id; -- check if student id not exists IF(C != 1) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Student id not found in student_info table'; END IF; END $$ DELIMITER ;
When we call the procedure with the provided student detail, we will get an error message.
CALL addStudent (16, 'Kevin', 'science', 66, '69934569359');
Here is the output:

More Example
Let us understand it with an example where the procedure changes the error message before issuing it to the caller.
DELIMITER $$ CREATE PROCEDURE get Devision (IN numerator INT, IN denominator INT, OUT res double) BEGIN DECLARE Division_By_Zero CONDITION FOR SQLSTATE '45000'; DECLARE CONTINUE HANDLER FOR Division_By_Zero RESIGNAL SET MESSAGE_TEXT = 'The denominator cannot be zero'; -- IF denominator = 0 THEN SIGNAL Division_By_Zero; ELSE SET res := numerator / denominator; END IF; END $$ DELIMITER ;
If we call the procedure using the below statement, we will get an error message:
CALL getDivision (25, 0, @res);
Here is the result:

RESIGNAL Statement
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: { 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. RE
SIGNAL
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.
RESIGNAL Overview
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.”
The RESIGNAL
statement takes condition_value
and SET
clauses, both of which are optional. This leads to several possible uses:
RESIGNAL
alone:RESIGNAL;
RESIGNAL
with new signal information:RESIGNAL SET signal_information_item [, signal_information_item] ...;
RESIGNAL
with a condition value and possibly new signal information:RESIGNAL condition_value [SET signal_information_item [, signal_information_item] ...];
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
, orMESSAGE_TEXT
.
There is a stack of diagnostics areas. When a handler takes control, it pushes a diagnostics area to the top of the stack, so there are two diagnostics areas during handler execution:
- The first (current) diagnostics area, which starts as a copy of the last diagnostics area, but is overwritten by the first statement in the handler that changes the current diagnostics area.
- The last (stacked) diagnostics area, which has the condition areas that were set up before the handler took control.
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.
RESIGNAL Alone
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 New Signal Information
RESIGNAL
with a SET
clause provides new signal information, so the statement means “pass on the error with changes”:
RESIGNAL SET signal_information_item [, signal_information_item] ...;
As with alone, the idea is to pop the diagnostics area stack so that the original information goes out. Unlike 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 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 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 and Optional New Signal Information
The condition value means “push a condition into the current diagnostics area.” If the SET
clause is present, it also changes the error information.
RESIGNAL condition_value [SET signal_information_item [, signal_information_item] ...];
This form of 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 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 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 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' | +-------+------+----------------------------------+
RESIGNAL Requires Condition Handler Context
All forms of require that the current context be a condition handler. Otherwise 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();
it 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.