Diagnostics area

An SQL statement can generate diagnostic information in a diagnostics area. A standard SQL statement contains a diagnostics area stack, which includes a diagnostics area for each nested execution context. Standard SQL statements also support the GET STACKED DIAGNOSTICS syntax for referencing a second diagnostics area during the execution of a condition handler. This topic describes the structure of a diagnostics area, the identified information items, how to clear and set a diagnostics area, how to push a diagnostics area into the stack, and how to pop a diagnostics area from the stack.

Structure of a diagnostics area

A diagnostics area contains the following two types of information:

• Statement information, such as the number of conditions that occurred and the number of rows affected.

• Condition information, such as error codes and messages. If a statement raises multiple conditions, the condition information part in the diagnostics area has a condition area for each condition. If a statement does not raise any condition, the condition information part in the diagnostics area is empty.

The following example shows the statement and condition information contained in the diagnostics area of a statement that raises three conditions.

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

Information items in the diagnostics area

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

A diagnostics area contains the following statement information:

• NUMBER: an integer that indicates the number of condition areas that have an information item.

• ROW_COUNT: an integer that indicates the number of rows affected by the statement. The value of ROW_COUNT is the same as that of the ROW_COUNT() function.

The condition information part of the diagnostics area contains the condition area of each condition. The condition areas are numbered from 1 to the value of the NUMBER statement condition item. If the value of NUMBER is 0, no conditional area exists.

Each condition area contains items in the following list. All items are standard SQL except for MYSQL_ERRNO, which is a MySQL extension.

• RETURNED_SQLSTATE: a string that indicates the SQLSTATE value of the condition.

• MESSAGE_TEXT: a string that indicates a condition error message.

• MYSQL_ERRNO: an integer that indicates the error code of the condition.

The above definition applies to conditions that are not raised by signals such as SIGNAL or RESIGNAL statements.

If a SIGNAL or RESIGNAL statement populates the diagnostics area, its SET clause can assign a valid value of the data type to any condition information item except RETURNED_SQLSTATE. The SIGNAL statement also sets the RETURNED_SQLSTATE value, which comes from the SQLSTATE parameter in the SIGNAL statement, rather than being set directly in the SET clause.

The SIGNAL statement also sets the statement information item NUMBER to 1, and sets ROW_COUNT to -1 to indicate an error or to 0 in other cases.

Clear and populate diagnostics areas

Non-diagnostic SQL statements automatically populate diagnostics areas and can explicitly set the content of diagnostics areas by using SIGNAL and RESIGNAL statements. You can use GET DIAGNOSTICS to check the diagnostics area and extract specified information, or use SHOW WARNINGS or SHOW ERRORS to view conditions or errors.

An SQL statement clears and sets the diagnostics area in the following way:

• When a server starts executing the statement after parsing, the server clears the non-diagnostic statements in the diagnostics area. A diagnostic statement does not clear the diagnostics area. The following statements are diagnostic statements:

  • GET DIAGNOSTICS

  • SHOW ERRORS

  • SHOW WARNINGS

• If a statement raises a condition, conditions that belong to earlier statements are cleared from the diagnostics area. The exception is that conditions raised by GET DIAGNOSTICS and RESIGNAL are added to the diagnostics area rather than being cleared.

Therefore, even if a statement does not clear the diagnostics area when it starts to execute, the diagnostics area will still be cleared when the statement raises a condition.

The following examples show the impact of various statements on the diagnostics area and use the SHOW WARNINGS statement to show the information of stored conditions.

Example 1: The DROP TABLE statement clears the diagnostics area and populates the diagnostics area when a condition occurs.

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. Therefore, it clears and populates the diagnostics 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 raises a condition. Therefore, the only valid condition number of GET DIAGNOSTICS is 1 here. The following statement generates a warning and uses the condition number 2. The warning is added to the diagnostics 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 preceding example, the diagnostics area now has two conditions. Therefore, the same GET DIAGNOSTICS statement will be executed.

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

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

Working principles of a diagnostics area stack

When the first (current) diagnostics area is pushed to the diagnostics area stack, it becomes the second diagnostics area, which is a stacked diagnostics area. In the meantime, a new current diagnostics area is created as a replica. A diagnostics area is pushed into the stack and popped from the stack in the following cases:

• Execution of a stored program

  Before the program is executed, it is pushed to the diagnostics area and then popped from the diagnostics area. If the stored program ends when the handler is executed, multiple diagnostics areas can be popped. This is because no appropriate handler exists to throw an exception, or because of the RETURN statement in the handler.

  Then, any alerts or error conditions in the popped diagnostics area are added to the current diagnostics area. However, for triggers, only error conditions are added. When the stored program ends, the caller sees these conditions in its current diagnostics area.

• Execution of a condition handler in a stored program

  When a condition handler is activated and causes a push, the stacked diagnostics area is the current area (in the stored program) before the push. The new current diagnostics area is the current diagnostics area of the handler. You can use GET [CURRENT] DIAGNOSTICS and GET STACKED DIAGNOSTICS in a handler to access the content of the current (handler) and stacked (stored program) diagnostics areas. Initially, they return the same result. However, statements executed in the handler modifies the current diagnostics area and clears and sets its content according to general rules. Statements except RESIGNAL executed in a handler cannot modify the stacked diagnostics area.

  If the handler is executed successfully, the current (handler) diagnostics area is popped, and the stacked (stored program) diagnostics area becomes the current diagnostics area again. Conditions added to the handler diagnostics area during handler execution will be added to the current diagnostics area.

• Execution of RESIGNAL

  The RESIGNAL statement is used to pass available error condition information during the execution of a condition handler in a compound statement within the stored program. The RESIGNAL statement may change some or all of the information and modify the stacked diagnostics area before passing the information. For more information, see RESIGNAL.