12.7.8.2. RESIGNAL Syntax

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

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name

signal_information:
    condition_information_item = simple_value_specification

condition_information_item:
{
    CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME
  | MESSAGE_TEXT
  | MYSQL_ERRNO
}

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 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 could handle part of a situation, then pass the condition “up the line” to another handler.

No special privileges are required to execute the RESIGNAL statement.

Unless otherwise indicated, the definitions and rules for condition_value and signal_information are the same for the RESIGNAL statement as for SIGNAL (see Section 12.7.8.1, “SIGNAL Syntax”).

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 [, signal_information] ...;
    
  • RESIGNAL with a condition value and possibly new signal information:

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

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 the top of the stack, so there are two diagnostics areas during handler execution:

  • The current diagnostics area, which starts as a copy of the last diagnostics area, but will be overwritten by the first procedure statement in the handler.

  • The last 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.

12.7.8.2.1. 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();

The DROP TABLE xx statement fails. The diagnostics area stack looks like this:

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

Then execution enters the EXIT handler. It starts by pushing the top of the diagnostics area stack, which now looks like this:

1. ERROR 1051 (42S02): Unknown table 'xx'
2. ERROR 1051 (42S02): Unknown table 'xx'

Usually a procedure statement clears the first diagnostics area (also called the “current” diagnostics area). BEGIN is an exception, it does not clear, it does nothing. SET is not an exception, it clears, performs the operation, and then produces a result of “success.” The diagnostics area stack now looks like this:

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

At this point, if @a = 0, RESIGNAL pops the diagnostics area stack, which now looks like this:

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 last diagnostics area (it has been “handled”), so it can be thrown away. The diagnostics area stack looks like this:

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.

12.7.8.2.2. 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 [, signal_information] ...;

As with RESIGNAL alone, the idea is to pop the diagnostics area stack so that the original information will go 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:

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

The RESIGNAL SET MYSQL_ERRNO = 5 statement results in this stack instead:

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.

12.7.8.2.3. RESIGNAL with a Condition Value and Optional New Signal Information

RESIGNAL with a condition value means “push a condition into the current diagnostics stack area.” If the SET clause is present, it also changes the error information.

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

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 is added rather than replaced is the use of a condition value.)

The RESIGNAL statement includes a condition value (SQLSTATE '45000'), so it “pushes” a new condition area, resulting in a diagnostics area stack that looks like this:

1. (condition 1) ERROR 5 (45000) Unknown table 'xx'
   (condition 2) ERROR 1051 (42S02): 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 |    5 | Unknown table 'xx'               |
| Error | 1051 | Unknown table 'xx'               |
+-------+------+----------------------------------+
12.7.8.2.4. RESIGNAL Requires an Active Handler

All forms of RESIGNAL require that a handler be active when it executes. If no handler is active, 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 1739 (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();

At the time the RESIGNAL executes, there is a handler, even though the RESIGNAL is not defined inside the handler.

A statement such as the one following may appear bizarre because RESIGNAL apparently is not in a handler:

CREATE TRIGGER t_bi BEFORE INSERT ON t FOR EACH ROW RESIGNAL;

But it does not matter. RESIGNAL does not have to be technically “in” (that is, contained in), a handler declaration. The requirement is that a handler must be active.

Copyright © 2010-2017 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout