Background

"log_timestamps" is a new parameter that is introduced in MySQL 5.7.2, and mainly used to control display time parameters of MySQL's record logs like error log, slow query log, and genera log, without influencing the display time that is written to the table (mysql.general_log, mysql.slow_log).

Failure phenomenon

When using Baidu AI Cloud RDS product, one customer finds that in the slow query log of January 6, there is a timestamp of January 5, and the example is as follows:

Analysis of cause

Thanks to the location by Baidu Cloud Database engineers, we confirm that the problem results from the parameter "log_timestamps=UTC". "log_timestamps" value's implication is as follows:

• UTC: Use the UTC (Universal Time Coordinated) time, and UTC is the default value in Version 5.7, but the displayed timestamp is eight hours later than Beijing Time.
• SYSTEM: use of local time, local time =UTC+ time zone difference

Solution

Log in to Baidu Cloud Database Console, find the "log_timestamps" parameter on the "Parameter Configuration" page, and modify UTC as SYSTEM.

Case review

1. log_timestamps=UTC: Log in to RDS instance to view the parameter setting and current system time:

Execute test SQL and build a slow query:

select sleep(10);

Find in the slow log that UTC time is 8 hours later than local time:

2. log_timestamps=SYSTEM: Modify the value setting in "Console -parameter configuration"

Log in to the RDS instance to view parameter setting and current system time:

Execute test SQL, and build a slow query:

select sleep(10);

Find in the slow log that that local time is displayed: