MySQL :: MySQL 8.4 Reference Manual :: 29.12.22.2 The error_log Table (original) (raw)

29.12.22.2 The error_log Table

Of the logs the MySQL server maintains, one is the error log to which it writes diagnostic messages (seeSection 7.4.2, “The Error Log”). Typically, the server writes diagnostics to a file on the server host or to a system log service. 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 theerror_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.4.5) 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 inSection 7.4.2.3, “Error Event Fields”.

• LOGGED
  The event timestamp, with microsecond precision.LOGGED corresponds to thetime field of error events, although with certain potential differences:
  • time values in the error log are displayed according to thelog_timestamps system variable setting; seeEarly-Startup Logging Output Format.
  • The LOGGED column stores values using the TIMESTAMP data type, for which values are stored in UTC but displayed when retrieved in the current session time zone; see Section 13.2.2, “The DATE, DATETIME, and TIMESTAMP Types”.
    To display LOGGED values in the same time zone as displayed in the error log file, first set the session time zone as follows:

SET @@session.time_zone = @@global.log_timestamps;  

If the log_timestamps value is UTC and your system does not have named time zone support installed (seeSection 7.1.15, “MySQL Server Time Zone Support”), set the time zone like this:

SET @@session.time_zone = '+00:00';  

• THREAD_ID
  The MySQL thread ID. THREAD_ID corresponds to the thread field of error events.
  Within the Performance Schema, theTHREAD_ID column in theerror_log table is most similar to the PROCESSLIST_ID column of the threads table:
  • For foreground threads, THREAD_ID and PROCESSLIST_ID represent a connection identifier. This is the same value displayed in the ID column of theINFORMATION_SCHEMA PROCESSLIST table, displayed in the Id column ofSHOW PROCESSLIST output, and returned by theCONNECTION_ID() function within the thread.
  • For background threads, THREAD_ID is 0 and PROCESSLIST_ID isNULL.
    Many Performance Schema tables other thanerror_log has a column namedTHREAD_ID, but in those tables, theTHREAD_ID column is a value assigned internally by the Performance Schema.
• PRIO
  The event priority. Permitted values areSystem, Error,Warning, Note. ThePRIO column is based on thelabel field of error events, which itself is based on the underlying numericprio field value.
• ERROR_CODE
  The numeric event error code.ERROR_CODE corresponds to theerror_code field of error events.
• SUBSYSTEM
  The subsystem in which the event occurred.SUBSYSTEM corresponds to thesubsystem field of error events.
• DATA
  The text representation of the error event. The format of this value depends on the format produced by the log sink component that generates theerror_log row. For example, if the log sink is log_sink_internal orlog_sink_json, DATA values represent error events in traditional or JSON format, respectively. (SeeSection 7.4.2.9, “Error Log Output Format”.)
  Because the error log can be reconfigured to change the log sink component that supplies rows to theerror_log table, and because different sinks produce different output formats, it is possible for rows written to theerror_log table at different times to have different DATA formats.

The error_log table has these indexes:

• Primary key on (LOGGED)
• Index on (THREAD_ID)
• Index on (PRIO)
• Index on (ERROR_CODE)
• Index on (SUBSYSTEM)

TRUNCATE TABLE is not permitted for the error_log table.

Implementation and Configuration of the error_log Table

The Performance Schemaerror_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 theerror_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 theerror_log table. Messages written during shutdown by the previous instance may be useful for diagnosing why shutdown occurred.

Currently, the traditional-formatlog_sink_internal and JSON-formatlog_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. Thelog_error_services value pertains to populating theerror_log table as follows:

• At startup, the server examines thelog_error_services value and chooses from it the leftmost log sink that satisfies these conditions:
  • A sink that supports theerror_log table and provides a parser.
  • If none, a sink that supports theerror_log table but provides no parser.
    If no log sink satisfies those conditions, theerror_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 oflog_error_services changes, the server again examines it, this time looking for the leftmost enabled log sink that supports theerror_log table, regardless of whether it provides a parser.
  If no such log sink exists, no additional error events are written to theerror_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 ongoingerror_log operation.