Detailed explanation of the reason why the MySQL 5.7 slow query log time is 8 hours behind the system time

When checking the slow query, I found that the time was incorrect, which was exactly 8 hours different from the system time.

1. The slow query display time is as follows

# Time: 2020-01-10T06:42:24.940811Z

2. System time

$ date
Fri Jan 10 14:42:31 CST 2020

3. View database parameters

mysql> show variables like 'log_timestamps';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| log_timestamps | UTC |
+----------------+-------+
1 row in set (0.00 sec)

As we all know, UTC is the unified universal time, and my current system time is East 8, which is 8 hours ahead of UTC, so it is correct. Check the official documentation for the explanation on the official website.

log_timestamps

Property Value
Command-Line Format --log-timestamps=#
Introduced 5.7.2
System Variable log_timestamps
Scope Global
Dynamic Yes
Type Enumeration
Default Value UTC
Valid Values
UTC

SYSTEM

This variable controls the time zone of timestamps in messages written to the error log, and in general query log and slow query log messages written to files. It does not affect the time zone of general query log and slow query log messages written to tables (mysql.general_log, mysql.slow_log). Rows retrieved from those tables can be converted from the local system time zone to any desired time zone with CONVERT_TZ() or by setting the session time_zone system variable.

Permitted log_timestamps values ​​are UTC (the default) and SYSTEM (local system time zone).

Timestamps are written using ISO 8601 / RFC 3339 format: YYYY-MM-DDThh:mm:ss.uuuuuu plus a tail value of Z signifying Zulu time (UTC) or ±hh:mm (an offset from UTC).

Modifying the parameters can solve the problem.

mysql> SET GLOBAL log_timestamps = SYSTEM;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'log_timestamps';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| log_timestamps | SYSTEM |
+----------------+--------+

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.