SQL_ERROR_LOG Plugin
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:
Name | Type | Description |
---|---|---|
sql_error_log_filename | string | The name of the logfile. Rotation of it will be named like sql_error_log_filename.001 |
sql_error_log_rate | integer | The 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_limit | integer | The 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_rotations | integer | The 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_rotate | boolean | This 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