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
DIAGNOSTICS syntax for referring to the second
diagnostics area during condition handler execution. MySQL
STACKED keyword as of MySQL 5.7.
Before that, MySQL does not support
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, how statements clear and set the diagnostics area, and how diagnostics areas are pushed to and popped from the stack.
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 ...
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 is 0 or the empty string, depending on the item data
The statement information part of the diagnostics area contains these items:
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
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 begins
with a class value defined in SQL standards document ISO
9075-2 (section 24.1, SQLSTATE),
SUBCLASS_ORIGIN: A string containing
the subclass of the
RETURNED_SQLSTATE: A string that
SQLSTATE value for the
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_NAME: Strings that indicate
the catalog, schema, and name for a violated constraint.
They are always empty.
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.
MYSQL_ERRNO values for particular errors,
see Section C.3, “Server Error Codes and Messages”.
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.
also sets the
RETURNED_SQLSTATE value, but
not directly in its
SET clause. That value
comes from the
SIGNAL also sets statement
information items. It sets
NUMBER to 1. It
ROW_COUNT to –1 for errors and 0
Nondiagnostic SQL statements populate the diagnostics area
automatically, and its contents can be set explicitly with the
RESIGNAL statements. The
diagnostics area can be examined with
DIAGNOSTICS to extract specific items, or with
SHOW WARNINGS or
SHOW ERRORS to see conditions
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. (Before MySQL 5.7.2, the server clears the
diagnostics area for nondiagnostic statements that use
tables.) Diagnostic statements do not clear the
diagnostics area (
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
WARNINGS to display information about conditions
DROP TABLE statement
clears the diagnostics area and populates it when the
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)
statement generates an error, so it clears and populates the
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)
statement produced a single condition, so 1 is the only valid
condition number for
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:
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
GET DIAGNOSTICS statement
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)
When a push to the diagnostics area stack occurs, the first (current) diagnostics area becomes the second (stacked) diagnostics area and a new current diagnostics area is created as a copy of it. Diagnostics areas are pushed to and popped from the stack under the following circumstances:
Execution of a stored program
A push occurs before the program executes and a pop occurs
afterward. If the stored program ends while handlers are
executing, there can be more than one diagnostics area to
pop; this occurs due to an exception for which there are
no appropriate handlers or due to
RETURN in the handler.
Any warning or error conditions occurring during stored program execution then are added to the current diagnostics area, except that, for triggers, only errors are added. When the stored program ends, the caller sees these conditions in its current diagonstics area.
Execution of a condition handler within a stored program
When a push occurs as a result of condition handler
activation, the stacked diagnostics area is the area that
was current within the stored program prior to the push.
The new now-current diagnostics area is the handler's
current diagnostics area.
[CURRENT] DIAGNOSTICS and
STACKED DIAGNOSTICS can be used within the
handler to access the contents of the current (handler)
and stacked (stored program) diagnostics areas. Initially,
they return the same result, but statements executing
within the handler modify the current diagnostics area,
clearing and setting its contents according to the normal
rules (see Section 18.104.22.168.3, “How the Diagnostics Area is Populated”).
The stacked diagnostics area cannot be modified by
statements executing within the handler except
If the handler executes successfully, the current (handler) diagnostics area is popped and the stacked (stored program) diagnostics area again becomes the current diagnostics area. Conditions added to the handler diagnostics area during handler execution are added to the current diagnostics area.
passes on the error condition information that is
available during execution of a condition handler within a
compound statement inside a stored program.
RESIGNAL may change some or
all information before passing it on, modifying the
diagnostics stack as described in
Section 22.214.171.124, “
Certain system variables control or are related to some aspects of the diagnostics area:
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
RESIGNAL are always
added, with older conditions being discarded as necessary
to make room.)
the number of conditions that occurred. This includes
errors, warnings, and notes. Normally,
warning_count are the
same. However, as the number of conditions generated
the value of
to rise whereas
NUMBER remains capped
because no additional conditions are stored in the
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
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
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices