`GET DIAGNOSTICS` Syntax

SQL statements produce diagnostic information that populates the diagnostics area. The GET DIAGNOSTICS statement enables applications to inspect this information. No special privileges are required to execute GET DIAGNOSTICS, which is available as of MariaDB 5.6.4.

The keyword CURRENT means to retrieve information from the current diagnostics area. In MySQL, it has no effect because that is the default behavior.

For a description of the diagnostics area, see , "The MariaDB Diagnostics Area". Briefly, it contains two kinds of information:

Statement information, such as the affected-rows count or the number of conditions that occurred.
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:
```
GET DIAGNOSTICS @p1 = ROW_COUNT, @p2 = NUMBER;
```
To obtain condition information, specify the condition number and retrieve the desired condition items into target variables:
```
GET DIAGNOSTICS CONDITION 1
 @p1 = RETURNED_SQLSTATE, @p2 = 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 OUT 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.

GET DIAGNOSTICS is typically used within stored programs, but it is a MariaDB extension that it is permitted outside that 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' |
+-------+------------------------------------+

Currently, not all condition items recognized by GET DIAGNOSTICS are populated when a condition occurs. For example:

mysql> GET DIAGNOSTICS CONDITION 1
 -> @p3 = SCHEMA_NAME, @p4 = TABLE_NAME;
mysql> SELECT @p3, @p4;
+------+------+
| @p3 | @p4 |
+------+------+
| | |
+------+------+

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 also 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 rows 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 rows = ROW_COUNT;
 SET result = CONCAT('insert succeeded, row count = ',rows);
 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:

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 , "How the Diagnostics Area is Populated".

In standard SQL, the first condition relates to the SQLSTATE value returned for the previous SQL statement. In MySQL, this is not guaranteed, so to get the main error, you cannot do this:

GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO;

Instead, do this:

GET DIAGNOSTICS Syntax

Retornar

`GET DIAGNOSTICS` Syntax