Learn about diagnostics in programmatic compound statements. This section covers error handling and information retrieval within stored procedures and functions for effective debugging.
The diagnostics area contains information about the error conditions which were produced by an SQL statement, as well as some information about the statement which generated them.
The statement information area contains the following data:
NUMBER
is the number of conditions which are present in the diagnostics area.
ROW_COUNT
has the same value as the ROW_COUNT() function for the statement that produced the conditions.
Each condition has several properties, which are explained here.
The following table shows the type and size of all the properties:
RETURNED_SQLSTATE
VARCHAR(5)
MYSQL_ERRNO
SMALLINT UNSIGNED
MESSAGE_TEXT
VARCHAR(512)
CLASS_ORIGIN
VARCHAR(64)
SUBCLASS_ORIGIN
VARCHAR(64)
CONSTRAINT_CATALOG
VARCHAR(64)
CONSTRAINT_SCHEMA
VARCHAR(64)
CONSTRAINT_NAME
VARCHAR(64)
CATALOG_NAME
VARCHAR(64)
SCHEMA_NAME
VARCHAR(64)
TABLE_NAME
VARCHAR(64)
COLUMN_NAME
VARCHAR(64)
CURSOR_NAME
VARCHAR(64)
These properties can never be set to NULL
. If they are empty, the empty string is used.
The most common ones have a value for all built-in errors, and can be read both via SQL and via the API:
RETURNED_SQLSTATE
is the SQLSTATE
of the condition. It is a five characters code, composed by a class (first two characters) and a subclass (last three characters). For more information about this property, refer to the SQLSTATE page.
MYSQL_ERRNO
is the error code. Each built-in condition has a unique numeric code. 0 indicates success, but it cannot be explicitly set or read via SQL. For a list of built-in error codes, refer to MariaDB Error Codes. The API function to read it is mysql_errno().
MESSAGE_TEXT
is a descriptive, human-readable message. For built-in errors, parsing this string is the only way to get more information about the error. For example, parsing a message like "Table 'tab1' already exists", a program can find out that the missing table is tab1. The API function to read it is mysql_error().
For conditions generated by the user via SIGNAL
, if MYSQL_ERRNO
and MESSAGE_TEXT
are not specified, their default values depend on the first two SQLSTATE
characters:
'00' means 'success'. It can not be set in any way, and can only be read via the API.
For '01' class, default MYSQL_ERRNO
is 1642 and default MESSAGE_TEXT
is 'Unhandled user-defined warning condition'.
For '02' class, default MYSQL_ERRNO
is 1643 and default MESSAGE_TEXT
is 'Unhandled user-defined not found condition'.
For all other cases, including the '45000' value, default MYSQL_ERRNO
is 1644 and default MESSAGE_TEXT
is 'Unhandled user-defined exception condition'.
There are more condition properties, which are never set for built-in errors. They can only be set via SIGNAL
and RESIGNAL
statements, and can only be read via GET DIAGNOSTICS
- not via the API. Such properties are:
CLASS_ORIGIN
indicates whether the SQLSTATE
uses a standard class or a software-specific class. If it is defined in the SQL standards document ISO 9075-2 (section 24.1, SQLSTATE), this property's value is supposed to be 'ISO 9075', otherwise it is supposed to be 'MySQL'. However, any string is accepted.
SUBCLASS_ORIGIN
indicates whether the SQLSTATE
uses a standard subclass or a software-specific class. If the SQLSTATE
is defined in the SQL standards document ISO 9075-2 (section 24.1, SQLSTATE
), this property's value is supposed to be 'ISO 9075', otherwise it is supposed to be 'MySQL'. However, any string is accepted.
SCHEMA_NAME
indicates in which schema (database) the error occurred.
TABLE_NAME
indicates the name of the table which was accessed by the failed statement.
COLUMN_NAME
indicates the name of the column which was accessed by the failed statement.
CONSTRAINT_NAME
indicates the name of the constraint that was violated.
CONSTRAINT_SCHEMA
indicates in which schema the violated constraint is located.
CURSOR_NAME
indicates the name of the cursor which caused the error.
The following properties can be used and are defined in the standard SQL, but have no meaning because MariaDB doesn't currently support catalogs:
CATALOG_NAME
is used by the standard SQL to indicate in which catalog the error occurred.
CONSTRAINT_CATALOG
is used by the standard SQL to indicate in which catalog the violated constraint is located.
When a statement produces one or more error conditions (errors, warnings, notes) the conditions are inserted into the diagnostics area, and the statement information area is updated with that statement’s information. Usually, this also clears all the old conditions from the diagnostics area, but there is an exception: if the new statement is a correctly parsed RESIGNAL
or GET DIAGNOSTICS
, the old contents will remain in the diagnostics area. SIGNAL
clears the old conditions.
When a table-based statement (like INSERT
) is executed, the old data in the diagnostics area is cleared even if the statement doesn't produce any condition. However, statements which don't access any table (like SET
, or a SELECT
with no FROM
clause) is executed and produces no warnings, the diagnostics area remains unchanged.
The maximum number of conditions that can be in the diagnostics area is max_error_count. If this value is 0, the diagnostics area is empty. If this variable is changed, the new value takes effect with the next statement (that is, the diagnostics area is not immediately truncated).
The following statements explicitly add conditions to the diagnostics area:
SIGNAL: produces a custom error.
RESIGNAL: after an error is produced, generates a modified version of that error.
The following statements read contents from the diagnostics area:
GET DIAGNOSTICS is the only way to read all information.
SHOW WARNINGS shows a summary of errors, warnings and notes.
SHOW ERRORS shows a summary of errors.
DECLARE HANDLER can be used to handle error conditions within stored programs.
DECLARE CONDITION can be used to associate an SQLSTATE
or an error code to a name. That name can be referenced in DECLARE HANDLER
, SIGNAL
and RESIGNAL
statements.
All these statements can also be executed inside a stored routine. However, only SHOW WARNINGS
and SHOW ERRORS
can be executed as a prepared statement. After an EXECUTE statement, the diagnostics area contains information about the prepared statement, if it produces error conditions.
This page is licensed: CC BY-SA / Gnu FDL
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
| ROW_NUMBER
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. It 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 single 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.
If GET DIAGNOSTICS
produces an error condition (because the command is properly parsed but not correctly used), the diagnostics area is not emptied, and the new condition is added.
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.
For a list of SQLSTATE
values and MariaDB error codes, see MariaDB Error Codes.
The type for all the condition properties is VARCHAR
(64), except for MYSQL_ERRNO
, whose valid range is 1 to 65534.
You can use the ROW_NUMBER
property to retrieve the row number, too, even if the error text does not mention it. This property is named ERROR_INDEX
. ROW_NUMBER
is a reserved word.
There is no way, short of parsing the error text, to know in what row an error had happened.
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,
@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SELECT @sqlstate, @errno, @text;
+-----------+--------+-------------------------------------------+
| @sqlstate | @errno | @text |
+-----------+--------+-------------------------------------------+
| HY000 | 1266 | Using storage engine InnoDB for table 't' |
+-----------+--------+-------------------------------------------+
INSERT INTO t1 VALUES (4,'d',1.00104),(1,'a',1.00101),(2,'b',1.00102);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
GET DIAGNOSTICS CONDITION 1 @row_num= ROW_NUMBER; -- from MariaDB 10.7
SELECT @row_num;
+----------+
| @row_num |
+----------+
| 2 |
+----------+
This page is licensed: CC BY-SA / Gnu FDL
SQLSTATE
is a code which identifies SQL error conditions. It composed by five characters, which can be numbers or uppercase ASCII letters. An SQLSTATE
value consists of a class (first two characters) and a subclass (last three characters).
There are three important standard classes. They all indicate in which logical group of errors the condition falls. They match to a particular keyword which can be used with DECLARE HANDLER. Also, the SQLSTATE
class determines the default value for the MYSQL_ERRNO
and MESSAGE_TEXT
condition properties.
'00' means 'success'. It can not be set in any way, and can only be read via the API.
'01' contains all warnings, and matches to the SQLWARNING
keyword. The default MYSQL_ERRNO
is 1642 and default MESSAGE_TEXT
is 'Unhandled user-defined warning condition'.
'02' is the NOT FOUND
class. The default MYSQL_ERRNO
is 1643 and default MESSAGE_TEXT
is 'Unhandled user-defined not found condition'.
All other classes match the SQLEXCEPTION
keyword. The default MYSQL_ERRNO
is 1644 and default MESSAGE_TEXT
is 'Unhandled user-defined exception condition'.
The subclass, if it is set, indicates a particular condition, or a particular group of conditions within the class. However the '000' sequence means 'no subclass'.
For example, if you try to SELECT from a table which does not exist, a 1109 error is produced, with a '42S02' SQLSTATE
. '42' is the class and 'S02' is the subclass. This value matches to the SQLEXCEPTION
keyword. When FETCH
is called for a cursor which has already reached the end, a 1329 error is produced, with a '02000' SQLSTATE. The class is '02' and there is no subclass (because '000' means 'no subclass'). It can be handled by a NOT FOUND
handlers.
The standard SQL specification says that classes beginning with 0, 1, 2, 3, 4, A, B, C, D, E, F and G are reserved for standard-defined classes, while other classes are vendor-specific. It also says that, when the class is standard-defined, subclasses starting with those characters (except for '000') are standard-defined subclasses, while other subclasses are vendor-defined. However, MariaDB and MySQL do not strictly obey this rule.
To read the SQLSTATE
of a particular condition which is in the diagnostics area, the GET DIAGNOSTICS statement can be used: the property is called RETURNED_SQLSTATE
. For user-defined conditions (SIGNAL and RESIGNAL statements), a SQLSTATE value must be set via the SQLSTATE
clause. However, SHOW WARNINGS and SHOW ERRORS do not display the SQLSTATE
.
For user-defined conditions, MariaDB and MySQL recommend the '45000' SQLSTATE
class.
'HY000' is called the "general error": it is the class used for builtin conditions which do not have a specific SQLSTATE
class.
A partial list of error codes and matching SQLSTATE
values can be found under MariaDB Error Codes.
This page is licensed: CC BY-SA / Gnu FDL