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 for referencing 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 onto 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, error codes and messages. 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 in the diagnostic area 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 items:

• 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, 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 not generated by signals (that is, not by SIGNAL or RESIGNAL statements).

If a SIGNAL (or RESIGNAL) statement fills the diagnostic area, its SET clause can assign valid values 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 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.

SQL statements clear and set the diagnostic area 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 a condition, 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 is cleared when a statement triggers a condition, even if it was not cleared at the start of execution.

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 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 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 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 previous one. Diagnostic areas are pushed onto and popped from the stack in the following situations:

• Execution of stored programs

  Before the execution of a stored program, the diagnostic area is pushed onto the stack, and after the program's execution, it 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 within 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 will see these conditions in its current diagnostic area.

• Execution of condition handlers within stored programs

  When a condition handler is activated, the diagnostic area is pushed onto the stack. The current diagnostic area before the handler is executed 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 within 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 within the handler modify the current diagnostic area according to general rules, clearing and setting its contents. Only RESIGNAL can modify the stacked diagnostic area within the 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 its 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. For more details, see RESIGNAL.