While the error log stores the SQL errors, it may be useful to keep such errors in a separate log, which will then be used to debug the applications. This can be done via SQL_ERROR_LOG, a plugin introduced in MariaDB 5.5.

The SQL_ERROR_LOG plugin is not installed by default. To install this, you can run the following SQL statement:

MariaDB [(none)]> INSTALL SONAME 'sql_errlog';
Query OK, 0 rows affected (0.04 sec)

MySQL users will probably notice that we used a MariaDB extension to the INSTALL statement. This syntax allows installing all plugins contained in the sql_errlog library, not only a specific plugin. In this case, the library contains only one plugin, so the only advantage is that the command is shorter. However, there is another difference: we did not include the file extension (.so or .dll), because it is optional in MariaDB. This makes the command independent from the system.

If SQL_ERROR_LOG is installed, some server system variables will be available. They can be used to control the behavior of this log. Let's check whether such variables exist using the following code snippet:

MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_error_log%';
+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| sql_error_log_filename   | sql_errors.log |
| sql_error_log_rate       | 1              |
| sql_error_log_rotate     | OFF            |
| sql_error_log_rotations  | 9              |
| sql_error_log_size_limit | 1000000        |
+--------------------------+----------------+
5 rows in set (0.01 sec)

If you uninstall the plugin, these variables will disappear.

Let's describe these variables so that you can properly use the plugin:

Rotation happens when the value in sql_error_log_size_limit is reached, or the user sets sql_error_log_rotate. Old files have the same name as the log file, plus a numeric extension. Let's see a file list, shown as follows:

MariaDB [(none)]> ! ls /usr/local/mysql/data | grep errors
sql_errors.log
sql_errors.log.1
sql_errors.log.2
sql_errors.log.3
sql_errors.log.4
sql_errors.log.5
sql_errors.log.6
sql_errors.log.7
sql_errors.log.8
sql_errors.log.9

Forcing the rotation can be useful to debug a script or a stored program. This avoids us having to read a large file to find new errors.

Comments are logged with the statements they belong to. However, this is not possible if the client eliminates the comments from statements before sending them to the server to optimize network traffic. The mysql command-line client strips them, unless the --comments option is specified. Logging comments is useful to make statements easier to search. For example, suppose you are not sure whether a given statement produces an error in some situations. You can add a unique ID to this statement, shown as follows:

CALL test.p(5, 0) /* test01 */ ;

Then, we will be able to search for the lines that contain the following comment:

cat /usr/local/mysql/data/sql_errors.log | grep test01

The SQL error log format has been designed to be read by humans, not programs. Here is an example of a typical record:

2014-01-09 17:31:07 root[root] @ localhost [] ERROR 1062: Duplicate entry '1' for key 'a' : INSERT INTO t VALUES (1, 1)

In the preceding record, there is a human-readable date and time value, the account that issued the query, the error that occurred, and the statement as it was received. The new line characters and spaces are preserved.