In this chapter, we will discuss the basic techniques that can be used to find problems in the MariaDB server and in SQL statements. The following are some of the basic tools and techniques used:
SQL_ERROR_LOG
pluginBefore discussing the database debugging techniques, it is important to understand the most important tools used by MariaDB that notify us about error conditions, that is, when something goes wrong.
An error in MariaDB consists of the following types of data:
SQLSTATE
valueWhile conditions are usually generated by the server, the user can raise them using the SIGNAL
and RESIGNAL
SQL statements.
To get information about errors, the C API provides three methods: mysql_sqlstate()
, mysql_errno()
, and mysql_error()
. Most MariaDB or MySQL APIs have corresponding methods with almost identical names. These methods and statements will be discussed later in this chapter. Now, let's discuss the MariaDB errors.
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.
For more details, refer to the MariaDB Error Codes page at https://mariadb.com/kb/en/mariadb-error-codes/.
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.
The default error number depends on the SQLSTATE
value: 1642
for warnings, 1643
for not found conditions, and 1644
for errors. These values are dedicated to user-generated errors. In these cases, the default error message informs whether the condition is a user-generated warning or an error. Otherwise, the default error message is an empty string.
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.