GET DIAGNOSTICS
GET [CURRENT] DIAGNOSTICS { statement_property [, statement_property] ... | CONDITION condition_number condition_property [, condition_property] ... } statement_property: variable = statement_property_name condition_property: variable = condition_property_name statement_property_name: NUMBER | ROW_COUNT condition_property_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
The GET DIAGNOSTICS statement has been added in MariaDB 10.0.4 and MySQL 5.6.4.
The diagnostics area contains information about the errors, warnings and notes which were produced by the last SQL statement. If that statement didn't produce any warnings, the diagnostics area contains information about the last executed statement which involved a table. The GET DIAGNOSTICS statement copies the requested information from the diagnostics area to the specified variables. It is possible to use both user variables or local variables.
To use GET DIAGNOSTICS, it is important to know how the diagnostics area is structured. I has two sub-areas: the statement information area and the error conditions information area. For details, please refer to the diagnostics area page.
Each single GET DIAGNOSTICS command can read information from the statement information area or from a signle error condition. This means that, if you have two warnings and you want to know the number of warnings, and read both the warnings, you need to issue GET DIAGNOSTICS three times.
The CURRENT keywords adds nothing to the statement, because MariaDB has only one diagnostics area.
Getting information from a condition
To read information from a condition, the CONDITION keyword must be specified and it must be followed by the condition number. This number can be specified as a constant value or as a variable. The first condition's index is 1. If the error condition does not exist, the variables will not change their value and a 1758 error will be produced ("Invalid condition number").
The condition properties that can be read with GET DIAGNOSTICS are the same that can be set with SIGNAL and RESIGNAL statements. They are explained in the diagnostics area page. However, there is one more property: RETURNED_SQLSTATE, which indicates the condition's SQLSTATE.
Examples
In the following example, a statement generates two warnings, and GET DIAGNOSTICS is used to get the number of warnings:
CREATE TABLE `test`.`t` (`c` INT) ENGINE = x; Query OK, 0 rows affected, 2 warnings (0.19 sec) GET DIAGNOSTICS @num_conditions = NUMBER; SELECT @num_conditions; +-----------------+ | @num_conditions | +-----------------+ | 2 | +-----------------+
Then, we can see the warnings:
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT; SELECT @sqlstate, @errno, @text; +-----------+--------+----------------------------+ | @sqlstate | @errno | @text | +-----------+--------+----------------------------+ | 42000 | 1286 | Unknown storage engine 'x' | +-----------+--------+----------------------------+ GET DIAGNOSTICS CONDITION 2 @sqlstate = RETURNED_SQLSTATE, @er rno = MYSQL_ERRNO, @text = MESSAGE_TEXT; SELECT @sqlstate, @errno, @text; +-----------+--------+-------------------------------------------+ | @sqlstate | @errno | @text | +-----------+--------+-------------------------------------------+ | HY000 | 1266 | Using storage engine InnoDB for table 't' | +-----------+--------+-------------------------------------------+