The diagnostics area
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.
Statement information
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.
Condition information
Each condition has several properties, which are explained here.
Data types and sizes
The following table shows the type and size of all the properties:
| Property name | Property type |
|---|---|
| RETURNED_SQLSTATE | VARCHAR(5) |
| MYSQL_ERRNO | SMALLINT UNSIGNED |
| MESSAGE_TEXT | VARCHAR(128) |
| 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.
Common condition properties
The most common ones have a value for all builtin 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 SQLSTATE Codes. 'HY000' is called the "general error", because MariaDB uses it for all errors which don't have a specific SQLSTATE. For custom conditions generated by the user, the recommended SQLSTATE is '45000'. '00000' indicates success, but it cannot be explicitly set or read via SQL The API function to read it is mysql_sqlstate().
MYSQL_ERRNO is the error code. Each builtin condition has a unique numeric code. 0 indicates success, but it cannot be explicitly set or read via SQL. For a list of builtin 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 builtin 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().
If not specified, MYSQL_ERRNO and MESSAGE_TEXT have default values which 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'.
Special condition properties
There are more condition properties, which are never set for builtin 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 wether 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 wether 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 constaint is located.
How the diagnostics area is populated and cleared
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 statenent'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.
How to access the diagnostics area
The following statements explicitly add conditions to the diagnostics area:
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
