Documentation Home
MySQL 9.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.0Mb
PDF (A4) - 40.1Mb
Man Pages (TGZ) - 259.0Kb
Man Pages (Zip) - 366.2Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


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 Schemaerror_log table. Granting theSELECT 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.

Theerror_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.

Exampleerror_log contents:

mysql> SELECT * FROM performance_schema.error_log\G*************************** 1. row ***************************    LOGGED: 2020-08-06 09:25:00.338624 THREAD_ID: 0      PRIO: SystemERROR_CODE: MY-010116 SUBSYSTEM: Server      DATA: mysqld (mysqld 9.0.1) starting as process 96344*************************** 2. row ***************************    LOGGED: 2020-08-06 09:25:00.363521 THREAD_ID: 1      PRIO: SystemERROR_CODE: MY-013576 SUBSYSTEM: InnoDB      DATA: InnoDB initialization has started....*************************** 65. row ***************************    LOGGED: 2020-08-06 09:25:02.936146 THREAD_ID: 0      PRIO: WarningERROR_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: SystemERROR_CODE: MY-013292 SUBSYSTEM: Server      DATA: Admin interface ready for connections, address: '127.0.0.1' port: 33062

Theerror_log table has the following columns. As indicated in the descriptions, all but theDATA 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:

    To displayLOGGED 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 thelog_timestamps value isUTC 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 thethread field of error events.

    Within the Performance Schema, theTHREAD_ID column in theerror_log table is most similar to thePROCESSLIST_ID column of thethreads table:

    • For foreground threads,THREAD_ID andPROCESSLIST_ID represent a connection identifier. This is the same value displayed in theID column of theINFORMATION_SCHEMAPROCESSLIST table, displayed in theId column ofSHOW PROCESSLIST output, and returned by theCONNECTION_ID() function within the thread.

    • For background threads,THREAD_ID is 0 andPROCESSLIST_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 islog_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 differentDATA formats.

Theerror_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 theerror_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 theerror_log table and provide a parser for reading previously written error log files.

Thelog_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 affectserror_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.

Theerror_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.

Status VariableMeaning
Error_log_buffered_bytesBytes used in table
Error_log_buffered_eventsEvents present in table
Error_log_expired_eventsEvents discarded from table
Error_log_latest_writeTime of last write to table