GET DIAGNOSTICS

Syntax:
GET [CURRENT | STACKED] DIAGNOSTICS {
statement_information_item
[, statement_information_item] ...
| CONDITION condition_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. (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. The keyword STACKED means to retrieve information
from the second diagnostics area, which is available only if the
current context is a condition handler. If neither keyword is given,
the default is to use the current diagnostics area.

The GET DIAGNOSTICS statement is typically used in a handler within a
stored program. It is a MySQL extension that GET [CURRENT] DIAGNOSTICS
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' |
+-------+------------------------------------+

This extension applies only to the current diagnostics area. It does
not apply to the second diagnostics area because GET STACKED
DIAGNOSTICS is permitted only if the current context is a condition
handler. If that is not the case, a GET STACKED DIAGNOSTICS when
handler not active error occurs.

For a description of the diagnostics area, see
https://dev.mysql.com/doc/refman/8.0/en/diagnostics-area.html. Briefly,
it contains two kinds of information:

o Statement information, such as the number of conditions that occurred
or the affected-rows count.

o 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:

o 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;

o 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 target = item_name
assignments, separated by commas. Each assignment names a target
variable and either a 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
https://dev.mysql.com/doc/refman/8.0/en/diagnostics-area.html#diagnosti
cs-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 current 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 |
+-------------------------------------------------------------------------+

URL: https://dev.mysql.com/doc/refman/8.0/en/get-diagnostics.html