Diagnostic areas

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

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 contains no condition areas.

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, its value 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 with 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.

These definitions apply to conditions not generated by signals (that is, not generated 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 other than 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 the 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 using SIGNAL and RESIGNAL statements. You can use GET DIAGNOSTICS to extract specified information from the diagnostic area, or use SHOW WARNINGS or SHOW ERRORS to view conditions or errors.

The way SQL statements clear and set the diagnostic area is as follows:

• When the server begins executing 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:

  • GET DIAGNOSTICS
  • SHOW ERRORS
  • SHOW WARNINGS

• If a statement triggers conditions, 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.

Therefore, diagnostic area statements are not cleared when execution begins, but are cleared when the statement triggers conditions.

The following examples show the effects of various statements on the diagnostic area and use SHOW WARNINGS to display stored condition information.

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

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

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

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

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

Example 3: The SET statement in the previous 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, which is added to the diagnostic area without being cleared.

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

obclient> 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 previous example, the diagnostic area now contains two conditions, so the same GET DIAGNOSTICS statement will execute successfully.

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

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

How the diagnostic area stack works

When pushed to 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 to 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 to the stack, and then popped after the program is executed. If the stored program ends during the execution of a handler, multiple diagnostic areas can be popped. This occurs when no appropriate handler is triggered or when a RETURN statement in the handler is triggered.

  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 will see 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 to the stack. The diagnostic area that was current before the push becomes the stacked diagnostic area. The new current diagnostic area is the diagnostic area of the handler. GET [CURRENT] DIAGNOSTICS and GET STACKED DIAGNOSTICS can be used in the 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 according to general rules. Only RESIGNAL can modify the stacked diagnostic area.

  If the handler executes successfully, the current (handler) diagnostic area is popped, 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 composite 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 modify the stacked diagnostic area, for details, see RESIGNAL.