Rechercher dans le manuel MySQL

13.6.7.4 RESIGNAL Syntax

  1. RESIGNAL [condition_value]
  2.     [SET signal_information_item
  3.     [, signal_information_item] ...]
  4.  
  5. condition_value: {
  6.     SQLSTATE [VALUE] sqlstate_value
  7.   | condition_name
  8. }
  9.  
  10. signal_information_item:
  11.     condition_information_item_name = simple_value_specification
  12.  
  13. condition_information_item_name: {
  14.     CLASS_ORIGIN
  15.   | SUBCLASS_ORIGIN
  16.   | MESSAGE_TEXT
  17.   | MYSQL_ERRNO
  18.   | CONSTRAINT_CATALOG
  19.   | CONSTRAINT_SCHEMA
  20.   | CONSTRAINT_NAME
  21.   | CATALOG_NAME
  22.   | SCHEMA_NAME
  23.   | TABLE_NAME
  24.   | COLUMN_NAME
  25.   | CURSOR_NAME
  26. }
  27.  
  28. condition_name, simple_value_specification:
  29.     (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 Syntax”). For information about the diagnostics area, see Section 13.6.7.7, “The MySQL Diagnostics Area”.

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. For additional information, see Section 13.6.7.5, “SIGNAL Syntax”.

The RESIGNAL statement takes condition_value and SET clauses, both of which are optional. This leads to several possible uses:

  • RESIGNAL alone:

    1. RESIGNAL;
  • RESIGNAL with new signal information:

    1. RESIGNAL SET signal_information_item [, signal_information_item] ...;
  • RESIGNAL with a condition value and possibly new signal information:

    1. RESIGNAL condition_value
    2.     [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 will be 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.

Contents Haut

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:

  1. delimiter //
  2.   DECLARE EXIT HANDLER FOR SQLEXCEPTION
  3.     SET @error_count = @error_count + 1;
  4.     IF @a = 0 THEN RESIGNAL; END IF;
  5.   END;
  6.   DROP TABLE xx;
  7. END//
  8. delimiter ;
  9. SET @error_count = 0;
  10. SET @a = 0;
  11. 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.

Contents Haut

RESIGNAL with New Signal Information

RESIGNAL with a SET clause provides new signal information, so the statement means pass on the error with changes:

  1. RESIGNAL SET signal_information_item [, signal_information_item] ...;

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:

  1. delimiter //
  2.   DECLARE EXIT HANDLER FOR SQLEXCEPTION
  3.     SET @error_count = @error_count + 1;
  4.     IF @a = 0 THEN RESIGNAL SET MYSQL_ERRNO = 5; END IF;
  5.   END;
  6.   DROP TABLE xx;
  7. END//
  8. delimiter ;
  9. SET @error_count = 0;
  10. SET @a = 0;
  11. 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.

Contents Haut

RESIGNAL with a Condition Value and Optional New Signal Information

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.

  1. RESIGNAL condition_value
  2.     [SET signal_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:

  1. delimiter //
  2.   DECLARE EXIT HANDLER FOR SQLEXCEPTION
  3.     SET @error_count = @error_count + 1;
  4.     IF @a = 0 THEN RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=5; END IF;
  5.   END;
  6.   DROP TABLE xx;
  7. END//
  8. delimiter ;
  9. SET @error_count = 0;
  10. SET @a = 0;
  11. SET @@max_error_count = 2;
  12. CALL p();

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:

  1. mysql> CALL p();
  2. ERROR 5 (45000): Unknown table 'xx'
  3. mysql> SHOW ERRORS;
  4. +-------+------+----------------------------------+
  5. | Level | Code | Message                          |
  6. +-------+------+----------------------------------+
  7. | Error | 1051 | Unknown table 'xx'               |
  8. | Error |    5 | Unknown table 'xx'               |
  9. +-------+------+----------------------------------+

Contents Haut

RESIGNAL Requires Condition Handler Context

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:

  1. mysql> CREATE PROCEDURE p () RESIGNAL;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> CALL p();
  5. ERROR 1645 (0K000): RESIGNAL when handler not active

Here is a more difficult example:

  1. delimiter //
  2.   RESIGNAL;
  3.   RETURN 5;
  4. END//
  5.   DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @a=f();
  6.   SIGNAL SQLSTATE '55555';
  7. END//
  8. delimiter ;
  9. 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.


Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-resignal.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut