MySQL ERROR Log Table Explained

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_log\G
*************************** 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_log\G
*************************** 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.