You may have overlooked mysql time zone issues

What background

Recently, I did a small program shopping mall project (click to view, please pay attention to exchange). During the test, I found that some time fields in mysql database are 8 hours later than the current Beijing time, and some are normal. There is no need to think that there must be a time zone problem, but the weird thing is that not all time has a problem… After a thorough investigation, it was found that the root of the problem is a small time zone problem involving more than just the database, there are really many pits here, just to summarize.

Project System configuration

1. Java Springboot framework is used at the back end, and mysql is connected as follows

jdbc:mysql://localhost:3306/test_db? useUnicode=true&characterEncoding=UTF- 8 -&serverTimezone=Asia/Shanghai
Copy the code

Mysql is directly deployed with docker, the startup command is as follows:

docker run --rm --name mysql-test -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest
Copy the code

The mysql time field type is datetime, and the default value is CURRENT_TIMESTAMP.

`add_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time'
Copy the code

Problem analysis

Let’s start by looking at where time zones are involved.

1, mysql time zone

Mysql > select * from time zone;

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.29 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021- 05- 12 11:41:05 |
+---------------------+
1 row in set (0.01 sec)
Copy the code

Time_zone indicates that mysql uses the system time zone. System_time_zone indicates that system uses the UTC time zone. The system time is 8 hours late. Now it is confirmed that there is a problem with the mysql time zone, but it still does not explain why some data times are normal

2. JDBC connection Settings

Let’s look at the JDBC connection string again and see that there is a time zone parameter serverTimezone=Asia/Shanghai.

jdbc:mysql://localhost:3306/test_db? useUnicode=true&characterEncoding=UTF- 8 -&serverTimezone=Asia/Shanghai
Copy the code

3. Analysis conclusions

Now, we can explain why some times are normal and some are not:

Because mysql itself has a time zone problem, so the default value of a field with CURRENT_TIMESTAMP is written through mysql
Since the TIME zone parameter is set for the JDBC connection string, the time to write to the database through the Java program is normal

From this, we can see that the time zone issue relates to mysql itself and its system (in this case docker, the default time zone of the Docker image is UTC), as well as the JDBC code level. Here are three ways to solve the time zone problem

The solution

1. Set the time zone of the Docker system

We first need to make sure that the time zone of the system on which mysql is running (docker in this case) is correct. There are two ways to set the time zone of docker

Method 1: Map native/etc/localtime(This method is to ensure that the docker system time zone is correct) Docker run--rm --name mysql-test -e MYSQL_ROOT_PASSWORD=123456 -v /etc/localtime:/etc/localtime -d mysql:latestMethod 2: Set environment variables-e TZ=Asia/Shanghai
docker run --rm --name mysql-test -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai -d mysql:latest
Copy the code

Change the startup command to check the time zone of mysql

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
Copy the code

Ok, now it’s CST, look at the generation time and it looks like everything is fine, but is it really fine? Search for CST and you’ll see the problem. Central Standard Time (USA) UTC-05:00 / UTC-06:00 2 Central Standard Time (Australia) UTC+ 09:303 China Standard Time UTC+08:00 4 This can cause problems when JDBC attempts to obtain the Time zone. For example, CST may be resolved to UTC-5 or UTC-6. Therefore, it is necessary to set the Time zone parameter for JDBC connection.

2. Set the time zone of mysql

It is highly recommended that you set the mysql time zone to +8:00 (where we are in east 8). There are also two ways to change the mysql time zone. Use 1 as appropriate. A temporary change

set global time_zone = From the '+'; Mysql > change the global time zone to Beijing time, but restart mysql server this setting is still invalidset time_zone = From the '+'; Flush PRIVILEGES; # Effective immediatelyCopy the code

2. Permanently modify the configuration file [Linux :my.cnf, Windows :my.ini]. Add: default-time_zone = ‘+8:00’ to the [mysqld] area and restart the mysql service.

Mysql time zone changed to +08:00

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +08:00 |
+------------------+--------+
2 rows in set (0.11 sec)
Copy the code

3. Set the time zone parameters for the JDBC connection

As you can see from my experience, if JDBC reads the wrong time zone, it will result in writing to the database at the wrong time, regardless of whether mysql is in the right time zone or not, so just passing the first two steps is not a foolproof guarantee. Therefore, be sure to add the time zone parameter serverTimezone=Asia/Shanghai to the JDBC connection string

Finally, a summary of ways to avoid time zone problems:

First, ensure that the system time zone is correct
Add the time zone parameter serverTimezone=Asia/Shanghai to the JDBC connection
You are advised to set the time_zone parameter to +8:00. Do not use an ambiguous CST (even the default _SYSTEM_ value). In addition, try to use datetime type instead of timestamp type. This recommendation is also related to the field type, so I will not elaborate on it here. If you have any questions, please feel free to contact me

Github: github.com/frank-say/b… Gitee:gitee.com/frank-say/b… Click the experience

You may have overlooked mysql time zone issues

What background