MySQL SIGNAL RESIGNAL

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. 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.

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, or MESSAGE_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.

MySQL SIGNAL RESIGNAL

MySQL SIGNAL Statement

Syntax

Example

More Example

RESIGNAL Statement

RESIGNAL Overview

RESIGNAL Alone

RESIGNAL with New Signal Information

RESIGNAL with a Condition Value and Optional New Signal Information

RESIGNAL Requires Condition Handler Context

Category Posts

Categories

Recent Posts