The SQLSTATE value is an alphanumerical string of five characters. The first two characters represent a class and provide general information about the problem. The last three characters represent a subclass and indicate an exact error or a set of possible errors. If a subclass is not indicated, then the value is 000. All characters are digits or uppercase English letters.

The special value 00000 represents a success. This is the only value in the 00 class, and this value cannot be raised by the user. The 01 class represents a warning that the requested action was performed, but some parts of it have been skipped or if some problem has occurred. The 02 class represents the not found conditions; this is not strictly an error. It is a condition that the user should expect, for example, if a cursor iterates a set of rows, after it reads the last rows, a not found condition will be produced.

Other classes represent errors. If an error occurs, the requested action cannot be completed. For nontransactional engines, this could mean that the actions have been partly executed. For example, if you try to insert two rows, and the second row contains a duplicated value for the primary key.

Some conditions do not have a dedicated SQLSTATE value in the current versions of MariaDB. These conditions use the HY000 value, which is sometimes called the general error. This value is used for many errors inherited from MySQL, and for most MariaDB-specific errors.

The error number, or code, is a SMALLINT SIGNED value (the maximum value is 32767), which only identifies a condition. The value 0 implies success and cannot be used for conditions raised by the user.

A MariaDB version shares the errors of the MySQL version it is based on. MariaDB-specific error codes start from 1900. A MariaDB-specific error is usually an error related to a MariaDB-specific feature such as the virtual columns or the dynamic columns.

The error message is a human readable VARCHAR(128) string. In the simplest of cases, it is just a way to recognize an error without searching its number in the MariaDB documentation. Sometimes, it also contains additional details such as the name of a table or column involved in an operation that failed.

Usually, the error message is enough to understand which error has occurred. However, if an error message is too vague or misleading, if the problem is not immediately clear, the user should refer to the error number and search for it in the MariaDB documentation.

A custom error is an error explicitly generated by the user with the SIGNAL or RESIGNAL statements. The SQLSTATE value, error code, and error message of such errors is defined by the user. The difference between these commands is that SIGNAL creates and raises a new error, while RESIGNAL modifies an error's properties before raising it again.

The main reason to raise a custom error is to provide a better interface to a stored program. For example, if an incorrect parameter is passed to a stored procedure, it is possible to raise an error that clearly states the problem. However, SIGNAL also works out of stored programs. For this reason, it is also possible to use it to write an error in SQL_ERROR_LOG, where the message could have a meaning for a DBA or a tool that will read the log.

The 45000 value is suggested for user-generated custom errors. MariaDB, MySQL, and all other forks cannot use this value even in future versions. However, any SQLSTATE value is safe if the generic 000 subclass is used because such values are not meant to identify a single error. For the same reason, the general error is also acceptable.

There could be valid reasons to use different SQLSTATE values; for example, a custom error in the 01 class is not fatal, and continues the execution. Another reason is mapping a custom error to a built-in error; for example, a custom error can be raised in a particular case when a duplicate key error occurs. A custom error is created to provide the DBA or the applications with useful information on how to debug the problem. However, you also probably want the application to take actions that it normally takes when a duplicate key error occurs. So, the custom error can use the same 23000 SQLSTATE value.

Other condition properties exist in SQL standard and are partially supported in MariaDB. Such condition properties contain additional information about the cause of errors. They can be set for custom errors via SIGNAL and RESIGNAL, and they can be read via GET DIAGNOSTICS. However, these properties are not set for built-in conditions and are never returned to the client; so, developers normally ignore them.

MariaDB errors and warnings, collectively known as conditions, are stored in a container called the diagnostics area. Generally, the diagnostics area contains the warnings and errors generated by the last executed statement. However, the exact mechanism that determines how the diagnostics area is populated or emptied is a bit more complex and will be explained in the next section. MariaDB provides some SQL statements that allow us to inspect the contents of the diagnostics area.

The SHOW WARNINGS statement returns all the conditions that are currently populating the diagnostics area. The SHOW COUNT(*) WARNING statement returns the number or count of such conditions. This number is also assigned to the warning_count session variable.

In the following example, we will execute a query that generates two warnings:

MariaDB [(none)]> CREATE TABLE test.t1 (col INT) ENGINE = xxx;
Query OK, 0 rows affected, 2 warnings (0.29 sec)
MariaDB [(none)]> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1286 | Unknown storage engine 'xxx'               |
| Warning | 1266 | Using storage engine InnoDB for table 't1' |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)

The output contains three columns. You can see the error codes, the error messages, and a level. The level indicates the condition type: it can be a note (just an informative message from the server), a warning, or an error. Notes can be excluded from the output of SHOW WARNINGS by setting the @@sql_note variable to 0.

Some SQL clauses and system variables can change the type of some conditions. For example, the IF EXISTS and IF NOT EXISTS options can be added to several DDL statements such as CREATE TABLE and DROP TABLE. They turn an error into a note, in case an object cannot be created because it already exists, or the object cannot be dropped because it does not exist.

The following example shows how the Level column helps to indicate the relevance of a problem:

MariaDB [(none)]> DROP TABLE test.t;
ERROR 1051 (42S02): Unknown table 'test.t'
MariaDB [(none)]> SHOW WARNINGS G
*************************** 1. row ***************************
  Level: Error
   Code: 1051
Message: Unknown table 'test.t'
1 row in set (0.00 sec)
MariaDB [(none)]> DROP TABLE IF EXISTS test.t;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> SHOW WARNINGS G
*************************** 1. row ***************************
  Level: Note
   Code: 1051
Message: Unknown table 'test.t'
1 row in set (0.00 sec)

In the preceding example, we executed two very similar statements. In the first case, DROP TABLE generates an error because we are trying to erase a table that does not exist. In the second case, we added an IF EXISTS option to DROP TABLE, which means that we do not want MariaDB to generate an error if the table is not present. However, a note is still generated because you may want to know that no such table exists. This example shows the importance of the Level column. The SHOW WARNINGS statement returns a very similar output in these cases, with the level note being the only difference.

As explained previously, the DBA can decide whether warnings are written to the error log or not. For this reason, using SQL clauses that change the error behaviors (such as IF EXISTS and IF NOT EXISTS and IGNORE for the DML statements) or changing the value of the SQL_MODE system variable can help to log more possible problems. They can also help to keep the log files smaller if you think that some kinds of problems are not relevant in your case.

The SHOW ERRORS and SHOW COUNT(*) ERRORS statements are very similar to the statements explained in the previous section, but they only show and count the errors, not warnings or notes. The number of errors in the diagnostics area is also assigned to the error_count session variable.

The following example shows the usage of SHOW COUNT(*) WARNINGS and SHOW COUNT(*) ERRORS:

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [(none)]> SHOW COUNT(*) ERRORS G
*************************** 1. row ***************************
@@session.error_count: 0
1 row in set (0.00 sec)
MariaDB [(none)]> SHOW COUNT(*) WARNINGS G
*************************** 1. row ***************************
@@session.warning_count: 1
1 row in set (0.00 sec)

In the preceding example, trying to create a database with the IF NOT EXISTS clause generates a note. The number of errors is therefore 0, while SHOW COUNT(*) WARNINGS returns 1 because the sql_notes variable is ON by default.