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.
We can check the list of manually loaded components from table mysql.component.
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 Filter Components
- log_filter_internal
- Purpose: Filtering based on log event priority and error code which works in combination with log_error_verbosity and log_error_suppression_list system variables. This is also called "Priority-Based Error Log Filtering".
- URN: Built-in component/default component
- log_filter_dragnet
- Purpose: Filtering based on the rules defined by the dragnet.log_error_filter_rules system variable setting. This is also called "Rule-Based Error Log Filtering".
- URN:
file://component_log_filter_dragnet
- 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.
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:
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)
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
Hello,
ReplyDeleteIn 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 :)