Diagnostics area

SQL statements generate diagnostic information in the diagnostics area. Standard SQL includes a diagnostics area stack, with a diagnostics area for each nested execution context. Standard SQL also supports the GET STACKED DIAGNOSTICS syntax to reference the second diagnostics area during conditional handler execution. This topic describes the structure of the diagnostics area, the information items it identifies, how to clear and set the diagnostics area, and how the diagnostics area is pushed and popped.

Structure of the diagnostic area

The diagnostic area contains the following two types of information:

• Statement information. For example, the number of conditions that occurred or the number of affected rows.

• Condition information. For example, the error code and message. If a statement triggers multiple conditions, the diagnostic area contains a condition area for each condition. If a statement does not trigger any conditions, the diagnostic area does not contain any condition information.

The following example shows the statement and condition information that the diagnostic area contains for a statement that triggers three conditions.

Statement information:  -- Statement information
  row count
  ... other statement information items ...  -- Other statement information items
Condition area list:  -- Condition area list
  Condition area 1:   -- Condition area 1
    error code for condition 1    -- Error code for condition 1
    error message for condition 1  -- Error message for condition 1
    ... other condition information items ...   -- Other condition information items
  Condition area 2:   -- Condition area 2
    error code for condition 2:     -- Error code for condition 2
    error message for condition 2   -- Error message for condition 2
    ... other condition information items ...   -- Other condition information items
  Condition area 3:   -- Condition area 3
    error code for condition 3       -- Error code for condition 3
    error message for condition 3    -- Error message for condition 3
    ... other condition information items ...   -- Other condition information items

Information items in the diagnostic area

The diagnostic area contains statement and condition information items. Numeric items are integers, and character items are in the UTF-8 character set. No item can be NULL. If a statement that fills the diagnostic area does not set a statement or condition information item, the value of the item is 0 or an empty string, depending on the data type of the item.

The diagnostic area contains the following statement information:

• NUMBER: An integer indicating the number of condition areas that contain information.

• ROW_COUNT: An integer indicating the number of rows affected by the statement. ROW_COUNT has the same value as the ROW_COUNT() function.

The condition information part of the diagnostic area contains a condition area for each condition. Condition areas are numbered from 1 to the value of the statement condition NUMBER item. If NUMBER is 0, no condition areas exist.

Each condition area in standard SQL contains the following items (where MYSQL_ERRNO is an extended item):

• RETURNED_SQLSTATE: A string indicating the SQLSTATE value of the condition.

• MESSAGE_TEXT: A string indicating the error message of the condition.

• MYSQL_ERRNO: An integer indicating the error code of the condition.

The preceding definitions apply to conditions that are not generated by signals (that is, by SIGNAL or RESIGNAL statements).

If a SIGNAL (or RESIGNAL) statement fills the diagnostic area, its SET clause can assign a valid value of the appropriate data type to any condition information item except RETURNED_SQLSTATE. SIGNAL also sets the RETURNED_SQLSTATE value, which comes from the SQLSTATE parameter of the SIGNAL statement, not directly from its SET clause.

SIGNAL also sets the statement information item NUMBER to 1 and sets ROW_COUNT to -1 to indicate an error, otherwise to 0.

Clearing and filling the diagnostic area

Non-diagnostic SQL statements automatically fill the diagnostic area, and you can explicitly set the contents of the diagnostic area by using SIGNAL and RESIGNAL statements. You can use GET DIAGNOSTICS to extract specified information from the diagnostic area, or you can use SHOW WARNINGS or SHOW ERRORS to view conditions or errors.

SQL statements clear and set the diagnostic area as follows:

• When the server begins to execute a statement after parsing it, it clears the diagnostic area for non-diagnostic statements. Diagnostic statements do not clear the diagnostic area. The following statements are diagnostic statements:

  • GET DIAGNOSTICS

  • SHOW ERRORS

  • SHOW WARNINGS

• If a statement triggers a condition, the diagnostic area clears conditions that belong to earlier statements. Exceptions are conditions triggered by GET DIAGNOSTICS and RESIGNAL, which are added to the diagnostic area without being cleared.

So, diagnostic area statements are not cleared when they are first executed, but they are cleared when they trigger conditions.

The following example shows the effect of various statements on the diagnostic area and uses SHOW WARNINGS to display stored condition information.

Example 1: The DROP TABLE statement clears the diagnostic area and fills it with condition information when a condition occurs.

DROP TABLE IF EXISTS test.no_table_found;
Query OK, 0 rows affected, 1 warning

SHOW WARNINGS;
+-------+------+-------------------------------------+
| Level | Code | Message                             |
+-------+------+-------------------------------------+
| Note  | 1051 | Unknown table 'test.no_table_found' |
+-------+------+-------------------------------------+
1 row in set

Example 2: The SET statement generates an error, so it clears and fills the diagnostic area.

SET @var1 = @@var;
ERROR 1193 (HY000): Unknown system variable 'var'

SHOW WARNINGS;
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Error | 1193 | Unknown system variable 'var' |
+-------+------+-------------------------------+
1 row in set

Example 3: The SET statement in the preceding example generates a condition, so 1 is the only valid condition number for GET DIAGNOSTICS at this point. The following statement generates a warning and uses condition number 2. This warning is added to the diagnostic area without being cleared.

GET DIAGNOSTICS CONDITION 2 @var2 = MESSAGE_TEXT;
Query OK, 0 rows affected, 1 warning 

SHOW WARNINGS;
+-------+------+------------------------------+
| Level | Code | Message                      |
+-------+------+------------------------------+
| Error | 1193 | Unknown system variable 'var'|
| Error | 1753 | Invalid condition number     |
+-------+------+------------------------------+
2 rows in set

Example 4: Based on the preceding example, the diagnostic area now contains two conditions, so the same GET DIAGNOSTICS statement executes successfully.

GET DIAGNOSTICS CONDITION 2 @var2 = MESSAGE_TEXT;
Query OK, 0 rows affected

SELECT @var2;
+---------------------------+
| @var2                     |
+---------------------------+
| Invalid condition number  |
+---------------------------+
1 row in set

How the diagnostic area stack works

When a diagnostic area is pushed onto the diagnostic area stack, the first (current) diagnostic area becomes the second (stacked) diagnostic area, and a new current diagnostic area is created as a copy of the first. The diagnostic area is pushed onto the stack and popped from the stack in the following situations:

• Execution of stored programs

  Before the program is executed, the diagnostic area is pushed onto the stack, and after the program is executed, the diagnostic area is popped from the stack. If the stored program ends during the execution of a handler, multiple diagnostic areas can be popped from the stack. This can happen if no appropriate handler is triggered or if a RETURN statement is executed in the handler.

  Any warnings or error conditions in the popped diagnostic area are added to the current diagnostic area. However, for triggers, only error conditions are added. When the stored program ends, the caller sees these conditions in its current diagnostic area.

• Execution of condition handlers in stored programs

  When a condition handler is activated, the diagnostic area is pushed onto the stack. The diagnostic area that was current before the push becomes the stacked diagnostic area, and a new current diagnostic area is created as a copy of the previous one. GET [CURRENT] DIAGNOSTICS and GET STACKED DIAGNOSTICS can be used in a handler to access the contents of the current (handler) and stacked (stored program) diagnostic areas. Initially, they return the same results, but statements executed in the handler modify the current diagnostic area, clearing and setting its contents based on general rules. Only RESIGNAL can modify the stacked diagnostic area in a handler.

  If the handler executes successfully, the current (handler) diagnostic area is popped from the stack, and the stacked (stored program) diagnostic area becomes the current diagnostic area again. Conditions added to the handler's diagnostic area during execution are added to the current diagnostic area.

• Execution of RESIGNAL

  During the execution of a condition handler in a compound statement within a stored program, the RESIGNAL statement is used to pass available error condition information. RESIGNAL may modify some or all of the information before passing it and can modify the stacked diagnostic area, as described in RESIGNAL.