The Ultimate Guide to High Availability with MariaDB

SQL_ERROR_LOG Plugin

You are viewing an old version of this article. View the current version here.
MariaDB starting with 5.5.22

This plugin was introduced in MariaDB 5.5.22

Sometimes it's useful to collect errors sent to clients in a log file, that can later be analyzed. The SQL_ERROR_LOG plugin allows to do that. It is implemented as a MYSQL_AUDIT_PLUGIN, so after it is installed the plugin receives error notification from the server and stores them to the specified file. The log file can be rotated.

The typical command to enable the SQL errlog plugin:

install plugin SQL_ERROR_LOG soname 'sql_errlog';

or by adding --plugin-load=sql_errlog.so to the server's command line or to the [mysqld] section in your my.cnf file.

and to disable it:

uninstall plugin SQL_ERROR_LOG;

The SQL_ERROR_LOG plugin declares 5 variables that control it's behavior:

NameTypeDescription
sql_error_log_filenamestringThe name of the logfile. Rotation of it will be named like sql_error_log_filename.001
sql_error_log_rateintegerThe rate of logging. SET sql_error_log_rate=300; means that one of 300 errors will be written to the log.
If sql_error_log_rate is 0 the logging is disabled.
The default rate is 1 (every error is logged).
sql_error_log_size_limitintegerThe limitation for the size of the log file. After reaching the specified limit, the log file is rotated.
1M limit set by default.
sql_error_log_rotationsintegerThe number of rotations. When rotated, the current log file is stored and the new empty one created.
The sql_error_log_rotations logs are stored, older are removed.
The default number of rotations is 9.
sql_error_log_rotatebooleanThis is the 'write-only' variable. Assigning TRUE to this variable forces the log rotation.

Comments are logged too, which makes the statements easier to search. But this is only possible if the client does not strip the comments away. For example, if the queries are entered in the mysql command-line client, it must be started with che --comments option.

Example

install plugin SQL_ERROR_LOG soname 'sql_errlog';
Query OK, 0 rows affected (0.00 sec)

use test;
Database changed

set sql_mode='STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

CREATE TABLE foo2 (id int) ENGINE=WHOOPSIE;
ERROR 1286 (42000): Unknown storage engine 'WHOOPSIE'
\! cat data/sql_errors.log
2013-03-19  9:38:40 msandbox[msandbox] @ localhost [] ERROR 1286: Unknown storage engine 'WHOOPSIE' : CREATE TABLE foo2 (id int) ENGINE=WHOOPSIE

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.