Interpretation of new features | MySQL 8.0 error log in-depth filtering (Part 1)

Author: Yang Taotao

Senior database expert, specializing in MySQL research for more than ten years. Good at MySQL, PostgreSQL, MongoDB and other open source databases related to backup and recovery, SQL tuning, monitoring operation and maintenance, high-availability architecture design, etc. Currently working in Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial enterprises.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

--

MySQL 8.0 has a component called component_log_filter_dragnet. Its main function is to customize the filtering and transformation of MySQL's error log content. I mentioned it briefly before, and this time I will explain how to use it in detail.

Before use, install the components:

 INSTALL COMPONENT 'file://component_log_filter_dragnet'; 

After installation, set the system parameter log_error_services to enable it.

 SET global log_error_services = 'log_filter_dragnet; log_sink_internal';

Adjust the filtering rules through the system parameter dragnet.log_error_filter_rules , such as limiting the current of the specified error code, transforming the output, and so on. Similar to MySQL monitoring, there must be key factors such as filter conditions, trigger actions, and final results. The filter condition is similar to the filtering of a single field or a combination of multiple fields in an SQL statement. Such as field <operator> value, NOT EXISTS field, filter condition combination, etc. There are three types of fields: core fields, optional fields, and user-defined fields.

In this article, we will introduce the core fields.

The list of core fields is as follows:

1. time : time, for example, it can be set to not allow writing error logs before December 1, 2022.
2. msg : Error message, since err_code can directly locate msg, it is rarely used to judge, msg can participate in customized content.
3. prio : priority, the corresponding value is ERROR, WARNING, INFORMATION, NOTE or any combination, similar to setting the system parameter log_error_verbosity .
4. err_code / SQL_state : The specific error code, that is, the KEY of the error message.
5. err_symbol : specific error symbol, each error code in MySQL corresponds to an error symbol. The specific err_symbol data can be printed with perror or searched from the official website error reference page: https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
6. subsystem : Specify the filtered subsystem items, such as Server, InnoDB, etc.

There are four trigger actions:

1. drop : delete bad data.
2. throttle : throttle content.
3. set : custom field data.
4. unset : Reset field data.

The error log to be modified in this article is generated based on the following command: replace the entire article with command A.

 [root@ytt-pc ytt]# mysql -utest33333 
ERROR 1045 (28000): Access denied for user 'test33333'@'localhost' (using password: NO)

Corresponding error log code: intercept two err_codes from the result as MY-013360 and MY-010926.

 2022-03-24T06:03:59.511173Z 50 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2022-03-24T06:03:59.511322Z 50 [Note] [MY-010926] [Server] Access denied for user 'test33333'@'localhost' (using password: NO)

Here are some examples of common usage:

1. field time

Similar to filtering table time fields, you can define an equivalent condition or a value range. For example, the error data before 2023-01-01 is not recorded in the log, and it is realized with the field time and action drop:

    ytt-pc:ytt:8.0.28>set global dragnet.log_error_filter_rules='if time <''2023-01-01'' then drop .';
   Query OK, 0 rows affected (0.00 sec)

Exit and execute command A. As long as the time has not reached 2023, no data will be recorded in the error log.

2. Field prio.

For example, you can use the field prio to shield the warning data so that it is not included in the error log. The implementation is as follows:

    ytt-pc:(none):8.0.28>set global dragnet.log_error_filter_rules='if prio==warning then drop .';
   Query OK, 0 rows affected (0.00 sec)

Exit and execute command A, and check the error log when finished: only the Note data is retained in the log, and the warning data is not recorded.

    2022-03-24T06:05:41.037512Z 52 [Note] [MY-010926] [Server] Access denied for user 'test33333'@'localhost' (using password: NO)

3. Field err_code/SQL_state.

err_code is the most direct, as long as the error code is found, it can be filtered according to err_code. For example, the data with the error code MY-010926 is prohibited from being recorded in the log, and err_code=MY-010926 can be used to filter directly. The implementation is as follows:

    ytt-pc:ytt:8.0.28>set global dragnet.log_error_filter_rules='if err_code==MY-010926 then drop .';
   Query OK, 0 rows affected (0.00 sec)

Exit and execute command A, and check the error log: there is no data with error code MY-010926.

    2022-03-24T06:08:47.771611Z 53 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

Suppose you want to customize the error codes and transform them into values that cannot be found on the error reference page of the MySQL official website. You can achieve this with the action set:

    ytt-pc:ytt:8.0.28>set global dragnet.log_error_filter_rules='if err_code==MY-010926 or err_code==MY-013360  then set err_code=1234567890 .';
   Query OK, 0 rows affected (0.00 sec)

Exit and execute command A, and check the error log when finished: replace all relevant error codes with MY-1234567890 .

    2022-03-24T06:12:37.456522Z 55 [Warning] [MY-1234567890] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
   2022-03-24T06:12:37.456676Z 55 [Note] [MY-1234567890] [Server] Access denied for user 'test33333'@'localhost' (using password: NO)

Suppose you want to customize the error data, you can update the value of the field msg during the set action, as follows:

    ytt-pc:ytt:8.0.28>set global dragnet.log_error_filter_rules='if err_code==MY-010926 or err_code==MY-013360  then set msg=''你来看哦，没有了哦！！！'' .';
   Query OK, 0 rows affected (0.00 sec)

Exit and execute command A, and view the error log when finished: the error data has been re-customized.

    2022-03-24T06:16:05.617758Z 56 [Warning] [MY-013360] [Server] 你来看哦，没有了哦！！！
   2022-03-24T06:16:05.617898Z 56 [Note] [MY-010926] [Server] 你来看哦，没有了哦！！！

The above err_code can also be directly replaced with the corresponding SQL_state, with the same effect. For example: sql_state='HY000' (here is a string).

4. Field err_symbol

As I said before, err_symbol is similar to err_code. The err_symbol corresponding to the two error codes printed through perror is as follows: two strings of uppercase characters in parentheses.

    [root@ytt-pc ytt]# perror MY-013360 MY-010926
   MySQL error code MY-013360 (ER_SERVER_WARN_DEPRECATED): '%s' is deprecated and will be removed in a future release. Please use %s instead
   MySQL error code MY-010926 (ER_ACCESS_DENIED_ERROR_WITH_PASSWORD): Access denied for user '%-.48s'@'%-.64s' (using password: %s)

Now let err_symbol implement the msg data customized by err_code just now:

    ytt-pc:ytt:8.0.28>set global dragnet.log_error_filter_rules='if err_symbol==''ER_SERVER_WARN_DEPRECATED''  or err_symbol==''ER_ACCESS_DENIED_ERROR_WITH_PASSWORD'' then set msg=''你来看哦，没有了哦！！''.';
   Query OK, 0 rows affected (0.00 sec)

5. Field subsystem

Assuming that you want to shield server-level errors (the data corresponding to these two error codes in this article are also server-level), the implementation is as follows:

    ytt-pc:(none):8.0.28>set global dragnet.log_error_filter_rules='if subsystem==''Server'' then drop .';
   Query OK, 0 rows affected (0.00 sec)

Exit and execute command A, and check the error log again: the server-level error data is not recorded in the log.

6. Action recovery command: unset

unset can initialize specific fields, such as initializing the msg corresponding to the two error codes, as follows:

    ytt-pc:ytt:8.0.28>set global dragnet.log_error_filter_rules='if err_code==MY-010926 or err_code==MY-013360  then unset msg .';
   Query OK, 0 rows affected (0.00 sec)

Exit and execute command A, and check the error log when finished: the data becomes "No error message, or error message of non-string type. This is almost certainly a bug!"

    2022-03-24T06:24:14.846763Z 59 [ERROR] [MY-013360] [Server] No error message, or error message of non-string type. This is almost certainly a bug!
   2022-03-24T06:24:14.846925Z 59 [ERROR] [MY-010926] [Server] No error message, or error message of non-string type. This is almost certainly a bug!

7. Action current limit command: throttle

Assuming that only two error data can be recorded in the log, the implementation is as follows:

    ytt-pc:ytt:8.0.28>set global dragnet.log_error_filter_rules='if err_code==MY-010926 or err_code==MY-013360 then throttle 2 .';
   Query OK, 0 rows affected (0.00 sec)

No matter how many times command A is executed, only two records of the same error data are recorded in the log.

Assuming that these two error data are limited to 2 records per minute in the log, the implementation is as follows:

 ytt-pc:ytt:8.0.28>set global dragnet.log_error_filter_rules='if err_code==MY-010926 or err_code==MY-013360 then throttle 2/60 .';
   Query OK, 0 rows affected (0.00 sec)

The effect is that the error log records these two pieces of data at the minute level.

8. Add a slightly more complex judgment condition to the condition

Assuming that the msg corresponding to the error code MY-010926 is customized as " OK, that's it !", and the msg corresponding to the error code MY-013360 is customized as " OK, that's it !", the implementation is as follows:

    ytt-pc:ytt:8.0.28>set global dragnet.log_error_filter_rules='if err_code==MY-010926 then set msg=''好的，就这样！'' elseif err_code==MY-013360 then set msg=''不错哦，就这样吧！'' .';
   Query OK, 0 rows affected (0.00 sec)

Exit and execute command A, and check the error log when finished: msg is overwritten by the corresponding custom data.

    2022-03-24T06:32:06.505039Z 60 [Warning] [MY-013360] [Server] 不错哦，就这样吧！
   2022-03-24T06:32:06.505296Z 60 [Note] [MY-010926] [Server] 好的，就这样！

Due to the large amount of content, I divided it into two parts, and this is the end of the first part.