A new error culture...the MySQL error log table

Own your Errors... 

From version 8.0.22. the current content of the error log can be accessed via a kind of "online view" with the table "performance_schema.error_log".  

mysql> desc error_log; 
+------------+-----------------------------------------+------+-----+---------+-------+ 
| Field      | Type                                    | Null | Key | Default | Extra | 
+------------+-----------------------------------------+------+-----+---------+-------+ 
| LOGGED     | timestamp(6)                            | NO   | PRI | NULL    |       | 
| THREAD_ID  | bigint unsigned                         | YES  | MUL | NULL    |       | 
| PRIO       | enum('System','Error','Warning','Note') | NO   | MUL | NULL    |       | 
| ERROR_CODE | varchar(10)                             | YES  | MUL | NULL    |       | 
| SUBSYSTEM  | varchar(7)                              | YES  | MUL | NULL    |       | 
| DATA       | text                                    | NO   |     | NULL    |       | 
+------------+-----------------------------------------+------+-----+---------+-------+ 
6 rows in set (0.2861 sec)  

The table contains the same information as the entries in the known ASCII log file.
Convenient evaluations can of course now be carried out via the "SQL interface" in the error log world:  

mysql> select count(*), error_code, prio from error_log group by error_code, prio order by 1,3; 
… 
|        4 | MY-011492  | System  | 
|        4 | MY-011493  | Warning | 
|        4 | MY-011494  | Warning | 
|        4 | MY-011499  | Warning | 
|        4 | MY-011069  | Warning | 
|       11 | MY-011503  | System  | 
|     9669 | MY-013360  | Warning | 
+----------+------------+---------+  

Let's take a look at the most recent three system error messages:  

mysql> select prio, error_code, substr(data, 1,20) from error_log order by logged desc limit 3; 
+---------+------------+----------------------+ 
| prio    | error_code | substr(data, 1,20)   | 
+---------+------------+----------------------+ 
| Warning | MY-013360  | Plugin sha256_passwo | 
| Warning | MY-013360  | Plugin sha256_passwo | 
| Warning | MY-013360  | Plugin sha256_passwo | 
+---------+------------+----------------------+ 
3 rows in set (0.0081 sec)  

Someone still seems to want to log on to this system with an outdated password algorithm (error code "MY-013360). Suppose we are aware of this problem and would like to ignore it, we could use a corresponding system variable for this purpose. The following setting can be used to filter error messages depending on your needs:  

mysql> set global log_error_suppression_list="MY-013360";  

From this moment on, the error "MY-013360" is no longer processed. Of course, further error codes can be taken into account, separated by commas.  

Sink the Bismarck error... 

The internal error process in the current version (8) now consists of a configurable (!) process, consisting of two steps:

1. Error filtering
2. Error writing

What happens within these two steps is determined by the "log_error_services" variable:

mysql> show variables like '%error%serv%'; 
+--------------------+----------------------------------------+ 
| Variable_name      | Value                                  | 
+--------------------+----------------------------------------+ 
| log_error_services | log_filter_internal; log_sink_internal | 
+--------------------+----------------------------------------+ 
1 row in set (0.0033 sec)  

In our example, the "log_filter_internal" plug-in takes care of filtering the error messages based on "log_error_supression_list". "Log_sink_internal" is responsible for storing the error messages in the "error_log". The word "plugin" already indicates that changes are possible here.

For example, the "data sink" can be changed and even expanded. However, before other plugins can be used, they must be loaded.  

mysql> INSTALL COMPONENT 'file://component_log_sink_json‘; 
Query OK, 0 rows affected (0.09 sec) 
 
mysql> INSTALL COMPONENT 'file://component_log_sink_syseventlog'; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> SET PERSIST log_error_services = 'log_filter_internal; log_sink_internal; log_sink_json; log_sink_syseventlog'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> show variables like '%error_serv%'; 
+--------------------+-----------------------------------------------------------------------------+ 
| Variable_name      | Value                                                                       | 
+--------------------+-----------------------------------------------------------------------------+ 
| log_error_services | log_filter_internal; log_sink_internal; log_sink_json; log_sink_syseventlog | 
+--------------------+-----------------------------------------------------------------------------+  

This setting now causes the data to be written in three places:

1. It is stored in the classical error-log,
2. in JSON format and
3. in the system-eventlogot the operating system (you might remember the following from older versions; mysqld_safe —syslog ;-) )

bash ls -la *erro*  
-rw-r----- 1 root root 2092613 1. Apr 14:33 error.log  
-rw-r----- 1 root root 1028 1. Apr 14:33 error.log.00.json 

bash> cat error.log.00.json 
{ "prio" : 2, "err_code" : 13360, "component" : "plugin:sha256_password", "subsystem" : "Server", "source_file" : "sql_authentication.cc", "function" : "sha256_password_authenticate", "msg" : "Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'", "time" : "2021-04-01T12:33:53.517236Z", "ts" : 1617280433517, "thread" : 219556, "err_symbol" : "ER_SERVER_WARN_DEPRECATED", "SQL_state" : "HY000", "label" : "Warning" } 
… 

bash> tail -f /var/log/messages | grep mysqld 
Apr  1 14:33:53 localhost mysqld[5496]: Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead' 
…  

There is also an alternative extension (plug-in) for the "error filter": "log_filter_dragnet"

This is a rule-based filter (e.g., with IF-THEN-ELSE structures) that can be used to map more complex mechanisms to our error codes. The exact functionality including the syntax and some examples can be found at the following URL: https://dev.mysql.com/doc/refman/8.0/en/error-log-rule-based-filtering.html

Lessons learnt? 

It is good to see that apart from the big features (e.g.,InnoDB cluster, MySQL Shell, ...), there are also optimizations for the small details. A "elaborated" error management is certainly not an "error" (!) and helps one or the other DBA to optimize the operating processes.

Do you have any questions about the operation of MySQL databases? Talk to us.