MySQL 8.0 : Error Logging

MySQL 8.0 : Error Logging

MySQL 8.0 : Error Logging

Here I would like to explain, How MySQL 8.0 server can be configured to log diagnosis messages inside the error log file. 

Generally, the error log contains:
  • A record for mysqld service startup and shutdown.
  • Diagnostic messages such as errors, warnings, and notes during mysqld server startup, running or shut down. 
  • A stack trace if mysqld exits abnormally. 
  • mysqld_safe writes once it finds mysqld exists abnormally and mysqld_safe invoke/start mysqld

Error Log Component Configuration

MySQL 8.0 uses the MySQL error log component architecture that performs log event filtering and writing. These error log components can be configured using the system variable to achieve the desired logging results inside the error log file.

We have mainly two types of error log components: filter & sink.

  • Error Log Filter Components --->  Filter components, mainly process log events, to add, delete or modify event fields or to delete events entirely. 
  • Error Log Sink Components ---> Sink components, mainly log events into log messages in a particular format and then write these messages to its associated output such as into file or the system log. 
We have below error log components based on their category of filter & sink(writer). 
  • Error Log Sink Components
    • log_sink_internal
      • Purpose: Traditional error log message output format. Write inside file_name defined under log_error parameter. 
      • URN: Built-in component/default component
    • log_sink_json
      • Purpose: JSON-format error logging. Write inside the file named as file_name.NN.json
      • URN: file://component_log_sink_json
    • log_sink_syseventlog
      • Purpose: Error logging to the system log. Write to the system log file, regardless of the defined log_error parameter value.
      • URN: file://component_log_sink_syseventlog
    • log_sink_test
      • Purpose: Internal use in writing test cases. Not intended for production use.
      • URN: file://component_log_sink_test
These filter/sink components can be configured using the log_error_services system variable. 

The server executes the filter/sink components in the defined/named order inside the log_error_services parameter. So, we need to make sure the rightmost component should be the sink(writer) component. If we mistakenly place filter component as a rightmost component, then any changes on its event will have no effect on output. 

Below is the default value of log_error_services
mysql [localhost:8015] {msandbox} ((none)) > select @@global.log_error_services;
+----------------------------------------+
| @@global.log_error_services            |
+----------------------------------------+
| log_filter_internal; log_sink_internal |
+----------------------------------------+
1 row in set (0.00 sec) 

If we need to change log_error_services parameter value with new filter/sink component then we first need to load/enable/install that component using the "INSTALL COMPONENT" command(unless the component is a built-in component or already loaded). 

Here I will load some non-default filter/sink component using the "INSTALL COMPONENT" command with their respective URN. 
mysql [localhost:8015] {msandbox} ((none)) > INSTALL COMPONENT "file://component_log_filter_dragnet";
Query OK, 0 rows affected (0.20 sec)


mysql [localhost:8015] {msandbox} ((none)) > INSTALL COMPONENT "file://component_log_sink_json";
Query OK, 0 rows affected (0.01 sec)


mysql [localhost:8015] {msandbox} ((none)) > INSTALL COMPONENT "file://component_log_sink_syseventlog";
Query OK, 0 rows affected (0.01 sec)


We can check the list of manually loaded components from table mysql.component. 

mysql [localhost:8015] {msandbox} ((none)) > select * from mysql.component;
+--------------+--------------------+---------------------------------------+
| component_id | component_group_id | component_urn                         |
+--------------+--------------------+---------------------------------------+
|            1 |                  1 | file://component_log_filter_dragnet   |
|            2 |                  2 | file://component_log_sink_json        |
|            3 |                  3 | file://component_log_sink_syseventlog |
+--------------+--------------------+---------------------------------------+
3 rows in set (0.00 sec)


For example, now suppose we need to use system log writer(log_sink_syseventlog) instead of the default writer (log_sink_internal) then we can just set it dynamically to log_error_services parameters.

Note: above we have already installed the component log_sink_syseventlog.
mysql [localhost:8015] {msandbox} ((none)) > set global log_error_services="log_filter_internal; log_sink_syseventlog";
Query OK, 0 rows affected (0.00 sec)


mysql [localhost:8015] {msandbox} ((none)) > select @@global.log_error_services;
+-------------------------------------------+
| @@global.log_error_services               |
+-------------------------------------------+
| log_filter_internal; log_sink_syseventlog |
+-------------------------------------------+
1 row in set (0.00 sec)


If we need to UNINSTALL COMPONENT then we need to first make sure that COMPONENT is not being used inside the log_error_services parameter. If it is defined then first remove that component from log_error_services and then we can UNINSTALL that component.

Like example:
mysql [localhost:8015] {msandbox} ((none)) > select @@global.log_error_services;
+-------------------------------------------+
| @@global.log_error_services               |
+-------------------------------------------+
| log_filter_internal; log_sink_syseventlog |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:8015] {msandbox} ((none)) > UNINSTALL COMPONENT "file://component_log_sink_syseventlog";
ERROR 3540 (HY000): Unregistration of service implementation 'log_service.log_sink_syseventlog' provided by component 'mysql:log_sink_syseventlog' failed during unloading of the component.

mysql [localhost:8015] {msandbox} ((none)) > set global log_error_services="log_filter_internal; log_sink_internal";
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8015] {msandbox} ((none)) > UNINSTALL COMPONENT "file://component_log_sink_syseventlog";
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8015] {msandbox} ((none)) > select @@global.log_error_services;
+----------------------------------------+
| @@global.log_error_services            |
+----------------------------------------+
| log_filter_internal; log_sink_internal |
+----------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:8015] {msandbox} ((none)) > select * from mysql.component;
+--------------+--------------------+-------------------------------------+
| component_id | component_group_id | component_urn                       |
+--------------+--------------------+-------------------------------------+
|            1 |                  1 | file://component_log_filter_dragnet |
|            2 |                  2 | file://component_log_sink_json      |
+--------------+--------------------+-------------------------------------+
2 rows in set (0.00 sec)

******


Photo by Nathan Dumlao on Unsplash

Comments

  1. Hello,
    In June 2011, you asked a question on the Blogger forum about adding New Post/Older Post buttons to your Contempo Theme blog. I followed the answer and the Newer Post, Older Post buttons are now located in pagination. I would like to also locate these buttons at the bottom of each opened individual post, as I see you have here, so that I can move directly from one post to the next, or the previous. I am, sadly, not a coder, and would appreciate some direction on where I should place similar code to achieve this. And any CSS that would go along with that. I am using the Contempo Theme as well. Assistance greatly appreciated :)

    ReplyDelete

Post a Comment

Popular

SLEEP thread causing "Waiting for table metadata lock"

MySQL Memory Calculator

How to create CentOS VM using Vagrant

errno: 24 - Too many open files

How to properly shutdown MySQL before any maintenance activity

set --server-id to enable either a master or a slave

ERROR 1040 (HY000): Too many connections

GTID Replication (Skip Transaction using empty transaction)

Master has purged binary logs containing GTIDs that the slave requires

How to Prepare for MySQL Certification Exam