MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

29.12.21.2 The error_log Table

Of the logs the MySQL server maintains, one is the error log to which it writes diagnostic messages (see Section 7.4.2, “The Error Log”). Typically, the server writes diagnostics to a file on the server host or to a system log service. As of MySQL 8.0.22, depending on error log configuration, the server can also write the most recent error events to the Performance Schema error_log table. Granting the SELECT privilege for the error_log table thus gives clients and applications access to error log contents using SQL queries, enabling DBAs to provide access to the log without the need to permit direct file system access on the server host.

The error_log table supports focused queries based on its more structured columns. It also includes the full text of error messages to support more free-form analysis.

The table implementation uses a fixed-size, in-memory ring buffer, with old events automatically discarded as necessary to make room for new ones.

Example error_log contents: 

mysql> SELECT * FROM performance_schema.error_log\G
*************************** 1. row ***************************
    LOGGED: 2020-08-06 09:25:00.338624
 THREAD_ID: 0
      PRIO: System
ERROR_CODE: MY-010116
 SUBSYSTEM: Server
      DATA: mysqld (mysqld 8.0.23) starting as process 96344
*************************** 2. row ***************************
    LOGGED: 2020-08-06 09:25:00.363521
 THREAD_ID: 1
      PRIO: System
ERROR_CODE: MY-013576
 SUBSYSTEM: InnoDB
      DATA: InnoDB initialization has started.
...
*************************** 65. row ***************************
    LOGGED: 2020-08-06 09:25:02.936146
 THREAD_ID: 0
      PRIO: Warning
ERROR_CODE: MY-010068
 SUBSYSTEM: Server
      DATA: CA certificate /var/mysql/sslinfo/cacert.pem is self signed.
...
*************************** 89. row ***************************
    LOGGED: 2020-08-06 09:25:03.112801
 THREAD_ID: 0
      PRIO: System
ERROR_CODE: MY-013292
 SUBSYSTEM: Server
      DATA: Admin interface ready for connections, address: '127.0.0.1' port: 33062

The error_log table has the following columns. As indicated in the descriptions, all but the DATA column correspond to fields of the underlying error event structure, which is described in Section 7.4.2.3, “Error Event Fields”.

The error_log table has these indexes:

TRUNCATE TABLE is not permitted for the error_log table.

Implementation and Configuration of the error_log Table

The Performance Schema error_log table is populated by error log sink components that write to the table in addition to writing formatted error events to the error log. Performance Schema support by log sinks has two parts:

  • A log sink can write new error events to the error_log table as they occur.

  • A log sink can provide a parser for extraction of previously written error messages. This enables a server instance to read messages written to an error log file by the previous instance and store them in the error_log table. Messages written during shutdown by the previous instance may be useful for diagnosing why shutdown occurred.

Currently, the traditional-format log_sink_internal and JSON-format log_sink_json sinks support writing new events to the error_log table and provide a parser for reading previously written error log files.

The log_error_services system variable controls which log components to enable for error logging. Its value is a pipeline of log filter and log sink components to be executed in left-to-right order when error events occur. The log_error_services value pertains to populating the error_log table as follows:

  • At startup, the server examines the log_error_services value and chooses from it the leftmost log sink that satisfies these conditions:

    • A sink that supports the error_log table and provides a parser.

    • If none, a sink that supports the error_log table but provides no parser.

    If no log sink satisfies those conditions, the error_log table remains empty. Otherwise, if the sink provides a parser and log configuration enables a previously written error log file to be found, the server uses the sink parser to read the last part of the file and writes the old events it contains to the table. The sink then writes new error events to the table as they occur.

  • At runtime, if the value of log_error_services changes, the server again examines it, this time looking for the leftmost enabled log sink that supports the error_log table, regardless of whether it provides a parser.

    If no such log sink exists, no additional error events are written to the error_log table. Otherwise, the newly configured sink writes new error events to the table as they occur.

Any configuration that affects output written to the error log affects error_log table contents. This includes settings such as those for verbosity, message suppression, and message filtering. It also applies to information read at startup from a previous log file. For example, messages not written during a previous server instance configured with low verbosity do not become available if the file is read by a current instance configured with higher verbosity.

The error_log table is a view on a fixed-size, in-memory ring buffer, with old events automatically discarded as necessary to make room for new ones. As shown in the following table, several status variables provide information about ongoing error_log operation.

Status Variable Meaning
Error_log_buffered_bytes Bytes used in table
Error_log_buffered_events Events present in table
Error_log_expired_events Events discarded from table
Error_log_latest_write Time of last write to table
MySQL Documentation Library