13.6.7.7. The MySQL Diagnostics Area

SQL statements produce diagnostic information that populates the diagnostics area. Standard SQL has a diagnostics area stack, containing a diagnostics area for each nested execution context. Standard SQL also supports GET STACKED DIAGNOSTICS syntax for referring to the second diagnostics area during condition handler execution. MySQL does not support the STACKED keyword until MySQL 5.7. In MySQL 5.6, there is a single diagnostics area containing information from the most recent statement that wrote to it.

This section describes the structure of the diagnostics area in MySQL, the information items recognized by MySQL and how statements clear and set the diagnostics area.

13.6.7.7.1. Diagnostics Area Structure

The diagnostics area contains two kinds of information:

Statement information, such as the number of conditions that occurred or the affected-rows count.
Condition information, such as the error code and message. If a statement raises multiple conditions, this part of the diagnostics area has a condition area for each one. If a statement raises no conditions, this part of the diagnostics area is empty.

For a statement that produces three conditions, the diagnostics area contains statement and condition information like this:

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

13.6.7.7.2. Diagnostics Area Information Items

The diagnostics area contains statement and condition information items. Numeric items are integers. The character set for character items is UTF-8. No item can be NULL. If a statement or condition item is not set by a statement that populates the diagnostics area, its value will be 0 or the empty string, depending on the item data type.

The statement information part of the diagnostics area contains these items:

NUMBER: An integer indicating the number of condition areas that have 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 (see Section 12.14, "Information Functions").

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

Each condition area contains the items in the following list. All items are standard SQL except MYSQL_ERRNO, which is a MySQL extension. The definitions apply for conditions generated other than by a signal (that is, by a SIGNAL or RESIGNAL statement). For nonsignal conditions, MySQL populates only those condition items not described as always empty. The effects of signals on the condition area are described later.

CLASS_ORIGIN: A string containing the class of the RETURNED_SQLSTATE value. If the RETURNED_SQLSTATE value begins with a class value defined in SQL standards document ISO 9075-2 (section 24.1, SQLSTATE), CLASS_ORIGIN is 'ISO 9075'. Otherwise, CLASS_ORIGIN is 'MySQL'.
SUBCLASS_ORIGIN: A string containing the subclass of the RETURNED_SQLSTATE value. If CLASS_ORIGIN is 'ISO 9075' or RETURNED_SQLSTATE ends with '000', SUBCLASS_ORIGIN is 'ISO 9075'. Otherwise, SUBCLASS_ORIGIN is 'MySQL'.
RETURNED_SQLSTATE: A string that indicates the SQLSTATE value for the condition.
MESSAGE_TEXT: A string that indicates the error message for the condition.
MYSQL_ERRNO: An integer that indicates the MySQL error code for the condition.
CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME: Strings that indicate the catalog, schema, and name for a violated constraint. They are always empty.
CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME: Strings that indicate the catalog, schema, table, and column related to the condition. They are always empty.
CURSOR_NAME: A string that indicates the cursor name. This is always empty.

For the RETURNED_SQLSTATE, MESSAGE_TEXT, and MYSQL_ERRNO values for particular errors, see Section C.3, "Server Error Codes and Messages".

If a SIGNAL (or RESIGNAL) statement populates the diagnostics area, its SET clause can assign to any condition information item except RETURNED_SQLSTATE any value that is legal for the item data type. SIGNAL also sets the RETURNED_SQLSTATE value, but not directly in its SET clause. That value comes from the SIGNAL statement SQLSTATE argument.

SIGNAL also sets statement information items. It sets NUMBER to 1, and ROW_COUNT to –1 for errors and 0 otherwise.

13.6.7.7.3. How the Diagnostics Area is Populated

Most SQL statements populate the diagnostics area automatically, and its contents can be set explicitly with the SIGNAL and RESIGNAL statements. The diagnostics area can be examined with GET DIAGNOSTICS to extract specific items, or with SHOW WARNINGS or SHOW ERRORS to see conditions or errors.

SQL statements clear and set the diagnostics area as follows:

When the server starts executing a statement after parsing it, it clears the diagnostics area for nondiagnostic statements that use tables. Diagnostic statements are SHOW WARNINGS, SHOW ERRORS, and GET DIAGNOSTICS.
If a statement raises a condition, the diagnostics area is cleared of conditions that belong to earlier statements. The exception is that conditions raised by GET DIAGNOSTICS and RESIGNAL are added to the diagnostics area without clearing it.

Thus, even a statement that does not normally clear the diagnostics area when it begins executing clears it if the statement raises a condition.

The following example shows the effect of various statements on the diagnostics area, using SHOW WARNINGS to display information about conditions stored there.

This DROP TABLE statement uses a table, so it clears the diagnostics area and populates it when the condition occurs:

mysql> DROP TABLE IF EXISTS
            test.no_such_table;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> SHOW WARNINGS;+-------+------+------------------------------------+| Level | Code | Message                            |+-------+------+------------------------------------+| Note  | 1051 | Unknown table 'test.no_such_table' |+-------+------+------------------------------------+1 row in set (0.00 sec)

This SET statement does not use tables and does not generate warnings, so it leaves the diagnostics area unchanged:

mysql> SET @x = 1;Query OK, 0 rows affected (0.00 sec)mysql> SHOW WARNINGS;+-------+------+------------------------------------+| Level | Code | Message                            |+-------+------+------------------------------------+| Note  | 1051 | Unknown table 'test.no_such_table' |+-------+------+------------------------------------+1 row in set (0.00 sec)

This SET statement generates an error, so it clears and populates the diagnostics area:

mysql> SET @x = @@x;ERROR 1193 (HY000): Unknown system variable 'x'mysql> SHOW WARNINGS;+-------+------+-----------------------------+| Level | Code | Message                     |+-------+------+-----------------------------+| Error | 1193 | Unknown system variable 'x' |+-------+------+-----------------------------+1 row in set (0.00 sec)

The previous SET statement produced a single condition, so 1 is the only valid condition number for GET DIAGNOSTICS at this point. The following statement uses a condition number of 2, which produces a warning that is added to the diagnostics area without clearing it:

mysql> GET DIAGNOSTICS CONDITION 2 @p =
            MESSAGE_TEXT;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> SHOW WARNINGS;+-------+------+------------------------------+| Level | Code | Message                      |+-------+------+------------------------------+| Error | 1193 | Unknown system variable 'xx' || Error | 1753 | Invalid condition number     |+-------+------+------------------------------+2 rows in set (0.00 sec)

Now there are two conditions in the diagnostics area, so the same GET DIAGNOSTICS statement succeeds:

mysql> GET DIAGNOSTICS CONDITION 2 @p =
            MESSAGE_TEXT;Query OK, 0 rows affected (0.00 sec)mysql> SELECT
            @p;+--------------------------+| @p                       |+--------------------------+| Invalid condition number |+--------------------------+1 row in set (0.01 sec)

13.6.7.7.4. Diagnostics Area-Related System Variables

Certain system variables control or are related to some aspects of the diagnostics area:

max_error_count controls the number of condition areas in the diagnostics area. If more conditions than this occur, MySQL silently discards information for the excess conditions. (Conditions added by RESIGNAL are always added, with older conditions being discarded as necessary to make room.)
warning_count indicates the number of conditions that occurred. This includes errors, warnings, and notes. Normally, NUMBER and warning_count are the same. However, as the number of conditions generated exceeds max_error_count, the value of warning_count continues to rise whereas NUMBER remains capped at max_error_count because no additional conditions are stored in the diagnostics area.
error_count indicates the number of errors that occurred. This value includes "not found" and exception conditions, but excludes warnings and notes. Like warning_count, its value can exceed max_error_count.
If the sql_notes system variable is set to 0, notes are not recorded and do not increment warning_count.

Example: If max_error_count is 10, the diagnostics area can contain a maximum of 10 condition areas. Suppose that a statement raises 20 conditions, 12 of which are errors. In that case, the diagnostics area contains the first 10 conditions, NUMBER is 10, warning_count is 20, and error_count is 12.

Changes to the value of max_error_count have no effect until the next attempt to modify the diagnostics area. If the diagnostics area contains 10 condition areas and max_error_count is set to 5, that has no immediate effect on the size or content of the diagnostics area.

Before MySQL 5.6, statement information items are not available directly. ROW_COUNT can be obtained by calling the ROW_COUNT() function. NUMBER is approximated by the value of the warning_count system variable. However, whereas NUMBER is capped to the value of max_error_count, warning_count is not.