MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
GET [CURRENT] DIAGNOSTICS {statement_information_item
[,statement_information_item
] ... | CONDITIONcondition_number
condition_information_item
[,condition_information_item
] ... }statement_information_item
:target
=statement_information_item_name
condition_information_item
:target
=condition_information_item_name
statement_information_item_name
: { NUMBER | ROW_COUNT }condition_information_item_name
: { CLASS_ORIGIN | SUBCLASS_ORIGIN | RETURNED_SQLSTATE | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME }condition_number
,target
: (see following discussion)
SQL statements produce diagnostic information that populates the
diagnostics area. The GET
DIAGNOSTICS
statement enables applications to inspect
this information. It is available as of MySQL 5.6.4. (You can
also use SHOW WARNINGS
or
SHOW ERRORS
to see conditions or
errors.)
No special privileges are required to execute
GET DIAGNOSTICS
.
The keyword CURRENT
means to retrieve
information from the current diagnostics area. In MySQL, it has
no effect because that is the default behavior.
GET DIAGNOSTICS
is typically used
in a handler within a stored program, but it is a MySQL
extension that it is permitted outside handler context to check
the execution of any SQL statement. For example, if you invoke
the mysql client program, you can enter these
statements at the prompt:
mysql>DROP TABLE test.no_such_table;
ERROR 1051 (42S02): Unknown table 'test.no_such_table' mysql>GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
mysql>SELECT @p1, @p2;
+-------+------------------------------------+ | @p1 | @p2 | +-------+------------------------------------+ | 42S02 | Unknown table 'test.no_such_table' | +-------+------------------------------------+
For a description of the diagnostics area, see Section 13.6.7.7, “The MySQL Diagnostics Area”. Briefly, it 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 ...
GET DIAGNOSTICS
can obtain either
statement or condition information, but not both in the same
statement:
To obtain statement information, retrieve the desired
statement items into target variables. This instance of
GET DIAGNOSTICS
assigns the
number of available conditions and the rows-affected count
to the user variables @p1
and
@p2
:
GET DIAGNOSTICS @p1 = NUMBER, @p2 = ROW_COUNT;
To obtain condition information, specify the condition
number and retrieve the desired condition items into target
variables. This instance of GET
DIAGNOSTICS
assigns the SQLSTATE value and error
message to the user variables @p3
and
@p4
:
GET DIAGNOSTICS CONDITION 1 @p3 = RETURNED_SQLSTATE, @p4 = MESSAGE_TEXT;
The retrieval list specifies one or more
assignments,
separated by commas. Each assignment names a target variable and
either a
target
=
item_name
statement_information_item_name
or
condition_information_item_name
designator, depending on whether the statement retrieves
statement or condition information.
Valid target
designators for storing
item information can be stored procedure or function parameters,
stored program local variables declared with
DECLARE
, or user-defined
variables.
Valid condition_number
designators
can be stored procedure or function parameters, stored program
local variables declared with
DECLARE
, user-defined variables,
system variables, or literals. A character literal may include a
_charset
introducer. A warning occurs
if the condition number is not in the range from 1 to the number
of condition areas that have information. In this case, the
warning is added to the diagnostics area without clearing it.
When a condition occurs, MySQL does not populate all condition
items recognized by GET
DIAGNOSTICS
. For example:
mysql>GET DIAGNOSTICS CONDITION 1
@p5 = SCHEMA_NAME, @p6 = TABLE_NAME;
mysql>SELECT @p5, @p6;
+------+------+ | @p5 | @p6 | +------+------+ | | | +------+------+
In standard SQL, if there are multiple conditions, the first
condition relates to the SQLSTATE
value
returned for the previous SQL statement. In MySQL, this is not
guaranteed. To get the main error, you cannot do this:
GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO;
Instead, retrieve the condition count first, then use it to specify which condition number to inspect:
GET DIAGNOSTICS @cno = NUMBER; GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;
For information about permissible statement and condition information items, and which ones are populated when a condition occurs, see Diagnostics Area Information Items.
Here is an example that uses GET
DIAGNOSTICS
and an exception handler in stored
procedure context to assess the outcome of an insert operation.
If the insert was successful, the procedure uses
GET DIAGNOSTICS
to get the
rows-affected count. This shows that you can use
GET DIAGNOSTICS
multiple times to
retrieve information about a statement as long as the
diagnostics area has not been cleared.
CREATE PROCEDURE do_insert(value INT) BEGIN -- Declare variables to hold diagnostics area information DECLARE code CHAR(5) DEFAULT '00000'; DECLARE msg TEXT; DECLARE nrows INT; DECLARE result TEXT; -- Declare exception handler for failed insert DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT; END; -- Perform the insert INSERT INTO t1 (int_col) VALUES(value); -- Check whether the insert was successful IF code = '00000' THEN GET DIAGNOSTICS nrows = ROW_COUNT; SET result = CONCAT('insert succeeded, row count = ',nrows); ELSE SET result = CONCAT('insert failed, error = ',code,', message = ',msg); END IF; -- Say what happened SELECT result; END;
Suppose that t1.int_col
is an integer column
that is declared as NOT NULL
. The procedure
produces these results when invoked to insert
non-NULL
and NULL
values,
respectively:
mysql>CALL do_insert(1);
+---------------------------------+ | result | +---------------------------------+ | insert succeeded, row count = 1 | +---------------------------------+ mysql>CALL do_insert(NULL);
+-------------------------------------------------------------------------+ | result | +-------------------------------------------------------------------------+ | insert failed, error = 23000, message = Column 'int_col' cannot be null | +-------------------------------------------------------------------------+
Within a condition handler, GET
DIAGNOSTICS
should be used before other statements
that might clear the diagnostics area and cause information to
be lost about the condition that activated the handler. For
information about when the diagnostics area is set and cleared,
see Section 13.6.7.7, “The MySQL Diagnostics Area”.