Mysql log error verbosity

MySQL error log contains diagnostics messages such as errors, warnings and notes that occur during MySQL startup, shutdown and while the server is running. For example, a InnoDB table is corrupted and need to repaired, This will be recorded in the error log. MySQL 8.0 Error uses the MySQL component architecture for log event filtering and writing. The MySQL system variable log_error_services controls which log components to enable and the rules for filtering the log events. The component table in the mysql system database contains the information about currently loaded comments and shows which components have been registered with INSTALL COMPONENT. To confirm the components installed, you may use the SQL below:

SELECT * FROM mysql.component;

Currently the available log components are in lib/plugins:

• component_log_filter_dragnet.so
• component_log_sink_json.so
• component_log_sink_syseventlog.so
• component_log_sink_test.so

Error Log configuration / system variables

The log_error_services system variable controls which log components to enable for error logging

mysql> select @@log_error_services;
+----------------------------------------+
| @@log_error_services                   |
+----------------------------------------+
| log_filter_internal; log_sink_internal |
+----------------------------------------+
1 row in set (0.00 sec)

The default value indicates that log evens first pass through the built-in filter controller, log_filter_interval and later through the built-in log writer component, log_sink_interval. Typically, a sink processes log events into log messages that have a particular format and writes these messages to its associated output, such as a file or the system log. The combination of  log_filter_internal and log_sink_internal implements the default error log filtering and output behavior.

The output destination of error log can be collected from system variable log_error .  You can configure the destination of error log either to the system log or JSON file.

You can make mysqld to write the error log to system log (Event Log on Windows and syslog on Linux and Unix systems):

INSTALL COMPONENT 'file://component_log_sink_syseventlog';
SET GLOBAL log_error_services = 'log_filter_internal; log_sink_syseventlog';

You can enable JSON writer to record the error log by first loading the writer component and then modifying log_error_services system variable:

INSTALL COMPONENT 'file://component_log_sink_json';
SET GLOBAL log_error_services = 'log_filter_internal; log_sink_json';

traditional error log:

2019-03-10T08:36:59.950769Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.15) starting as process 13222
2019-03-10T08:37:00.253523Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-03-10T08:37:00.267812Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.15'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
2019-03-10T08:37:00.429164Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2019-03-10T08:37:37.635761Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.15)  MySQL Community Server - GPL.
2019-03-10T08:37:37.985380Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.15) starting as process 13410
2019-03-10T08:37:38.277912Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-03-10T08:37:38.291494Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.15'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.

JSON error log:

{ "prio" : 0, "err_code" : 10910, "source_line" : 2191, "source_file" : "mysqld.cc", "function" : "clean_up", "msg" : "/usr/sbin/mysqld: Shutdown complete (mysqld 8.0.15)  MySQL Community Server - GPL.", "time" : "2019-03-10T09:21:18.722864Z", "err_symbol" : "ER_SERVER_SHUTDOWN_COMPLETE", "SQL_state" : "HY000", "subsystem" : "Server", "label" : "System" }

• Filtering MySQL error with log_error_verbosity:
  • Errors only – 1
  • Errors and warnings  – 2
  • Errors, warnings and notes – 3
    • If log_error_verbosity is configured to 2 or higher, the MySQL server even logs the statement that are unsafe for statement-based logging / replication. if the value is 3, the server logs aborted connections and access-denied errors for fresh connection attempts. It is recommended to configure log_error_verbosity with 2 or higher to record detailed information about what is happening to MySQL infrastructure.
• How MySQL 8.0 component based error logging filters are different ? 

We are used to default built-in error log filters that are configured with MySQL system variable log_error_verbosity (default is 2). But, MySQL 8.0 has another component that allows you to filter on rules that you define: log_filter_dragnet. I have explained below step-by-step on how to setup Rule-Based Error Log Filtering using log_filter_dragnet :

INSTALL COMPONENT 'file://component_log_filter_dragnet';
SET GLOBAL log_error_services = 'log_filter_dragnet; log_sink_internal';

To limit information events to no more than one per 60 seconds:

mysql> SET GLOBAL dragnet.log_error_filter_rules =
    -> 'IF prio>=INFORMATION THEN throttle 1/60.';
Query OK, 0 rows affected (0.00 sec)

To throttle  plugin-shutdown messages to only 5 per 5 minutes (300 seconds):

IF err_code == ER_PLUGIN_SHUTTING_DOWN_PLUGIN THEN throttle 1.

To throttle errors and warnings to 1000 per hour and information messages to 100 per hour:

IF prio <= INFORMATION THEN throttle 1000/3600 ELSE throttle 100/3600.

and we can monitor the available dragnet rule:

mysql> select * from global_variables where VARIABLE_NAME like 'dragnet%'G
*************************** 1. row ***************************
 VARIABLE_NAME: dragnet.log_error_filter_rules
VARIABLE_VALUE: IF prio>=INFORMATION THEN throttle 1/60.
1 row in set (0.00 sec)

Conclusion

MySQL 8.0 Error Logging Services are more powerful compared to the versions before and you can now filter error logging much better by creating your own components 

Over the decades we have been reading the MySQL error log from the server system file, if there are any issues in MySQL or any unknown restart happened , generally we look at the mysql error log.

By default MySQL error log can be found in the default path /var/log/mysqld.log , or it can be explicitly configured using the variable log_error.

Few drawbacks using MySQL error log as FILE

• Possibility of missing genuine errors while reading lengthy information.
• Filtering of errors for the particular date and timeframes.
• Cannot provide the DB server access to developers because of fear of mishandling DB servers.

To overcome the above issues , from MySQL 8.0.22 we can access the error-log from the performance_schema.error_log table.

Granting the SELECT privilege for the error_log table will provide the read access to error log contents using a simple SQL queries for dev teams.

For demo purpose i have installed latest MySQL 8.0.23 in our test environment.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec)

Table structure

mysql> show create table performance_schema.error_logG
*************************** 1. row ***************************
       Table: error_log
Create Table: CREATE TABLE `error_log` (
  `LOGGED` timestamp(6) NOT NULL,
  `THREAD_ID` bigint unsigned DEFAULT NULL,
  `PRIO` enum('System','Error','Warning','Note') NOT NULL,
  `ERROR_CODE` varchar(10) DEFAULT NULL,
  `SUBSYSTEM` varchar(7) DEFAULT NULL,
  `DATA` text NOT NULL,
  PRIMARY KEY (`LOGGED`),
  KEY `THREAD_ID` (`THREAD_ID`),
  KEY `PRIO` (`PRIO`),
  KEY `ERROR_CODE` (`ERROR_CODE`),
  KEY `SUBSYSTEM` (`SUBSYSTEM`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Overview to the columns

LOGGED

LOGGED corresponds to the time field of error events occurred , the time values which are stored in the error log table are displayed according to the log_timestamps system variable i.e UTC by default.

we can change the timestamp value by changing log_timestamps variable according to our time zones.

THREAD_ID

It is a MySQL thread ID similar to PROCESSLIST_ID , the thread_id is displayed for events occurred by foreground MySQL threads, for background threads the value be 0.

PRIO

The event priority the permitted values are System, Error, Warning, Note.

ERROR_CODE

Displays the MySQL error codes.

SUBSYSTEM

The subsystem in which the event occurred, example SERVER,INNODB.

DATA

The text representation of the error event.

Variables controlling error logging

Below are the major variables controls the error logging , which defines the output of the error log.

mysql> show global variables like 'log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| log_error                  | /var/log/mysqld.log                    |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+

• log_error – This variable defines the path of the MySQL error log.
• log_error_services – This variable controls which log components to enable for error logging ,by default the values are set to log_filter_internal and log_sink_internal.
• log_filter_internal: This value provides error log filtering based on the log event priority and error code, in combination with the log_error_verbosity and log_error_suppression_list system variables.
• log_sink_internal: This value defines the traditional error log output format.
• log_error_suppression_list – This variable helps to filter or ignore the errors, information and warnings which is not worthy or which creates unnecessary noise in the error log.

we can list of one or more comma-separated values indicating the error codes which we need to suppress. Error codes can be specified in symbolic or numeric form.

Example:

Suppose if we insist to filter out the below warning message from error log:

2021-03-26T09:40:40.109075Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

log_error_suppression_list takes effect based on the log_error_verbosity value defined

mysql> set global log_error_suppression_list='MY-010068';
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| log_error                  | /var/log/mysqld.log                    |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list | MY-010068                       |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
4 rows in set (0.00 sec)

To persist the value in CNF

[mysqld] 
log_error_verbosity=2 # error and warning messages only #
log_error_suppression_list='MY-010068'

log_error_verbosity

This variables specifies the verbosity of events which will be logged in the error log , permitted values from 1 to 3. default is 2.

log_error_verbosity Value	Permitted Message Priorities
1	ERROR
2	ERROR, WARNING
3	ERROR, WARNING, INFORMATION

Now let us query the performance_schema.error_log table

mysql> SELECT * FROM performance_schema.error_logG
*************************** 1. row ***************************
    LOGGED: 2021-01-18 09:56:33.800985
 THREAD_ID: 0
      PRIO: System
ERROR_CODE: MY-013169
 SUBSYSTEM: Server
      DATA: /usr/sbin/mysqld (mysqld 8.0.23) initializing of server in progress as process 6335
*************************** 2. row ***************************
    LOGGED: 2021-01-18 09:56:33.818823
 THREAD_ID: 1
      PRIO: System
ERROR_CODE: MY-013576
 SUBSYSTEM: InnoDB
      DATA: InnoDB initialization has started.
*************************** 3. row ***************************
    LOGGED: 2021-01-18 09:56:40.454929
 THREAD_ID: 0
      PRIO: System
ERROR_CODE: MY-011323
 SUBSYSTEM: Server
      DATA: X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
*************************** 4. row ***************************
    LOGGED: 2021-02-15 06:00:28.384059
 THREAD_ID: 0
      PRIO: System
ERROR_CODE: MY-013172
 SUBSYSTEM: Server
      DATA: Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.23).
*************************** 5. row ***************************
    LOGGED: 2021-02-15 06:00:29.583157
 THREAD_ID: 0
      PRIO: System
ERROR_CODE: MY-010910
 SUBSYSTEM: Server
      DATA: /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23)  MySQL Community Server - GPL.
*************************** 6. row ***************************
    LOGGED: 2021-03-25 06:48:20.350948
 THREAD_ID: 376
      PRIO: Error
ERROR_CODE: MY-012640
 SUBSYSTEM: InnoDB
      DATA: Error number 28 means 'No space left on device'
*************************** 7. row ***************************
    LOGGED: 2021-03-25 06:48:20.416039
 THREAD_ID: 376
      PRIO: Warning
ERROR_CODE: MY-012145
 SUBSYSTEM: InnoDB
      DATA: Error while writing 4194304 zeroes to ./sbtest/sbtest5.ibd starting at offset 1107296256
*************************** 8. row ***************************
    LOGGED: 2021-03-25 06:48:20.434689
 THREAD_ID: 376
      PRIO: Error
ERROR_CODE: MY-013132
 SUBSYSTEM: Server
      DATA: The table 'sbtest5' is full!

with simple SQL queries , we can filter the logs based on priority

mysql> SELECT * FROM performance_schema.error_log WHERE PRIO='error'G
*************************** 1. row ***************************
    LOGGED: 2021-03-26 10:12:42.947343
 THREAD_ID: 0
      PRIO: Error
ERROR_CODE: MY-000067
 SUBSYSTEM: Server
      DATA: unknown variable 'innodb_flush_log_at_trx_t=1'.
*************************** 2. row ***************************
    LOGGED: 2021-03-26 10:12:42.947766
 THREAD_ID: 0
      PRIO: Error
ERROR_CODE: MY-010119
 SUBSYSTEM: Server
      DATA: Aborting

The older events from error_log able are automatically purged when there is a need of space for new events.

The error_log table status can be monitored from SHOW STATUS variables.

mysql> show global status like '%error_log%';
+---------------------------+------------------+
| Variable_name             | Value            |
+---------------------------+------------------+
| Error_log_buffered_bytes  | 8368             |
| Error_log_buffered_events | 62               |
| Error_log_expired_events  | 0                |
| Error_log_latest_write    | 1616753905924032 |
+---------------------------+------------------+
4 rows in set (0.00 sec

Limitations of error_log table

• TRUNCATE TABLE is not permitted on error_log table.
• The table cannot be index , each column is already indexed by default.

Finally ,introduction of error_log table in MySQL 8 has made error readability more convenient and easy, now the error log can be accessed from remote clients as well with few simple SQL queries without accessing the physical system file.

MySQL error logs table feature benefits the remote connections using MySQL shell and will benefits a lot in DBaaS like AWS RDS , AZURE MySQL , Google Cloud SQL platforms. Where the error log needs a console and log retention is complex.

Published
March 28, 2021March 30, 2021

Although only available as a release candidate, MySQL 8 is already proving to be a huge leap forward in many regards. Error logging is no exception. The MySQL development team just announced that they have redesigned the error logging subsystem to use a new component architecture.

The redesign will allow the filtering of log events, as well as the routing of error log output to multiple destinations via the enabling of multiple sink components. This will make it possible to send error log events to third-party systems for additional formatting and analysis.

In today’s article, we’ll explore how to employ MySQL 8’s component-based error logging to achieve a variety of logging configurations. Note that all this is specific for MySQL 8 and is not available in earlier versions and also not in MariaDB. However, MariaDB has an option to write the error log to the system “syslog” on systemd-based Linux variants — which includes all common Linux distributions released in the last few years. This option is not available in MySQL.

Component Architecture

MySQL Server now includes a component-based architecture. This will facilitate the management of server capabilities through the installing and un-installing of specific components.

The component-based architecture is more modular in that components only interact with each other through the services they provide. The services provided by components are available to the server as well as to other components.

With respect to the error log, a log component can be a filter or a sink:

• A filter processes log events to add, remove, or modify individual event fields or to delete events entirely.
• A sink is a destination (writer) for log events. Typically, a sink processes log events into log messages that have a particular format and writes these messages to its associated output, such as a file or the system log.

You can view the error log configuration by querying the global_variables table. Error log variables are prefixed with log_error_:

select * from global_variables where VARIABLE_NAME like 'log_error_%';

+---------------------+----------------------------------------+
| VARIABLE_NAME       | VARIABLE_VALUE                         |
+---------------------+----------------------------------------+
| log_error_services  | log_filter_internal; log_sink_internal |
| log_error_verbosity | 2                                      |
+---------------------+----------------------------------------+

The server executes filters and sinks in the log_error_services value in the order they are listed. By default, log events will first pass through log_filter_internal (the built-in filter component), followed by log_sink_internal (the built-in log writer component).

Component Types

Available log components reside in the lib/plugins directory and have an .so extension on Linux (and a .dll extension on Windows):

• component_log_filter_dragnet.so
• component_log_sink_json.so
• component_log_sink_syseventlog.so
• component_log_sink_test.so

To load a component, you refer to its URN. This is file:// plus the component filename without the .so extension. Each component’s respective URN is listed in the next section.

Error Log Filter Components

Error log filter components implement filtering of error log events. If no filter component is enabled, no filtering occurs. Otherwise, any enabled filter component affects log events only for components listed later in the log_error_services variable.

There are two log filter components:

1. The log_filter_internal component: It implements filtering based on the log_error_verbosity system variable setting. This component is built-in so it does not need to be loaded via INSTALL COMPONENT before use. Note that although log_error_verbosity affects the log_filter_internal component,log_error_verbosity has no effect on logging if log_filter_internal is not enabled.
2. The log_filter_dragnet component:It implements filtering based on the rules defined by the dragnet.log_error_filter_rules system variable setting. Its URN is file://component_log_filter_dragnet.

Error Log Sink Components

Error log sink components are writers that implement error log output. If no sink component is enabled, no log output occurs.

Some sink component descriptions refer to the default error log destination. This is the console or a file and is indicated by the fault of the log_error system variable.

There are four log sink components:

1. The log_sink_internal component:It implements traditional error log message output format. This component is built-in so it does not need to be loaded via INSTALL COMPONENT before use. The output is routed to the default error log destination.
2. The log_sink_json component:It implements JSON-format error logging. Its URN is file://component_log_sink_json. The JSON log writer determines its output destination based on the default error log destination, which is given by the log_error system variable.
3. The log_sink_syseventlog component:It implements error logging to the system log. This is the event log on Windows, and syslog on UNIX and UNIX-like systems, such as Linux. Its URN is file://component_log_sink_syseventlog. The output is written to the system log rather than the default error log destination.
4. The log_sink_test component:It’s intended for internal use in writing test cases and is not recommended for production use. Its URN is file://component_log_sink_test. The output destination is the default error log.

Enabling a Log Component

To enable a log component, you would first load it using INSTALL COMPONENT, then list the component in the log_error_services value.

For example, to use the system log writer (log_sink_syseventlog) instead of the default writer (log_sink_internal), we would first load the writer component, then modify the log_error_services value:

INSTALL COMPONENT 'file://component_log_sink_syseventlog';
SET GLOBAL log_error_services = 'log_filter_internal; log_sink_syseventlog';

One of the great things about the new error log architecture is that it’s possible to configure multiple log writers to send output to multiple destinations. For instance, to enable the system log writer in addition to the default writer, set the log_error_services value like so:

SET GLOBAL log_error_services = 'log_filter_internal; log_sink_internal; log_sink_syseventlog';

Here’s another example to load the JSON component writer:

INSTALL COMPONENT 'file://component_log_sink_json';

SET GLOBAL log_error_services = 'log_filter_internal; log_sink_internal; log_sink_json';

mysql> select * from global_variables where VARIABLE_NAME like 'log_error_%';

+---------------------+-------------------------------------------------------+
| VARIABLE_NAME       | VARIABLE_VALUE                                        |
+---------------------+-------------------------------------------------------+
| log_error_services  | log_filter_internal; log_sink_internal; log_sink_json |
| log_error_verbosity | 2                                                     |
+---------------------+-------------------------------------------------------+

A few more things to keep in mind regarding the JSON writer:

• If log_error names a file, the JSON writer bases the output file naming on that file name, plus a numbered .NN.json suffix, with NN starting at 00. For example, if log_error is my_log_file, successive instances of log_sink_json named in the log_error_servicesvalue write to my_log_file.00.json, my_log_file.01.json, and so on.
• If log_error is set to stderr, the JSON writer writes to the console by default. Even if log_json_writer is named multiple times in the log_error_services value, they all write to the console, which is not terribly useful.

Configuring a Log Component to Be Enabled at Server Startup

In most cases, you’ll want your error logging configuration to persist across server restarts. This can be achieved in a couple of ways:

1. Load the component using INSTALL COMPONENT. This registers it in the mysql.component system table so that the server loads the component automatically for subsequent startups.
2. Set the log_error_services value at startup to include the component name. You can set the value in the server my.cnf file.

As an example, suppose that you wanted to configure your MySQL server to use the JSON log writer (log_sink_json) in addition to the built-in log filter and writer (log_filter_internal, log_sink_internal). First, load the JSON writer:

INSTALL COMPONENT 'file://component_log_sink_json';

Then, in the my.cnf file, set log_error_services to take effect at server startup:

[mysqld]
log_error_services='log_filter_internal; log_sink_internal; log_sink_json'

SET PERSIST log_error_services = 'log_filter_internal; log_sink_internal; log_sink_json';

Either will work, but note that values set in my.cnf only take effect at the next restart, while those set using SET PERSIST take effect immediately, as well as for subsequent restarts.

Uninstalling a Log Component

To uninstall a log component, use the UNINSTALL COMPONENT command.

For example, to revert to using only the default writer and unload the system log writer, execute these statements:

SET GLOBAL log_error_services = 'log_filter_internal; log_sink_internal;
UNINSTALL COMPONENT 'file://component_log_sink_syseventlog';

Error Log Message Formats

Each error log component writes messages to its destination using its own individual output format, although other factors, such as system variables, may also influence the content of the messages.

For all log writers, the ID included in error log messages is that of the thread within the mysqld process responsible for writing the message (the connection thread ID). This indicates which part of the server produced the message and is consistent with general query log and slow query log messages, which also include the connection thread ID.

Besides the common ID field, log_sink_internal, log_sink_json, and log_sink_syseventlog each employ their own output format.

Output Format for log_sink_internal

This log writer produces the traditional error log output using this format:

timestamp thread_id [severity] [err_code] [subsystem] message1

Note: The square bracket characters [ … ] are literal characters that appear in the message format and do not indicate that fields are optional, as they would in function parameter notation.

[err_code] and [subsystem] are new fields that were added in MySQL 8.0.4 and 8.0.5, respectively. Hence, they will be absent from logs generated by older servers. The err_code is a string value as it is made up of alphanumeric characters.

Here’s some example output:

2018-03-29T14:22:01.538083Z 0 [Note] [MY-012487] [InnoDB] InnoDB: DDL log recovery : begin

2018-03-29T14:22:01.538083Z 0 [Warning] [MY-010068] [Server] CA certificate /var/mysql/sslinfo/mycert.pem is self signed.

2018-03-29T14:22:01.538083Z 4 [Note] [MY-010051] [Server] Event Scheduler: scheduler thread started with id 4

2018-03-29T14:22:01.538083Z 0 [Note] [MY-010253] [Server] IPv6 is available.

Output Format for log_sink_json

The JSON format log writer produces messages as JSON objects that contain key/value pairs. For example:

{ "prio": 3, "err_code": 10051, "subsystem": "Server",

  "source_file": "event_scheduler.cc", "function": "run",

  "msg": "Event Scheduler: scheduler thread started with id 4",

  "time": "2018-03-22T12:35:47.669397Z", "thread": 4,

  "err_symbol": "ER_SCHEDULER_STARTED", "SQL_state": "HY000",

  "label": "Note" }

Notice that the content is produced as a one-line string, without line breaks.

Output Format for log_sink_syseventlog

The system log writer produces output that conforms to the specific system log format used on the local platform, i.e. the Windows Event Log.

Factors Affecting Error Log Format

As mentioned above, there are a couple of factors that may influence the content of Error Log messages:

• Information available to the log writer: If a log filter component executed prior to the writer component removes a log event attribute for whatever reason, that attribute will no longer be available for writing.
• log_timestamps system variable: The log_timestamps system variable controls the time zone of timestamps in messages written to all logs, including the error log, general query log, and slow query log files. Permitted values include “UTC” (the default) and “SYSTEM” (local system time zone).

Using Rule-Based Error Log Filtering

MySQL 8’s new error log service allows you to use components to filter the events according to your own rules, using the log_filter_dragnet component.

Usually, the error log configuration includes one log filter component and one or more log writer components. As mentioned in Error Log Filter Components section, MySQL includes two log filter components: log_filter_internal and log_filter_dragnet.

Priority vs. Rules Based Filtering

The log_filter_internal component employs priority filtering based on the log_error_verbosityvalue.

Permitted log_error_verbosity values are:

• 1: Log errors only
• 2: Log errors and warnings
• 3: Log errors, warnings, and notes

The log_filter_internal system variable is built in and enabled by default but can be disabled. In the disabled state, changes to log_error_verbosity have no effect.

Important system messages about non-error situations are sent to the error log regardless of the log_error_verbosity value. These messages include startup and shutdown messages, and certain significant changes to settings.

If log_error_verbosity is set to two or greater, the server logs messages about statements that are unsafe for statement-based logging. In general, a statement is considered “safe” if it deterministic, i.e. always produces the same result for the same input, and “unsafe” if it is not. For example, statements containing system functions that may return a different value on slave such as FOUND_ROWS(), GET_LOCK(), IS_FREE_LOCK(), etc., would be considered nondeterministic, and thus, unsafe. Conversely, some nondeterministic functions, including CONNECTION_ID(), CURDATE(), CURRENT_DATE(), etc., are treated as safe for purposes of logging and replication. In addition, statements using results from floating-point math functions — which are hardware-dependent — are always considered unsafe.

If the value is 3, the server logs everything — from serious errors to aborted connections and access-denied errors for new connection attempts.

Filtering rules for the log_filter_dragnet component are defined by setting the dragnet.log_error_filter_rules system variable.

To enable the log_filter_dragnet filter, first load the filter component, then modify the log_error_services value. The following example adds your custom ruleset first in the chain, and the JSON log writer last:

INSTALL COMPONENT 'file://component_log_filter_dragnet'

SET GLOBAL log_error_services = 'log_filter_dragnet; 

log_sink_internal; log_sink_json';

A rule set consists of zero or more rules, where each rule is an IF statement terminated by a period (.) character. If the variable value is empty (zero rules), no filtering occurs.

Here’s a rule set that drops information events, and, for other events, removes the source_linefield:

SET GLOBAL dragnet.log_error_filter_rules =

  'IF prio>=INFORMATION THEN drop. IF EXISTS source_line THEN unset source_line.';

This rule limits information events to no more than one per 60 seconds:

SET GLOBAL dragnet.log_error_filter_rules = 

  'IF prio>=INFORMATION THEN throttle 1/60.';

Like other error-logging system variables, the log_filter_dragnet filter and dragnet.log_error_filter_rules can be enabled at server startup. To make your ruleset persist across server restarts, you should assign dragnet.log_error_filter_rules using SET PERSIST, as opposed to SET GLOBAL, as we did here. Alternatively, you can also add the setting to the server option file. See the «Configuring a Log Component to be Enabled at Server Startup» section above for instructions.

To turn off your custom filtering, you would remove the log_filter_dragnet filter from the set of error logging components, usually by setting a different and/or default filter component (rather than no filter component). For example:

SET GLOBAL log_error_services = 'log_filter_internal; log_sink_internal';

Don’t forget to use SET PERSIST if you want the setting to persist across server restarts!

Finally, uninstall the filter log_filter_dragnet component:

UNINSTALL COMPONENT 'file://component_log_filter_dragnet';

Conclusion

MySQL 8’s new component-based error logging architecture opens up a world of possibilities for customized error logging and routing. In a future blog, we’ll learn how to analyze error log messages using the Monyog MySQL Monitoring Tool.

The error log contains a record of critical errors that occurred during the server’s operation, table corruption, start and stop information.

SQL errors can also be logged in a separate file using the SQL_ERROR_LOG plugin.

Configuring the Error Log Output Destination

MariaDB always writes its error log, but the destination is configurable.

Writing the Error Log to a File

To configure the error log to be written to a file, you can set the log_error system variable. You can configure a specific file name. However, if a specific file name is not configured, then the log will be written to the ${hostname}.err file in the datadir directory by default.

The log_error system variable can be set in a server option group in an option file prior to starting up the server. For example, to write the error log to the default ${hostname}.err file, you could configure the following:

[mariadb]
...
log_error

If you configure a specific file name as the log_error system variable, and if it is not an absolute path, then it will be relative to the datadir directory. For example, if you configured the following, then the error log would be written to mariadb.err in the datadir directory:

[mariadb]
...
log_error=mariadb.err

If it is a relative path, then the log_error is relative to the datadir directory.

However, the log_error system variable can also be an absolute path. For example:

[mariadb]
...
log_error=/var/log/mysql/mariadb.err

Another way to configure the error log file name is to set the log-basename option, which configures MariaDB to use a common prefix for all log files (e.g. general query log, slow query log, error log, binary logs, etc.). The error log file name will be built by adding a .err extension to this prefix. For example, if you configured the following, then the error log would still be written to mariadb.err in the datadir directory:

[mariadb]
...
log-basename=mariadb
log_error

The log-basename cannot be an absolute path. The log file name is relative to the datadir directory.

Writing the Error Log to Stderr on Unix

On Unix, if the log_error system variable is not set, then errors are written to stderr, which usually means that the log messages are output to the terminal that started mysqld.

If the log_error system variable was set in an option file or on the command-line, then it can still be unset by specifying --skip-log-error.

Writing the Error Log to Syslog on Unix

On Unix, the error log can also be redirected to the syslog. How this is done depends on how you start MariaDB.

Syslog with mysqld_safe

If you start MariaDB with mysqld_safe, then the error log can be redirected to the syslog. See mysqld_safe: Configuring MariaDB to Write the Error Log to Syslog for more information.

Syslog with Systemd

If you start MariaDB with systemd, then the error log can also be redirected to the syslog. See Systemd: Configuring MariaDB to Write the Error Log to Syslog for more information.

systemd also has its own logging system called the journal, and some errors may get logged there instead. See Systemd:Systemd Journal for more information.

Writing the Error Log to Console on Windows

On Windows, if the console option is specified, and if the log_error system variable is not used, then errors are written to the console. If both options are specified, then the last option takes precedence.

Writing the Error Log to the Windows Event Viewer

On Windows, error log messages are also written to the Windows Event Viewer. You can find MariaDB’s error log messages by browsing Windows Logs, and then selecting Application or Application Log, depending on the Windows version.

In MariaDB 10.3 and before, you can find MariaDB’s error log messages by searching for the Source MySQL.

In MariaDB 10.4 and later, you can find MariaDB’s error log messages by searching for the Source MariaDB.

Configuring the Error Log Verbosity

The default value of the log_warnings system variable is 2.

The log_warnings system variable can be used to configure the verbosity of the error log. It can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL log_warnings=3;

It can also be set either on the command-line or in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
log_warnings=3

Some of the warnings included in each verbosity level are described below.

The log_warnings system variable only has an effect on some log messages. Some log messages are always written to the error log, regardless of the error log verbosity. For example, most warnings from the InnoDB storage engine are not affected by log_warnings. For a complete list of log messages affected by log_warnings, see the description of the log_warnings system variable.

Verbosity Level 0

If log_warnings is 0, then many optional warnings will not be logged. However, this does not prevent all warnings from being logged, because there are certain core warnings that will always be written to the error log. For example:

• If InnoDB strict mode is disabled, and if DDL is performed on a table that triggers a «Row size too large» error, then InnoDB will log a warning:

[Warning] InnoDB: Cannot add field col25 in table db1.tab because after 
  adding it, the row size is 8477 which is greater than maximum allowed 
  size (8126) for a record on index leaf page.

However, if InnoDB strict mode is enabled, then the same message will be logged as an error.

Verbosity Level 1

Default until MariaDB 10.2.3. If log_warnings is 1, then many types of warnings are logged. Some useful warnings are:

• Replication-related messages:

[Note] Error reading relay log event: slave SQL thread was killed
[Note] Slave SQL thread exiting, replication stopped in log 
  'dbserver-2-bin.000033'   at position 181420; 
  GTID position '0-263316466-368886'
[Note] Slave I/O thread exiting, read up to log 
  'dbserver-2-bin.000034', position 642; 
  GTID position 0-263316466-368887

• Messages related to DNS lookup failures:

[Warning] IP address '192.168.1.193' 
  could not be resolved: Name or service not known

• Messages related to the event scheduler:

[Note] Event Scheduler: Loaded 0 events

• Messages related to unsafe statements for statement-based replication:

[Warning] Unsafe statement written to the binary log using statement format since 
  BINLOG_FORMAT = STATEMENT. The statement is unsafe because 
  it uses a LIMIT clause. This 
  is unsafe because the set of rows included cannot be predicted.

Frequent warnings about unsafe statements for statement-based replication can cause the error log to grow very large. MariaDB will automatically detect frequent duplicate warnings about unsafe statements for statement-based replication. After 10 identical warnings are detected, MariaDB will prevent that same warning from being written to the error log again for the next 5 minutes.

Verbosity Level 2

Default from MariaDB 10.2.4. If log_warnings is 2, then a couple other different kinds of warnings are printed. For example:

• Messages related to access denied errors:

[Warning] Access denied for user 'root'@'localhost' (using password: YES)

• Messages related to connections that are aborted due to errors or timeouts:

[Warning] Aborted connection 35 to db: 'unconnected' user: 
  'user1@host1' host: '192.168.1.40' (Got an error writing communication packets)
[Warning] Aborted connection 36 to db: 'unconnected' user: 
  'user1@host2' host: '192.168.1.230' (Got an error writing communication packets)
[Warning] Aborted connection 38 to db: 'db1' user: 
  'user2' host: '192.168.1.60' (Unknown error) 
[Warning] Aborted connection 51 to db: 'db1' user: 
  'user2' host: '192.168.1.50' (Got an error reading communication packets)
[Warning] Aborted connection 52 to db: 'db1' user: 
  'user3' host: '192.168.1.53' (Got timeout reading communication packets)

• Messages related to table handler errors:

[Warning] Can't find record in 'tab1'.
[Warning] Can't write; duplicate key in table 'tab1'.
[Warning] Lock wait timeout exceeded; try restarting transaction.
[Warning] The number of locks exceeds the lock table size.
[Warning] Update locks cannot be acquired during a READ UNCOMMITTED transaction.

• Messages related to the files used to persist replication state:
  • Either the default master.info file or the file that is configured by the master_info_file option.
  • Either the default relay-log.info file or the file that is configured by the relay_log_info_file system variable.

[Note] Reading Master_info: '/mariadb/data/master.info'  
  Relay_info:'/mariadb/data/relay-log.info'
[Note] Initialized Master_info from '/mariadb/data/master.info'
[Note] Reading of all Master_info entries succeded
[Note] Deleted Master_info file '/mariadb/data/master.info'.
[Note] Deleted Master_info file '/mariadb/data/relay-log.info'.

• Messages about a master’s binary log dump thread:

[Note] Start binlog_dump to slave_server(263316466), pos(, 4)

Verbosity Level 3

If log_warnings is 3, then a couple other different kinds of warnings are printed. For example:

• Messages related to old-style language options:

[Warning] An old style --language value with language specific 
  part detected: /usr/local/mysql/data/
[Warning] Use --lc-messages-dir without language specific part instead.

• Messages related to progress of InnoDB online DDL:

[Note] InnoDB: Online DDL : Start
[Note] InnoDB: Online DDL : Start reading clustered index of the table and 
  create temporary files
[Note] InnoDB: Online DDL : End of reading clustered index of the table and 
  create temporary files
[Note] InnoDB: Online DDL : Start merge-sorting index PRIMARY (1 / 3), 
  estimated cost : 18.0263
[Note] InnoDB: Online DDL : merge-sorting has estimated 33 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 33 runs
[Note] InnoDB: Online DDL : merge-sorting current run 2 estimated 17 runs
[Note] InnoDB: Online DDL : merge-sorting current run 3 estimated 9 runs
[Note] InnoDB: Online DDL : merge-sorting current run 4 estimated 5 runs
[Note] InnoDB: Online DDL : merge-sorting current run 5 estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 6 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index PRIMARY (1 / 3)
[Note] InnoDB: Online DDL : Start building index PRIMARY (1 / 3), 
  estimated cost : 27.0395
[Note] InnoDB: Online DDL : End of building index PRIMARY (1 / 3)
[Note] InnoDB: Online DDL : Completed
[Note] InnoDB: Online DDL : Start merge-sorting index ux1 (2 / 3), 
  estimated cost : 5.7895
[Note] InnoDB: Online DDL : merge-sorting has estimated 2 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index ux1 (2 / 3)
[Note] InnoDB: Online DDL : Start building index ux1 (2 / 3), 
  estimated cost : 8.6842
[Note] InnoDB: Online DDL : End of building index ux1 (2 / 3)
[Note] InnoDB: Online DDL : Completed
[Note] InnoDB: Online DDL : Start merge-sorting index ix1 (3 / 3), 
  estimated cost : 6.1842
[Note] InnoDB: Online DDL : merge-sorting has estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 2 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index ix1 (3 / 3)
[Note] InnoDB: Online DDL : Start building index ix1 (3 / 3), 
  estimated cost : 9.2763
[Note] InnoDB: Online DDL : End of building index ix1 (3 / 3)
[Note] InnoDB: Online DDL : Completed

Verbosity Level 4

If log_warnings is 4, then a couple other different kinds of warnings are printed. For example:

• Messages related to killed connections:

[Warning] Aborted connection 53 to db: 'db1' user: 
  'user2' host: '192.168.1.50' (KILLED)

• Messages related to all closed connections:

[Warning] Aborted connection 56 to db: 'db1' user: 
  'user2' host: '192.168.1.50' (CLOSE_CONNECTION)

• Messages related to released connections, such as when a transaction is committed and completion_type is set to RELEASE:

[Warning] Aborted connection 58 to db: 'db1' user: 
  'user2' host: '192.168.1.50' (RELEASE)

Verbosity Level 9

If log_warnings is 9, then some very verbose warnings are printed. For example:

• Messages about initializing plugins:

[Note] Initializing built-in plugins
[Note] Initializing plugins specified on the command line
[Note] Initializing installed plugins

MySQL’s log_error_verbosity

MariaDB does not support the log_error_verbosity system variable added in MySQL 5.7.

Format

The format consists of the date (yyyy-mm-dd) and time, the thread ID, followed by the type of error (Note, Warning or Error) and the error message, for example:

2016-06-15 16:53:33 139651251140544 [Note] InnoDB: 
  The InnoDB memory heap is disabled

Until MariaDB 10.1.4, the format only consisted of the date (yymmdd) and time, followed by the type of error (Note, Warning or Error) and the error message, for example:

160615 16:53:08 [Note] InnoDB: The InnoDB memory heap is disabled

Rotating the Error Log on Unix and Linux

Unix and Linux distributions offer the logrotate utility, which makes it very easy to rotate log files. See Rotating Logs on Unix and Linux for more information on how to use this utility to rotate the error log.

Error Messages File

Many error messages are ready from an error messages file that contains localized error messages. If the server can’t find this file when it starts up, then you might see errors like the following:

[ERROR] Can't find messagefile '/usr/share/errmsg.sys'

If this error is occurring because the file is in a custom location, then you can configure this location by setting the lc_messages_dir system variable either on the command-line or in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
lc_messages_dir=/usr/share/mysql/

If you want to use a different locale for error messages, then you can also set the lc_messages system variable. For example:

[mariadb]
...
lc_messages_dir=/usr/share/mysql/
lc_messages=en_US

See Setting the Language for Error Messages for more information.