mysql solves time zone related problems

Preface:

When using MySQL, you may encounter time zone related issues, such as incorrect time display, time zone not being in the GMT+8 zone, inconsistency between the time obtained by the program and the time stored in the database, and so on. In fact, these problems are all related to the database time zone settings. This article will start with database parameters and gradually introduce time zone related content.

1. Introduction to log_timestamps parameters

First of all, log_timestamps parameter does not affect the time zone, but different settings will affect the time of some log records. This parameter mainly controls the display time in the error log, slow log, and genera log log files, but does not affect the display time of the general log and slow log written to the table (mysql.general_log, mysql.slow_log).

log_timestamps is a global parameter that can be modified dynamically. The UTC time zone is used by default, which will make the time recorded in the log 8 hours slower than Beijing time, making it inconvenient to view the log. It can be changed to SYSTEM to use the system time zone. The following is a simple test of the function and modification method of this parameter:

# View parameter values ​​mysql> show global variables like 'log_timestamps';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| log_timestamps | UTC |
+----------------+-------+
1 row in set (0.00 sec)

# Generate slow logmysql> select sleep(10),now();
+-----------+---------------------+
| sleep(10) | now() |
+-----------+---------------------+
| 0 | 2020-06-24 17:12:40 |
+-----------+---------------------+
1 row in set (10.00 sec)

# The slow log file records the discovery time in UTC time# Time: 2020-06-24T09:12:50.555348Z
# User@Host: root[root] @ localhost [] Id: 10
# Query_time: 10.000354 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1592989960;
select sleep(10),now();

# Modify the parameter value and test againmysql> set global log_timestamps = SYSTEM;
Query OK, 0 rows affected (0.00 sec)

mysql> select sleep(10),now();
+-----------+---------------------+
| sleep(10) | now() |
+-----------+---------------------+
| 0 | 2020-06-24 17:13:44 |
+-----------+---------------------+
1 row in set (10.00 sec)

# The time of the slow log file recording content is correct# Time: 2020-06-24T17:13:54.514413+08:00
# User@Host: root[root] @ localhost [] Id: 10
# Query_time: 10.000214 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1592990024;
select sleep(10),now();

2. Introduction to time_zone parameters

The time_zone parameter is used to set the time zone for each connection session. This parameter is divided into global and session levels and can be modified dynamically. The default value is SYSTEM. In this case, the value of the global parameter system_time_zone is used. The system_time_zone is inherited from the time zone of the current system by default. That is, the MySQL time zone is the same as the system time zone by default.

The time zone setting primarily affects the display and storage of time values ​​that are time zone sensitive. This includes the values ​​displayed by some functions (such as now() and curtime()) and the values ​​stored in the TIMESTAMP type. However, it does not affect the values ​​in the DATE, TIME, and DATETIME columns because these data types are not converted to the time zone when they are accessed. The TIMESTAMP type actually stores UTC time in the database, and different times will be displayed according to the specific time zone when queried.

Next, let's test the impact of changing the time_zone parameter:

# Check the Linux system time and time zone [root@centos ~]# date
Sun Jun 28 14:29:10 CST 2020

# View the current time zone and time of MySQLmysql> show global variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-06-28 14:31:12 |
+---------------------+
1 row in set (0.00 sec)

# Create a test table and insert some data mysql> CREATE TABLE `time_zone_test` (
  -> `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
  -> `dt_col` datetime DEFAULT NULL COMMENT 'datetime time',
  -> `ts_col` timestamp DEFAULT NULL COMMENT 'timestamp time',
  -> PRIMARY KEY (`id`)
  -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='time_zone test table';
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> insert into time_zone_test (dt_col,ts_col) values ​​('2020-06-01 17:30:00','2020-06-01 17:30:00'),(now(),now());
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from time_zone_test;
+----+---------------------+---------------------+
| id | dt_col | ts_col |
+----+---------------------+---------------------+
| 1 | 2020-06-01 17:30:00 | 2020-06-01 17:30:00 |
| 2 | 2020-06-28 14:34:55 | 2020-06-28 14:34:55 |
+----+---------------------+---------------------+

# Change to UTC time zone and reconnect. It is found that the time stored in timestamp will change with the time zone. mysql> set global time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)
mysql> set time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | +00:00 |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-06-28 06:36:16 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from time_zone_test;
+----+---------------------+---------------------+
| id | dt_col | ts_col |
+----+---------------------+---------------------+
| 1 | 2020-06-01 17:30:00 | 2020-06-01 09:30:00 |
| 2 | 2020-06-28 14:34:55 | 2020-06-28 06:34:55 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)

# Change back to the East 8 time zone and restore to normal mysql> set global time_zone='+8:00';
Query OK, 0 rows affected (0.00 sec)

mysql> set time_zone='+8:00';
Query OK, 0 rows affected (0.00 sec)

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

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-06-28 14:39:14 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from time_zone_test;
+----+---------------------+---------------------+
| id | dt_col | ts_col |
+----+---------------------+---------------------+
| 1 | 2020-06-01 17:30:00 | 2020-06-01 17:30:00 |
| 2 | 2020-06-28 14:34:55 | 2020-06-28 14:34:55 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)

If you want it to be permanent, you need to write it into the configuration file. For example, if you want to change the time zone to East 8, you need to add a line to the [mysqld] section of the configuration file: default_time_zone = '+8:00'.

3. Common time zone issues and how to avoid them

Improper time zone settings may cause various problems. Here are some common problems and solutions:

3.1 MySQL internal time is not Beijing time

When you encounter this kind of problem, first check whether the system time and time zone are correct, then check the time_zone of MySQL. It is recommended to change the time_zone to '+8:00'.

3.2 The time accessed by the Java program differs from the time in the database by 8 hours

This problem is most likely caused by the inconsistency between the program time zone and the database time zone. We can check the time zones on both sides. If we want to use Beijing time uniformly, we can add serverTimezone=Asia/Shanghai in the jdbc connection string, and we can also change the time_zone on MySQL to '+8:00'.

3.3 The program time differs from the database time by 13 or 14 hours

If the 8-hour difference isn't surprising enough, the 13-hour difference may leave many people scratching their heads. This problem occurs because JDBC and MySQL do not agree on the "CST" time zone negotiation. Because the CST time zone is a very confusing time zone, it has four meanings:

• Central Standard Time (USA) UTC-05:00 or UTC-06:00
• Central Standard Time (Australia) UTC+09:30
• China Standard Time UTC+08:00
• Cuba Standard Time UTC-04:00
  

In MySQL, if time_zone is the default SYSTEM value, the time zone is inherited as the system time zone CST, which is considered by MySQL internally to be UTC+08:00. JDBC considers CST to be the Central Time of the United States, which results in a difference of 13 hours. If it is winter time, it will be a difference of 14 hours.

The solution to this problem is also very simple. We can explicitly specify the time zone of the MySQL database. Instead of using the misleading CST, we can change time_zone to '+8:00' and add serverTimezone=Asia/Shanghai to the jdbc connection string.

3.4 How to avoid time zone problems

You may have some ideas on how to avoid the above time zone issues. Here are some brief summaries:

1. First, ensure that the system time zone is accurate.
2. The time zone is specified in the jdbc connection string and is consistent with the database time zone.
3. The recommended setting for the time_zone parameter is '+8:00' to avoid using CST which is easily misleading.
4. The time zone parameters of the database instances in each environment remain the same.
   

Some students may have said that the time_zone parameter in our database selects the default SYSTEM value, and there is no inconsistency between the program time and the database time. Do I need to change time_zone to '+8:00' at this time? In this case, it is recommended to change time_zone to '+8:00', especially when the TIMESTAMP field is frequently queried. Because when time_zone=system, querying the timestamp field will call the system time zone for time zone conversion, which is protected by the global lock __libc_lock_lock, which may limit system performance in a thread concurrency environment. Changing it to '+8:00' will not trigger the system time zone conversion, and use MySQL's own conversion, which greatly improves performance.

Summarize:

After reading this article, do you have a deeper understanding of database time zone? I hope this article is helpful to you, especially if you want to learn more about MySQL time zones. If you have encountered other time zone related issues, please leave a message to discuss.

The above is the details of how MySQL solves time zone related problems. For more information about MySQL time zone related issues, please pay attention to other related articles on 123WORDPRESS.COM!