Diagnostics area

SQL statements can 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, which allows you 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 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 use 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 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, there are no condition areas.

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 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 any 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 diagnostic area's content by 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 starts 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 from earlier statements. Exceptions are conditions triggered by GET DIAGNOSTICS and RESIGNAL, which are added to the diagnostic area without being cleared.

Therefore, the diagnostic area's statement is cleared when the statement triggers conditions, even if it is not cleared when execution starts.

The following example shows how various statements affect 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 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, 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 at this point for GET DIAGNOSTICS. 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 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 off the stack in the following situations:

• Execution of a stored program

  Before the program is executed, the diagnostic area is pushed onto the stack, and then popped off the stack after the program is executed. If the stored program ends during handler execution, multiple diagnostic areas can be popped off the stack. 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 sees these conditions in its current diagnostic area.

• Execution of a condition handler in a stored program

  When a condition handler is activated, the diagnostic area is pushed onto the stack. The diagnostic area on the stack is the current diagnostic area before the handler is executed (in the stored program). The new current diagnostic area is the handler's diagnostic area. GET [CURRENT] DIAGNOSTICS and GET STACKED DIAGNOSTICS can be used in the handler to access the content 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 content according to general rules. Except for RESIGNAL, statements executed in the handler cannot modify the stacked diagnostic area.

  If the handler executes successfully, the current (handler) diagnostic area is popped off 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 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 more information, see RESIGNAL.