Shulou(Shulou.com)06/01 Report--

Foreword:

In the process of using MySQL, you may encounter problems related to the time zone, such as the time display error, the time zone is not East Zone 8, the time obtained by the program is inconsistent with the time stored in the database, and so on. In fact, these problems are related to the database time zone setting. This article will start with the database parameters and introduce the relevant contents of the time zone step by step.

Introduction of 1.log_timestamps parameters

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

Log_timestamps is a global parameter that can be dynamically modified. By default, the UTC time zone is used, which makes the time recorded in the log 8 hours slower than Beijing time, making it inconvenient to view the log. Can be changed to SYSTEM to use the system time zone. The following is a simple test of the function of this parameter and how to modify it:

# 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 log mysql > select sleep (10), now () +-+ | sleep (10) | now () | +-+-+ | 0 | 2020-06-24 17:12:40 | +-+- -+ 1 row in set (10.00 sec) # slow log file content discovery time is UTC time # Time: 2020-06-24T09:12:50.555348Z# User@Host: root [root] @ localhost [] Id: 1 recording Query_time: 10.000354 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1SET timestamp=1592989960 Select sleep (10), now (); # modify parameter values to test again mysql > 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) # slow log file recording content time is correct # Time: 2020-06-24T17:13:54.514413+08:00# User@Host: root [root] @ localhost [] Id: 1 recording Query_time: 10.000214 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1SET timestamp=1592990024 Select sleep (10), now (); introduction of 2.time_zone parameters

The time_zone parameter, which is used to set the time zone for each connection session, is divided into global and session levels and can be modified dynamically. The default value is SYSTEM, which uses the value of the global parameter system_time_zone, while system_time_zone inherits from the current system's time zone by default, that is, the MySQL time zone is the same as the system time zone by default.

The time zone setting mainly affects the display and storage of time zone sensitive time values. Includes the values displayed by some functions (such as now (), curtime ()), as well as the values stored in the TIMESTAMP type, but does not affect the values in the DATE, TIME, and DATETIME columns, because these data types are not time-zone converted when accessed, and the time that the TIMESTAMP type is actually stored in the database is the time of UTC, and the query display will display different times according to the specific time zone.

Let's test the impact of time_zone parameter modification:

# View linux system time and time zone [root@centos ~] # dateSun Jun 28 14:29:10 CST 202 check MySQL current time zone, time mysql > 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 () | +-+ | 0-06-28 14:31:12 | +-+ 1 row in set (0.00 sec) # create test table, Insert some data mysql > CREATE TABLE `time_zone_ test` (- > `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key' -> `ts_ 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 rows affected 3000'), (now (), now ()); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > 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 | +-+ # changed to UTC time zone and reconnected to the time when timestamp storage was found Can change mysql > set global time_zone='+0:00' at any time 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 (sec) mysql > select now () +-+ | now () | +-+ | 0-06-28 06:36:16 | +-+ 1 row in set (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 sec (2020 sec) # change back to East eighth time zone Return 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 (sec) mysql > select now () +-+ | now () | +-+ | 0-06-28 14:39:14 | +-+ 1 row in set (sec) mysql > select * from time_zone_test +-+ | id | dt_col | ts_col | +-+- + | 1 | 0-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 (2020 sec)

If you need to take effect permanently, you also need to write it to the configuration file. For example, if you change the time zone to East Zone 8, you need to add a line to the [mysqld] section of the configuration file: default_time_zone ='+ 8 00'.

3. Common problems in time zone and how to avoid them

Improper time zone setting may give rise to a variety of problems. Here are a few common problems and solutions:

3.1 MySQL internal time is not Beijing time

If you encounter this kind of problem, first check whether the system time and time zone is correct, and then take a look at the time_zone of MySQL. It is recommended that time_zone be changed to'+ 8time_zone'.

3.2 the difference between the access time of the Java program and that in the database is 8 hours

The most likely cause of this problem is 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 uniformly use Beijing time, we can add serverTimezone=Asia/Shanghai to the jdbc connection string, and for MySQL, we can change time_zone to'+ 8MySQL'.

3.3 the difference between program time and database time is 13 or 14 hours.

If the eight-hour difference is not surprising, the 13-hour difference may be confusing to many people. This problem occurs because JDBC and MySQL do not agree on the "CST" time zone. Because the CST time zone is a chaotic time zone, it has four meanings:

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

In MySQL, if time_zone is the default system value, the time zone inherits as the system time zone CST,MySQL internally considers it to be UTC+08:00. Jdbc will think of CST as central American time, which results in a difference of 13 hours, and another 14 hours in winter.

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

3.4 how to avoid time zone problems

You may also have some ways to avoid the above time zone problems. A few points can be summarized as follows:

First of all, ensure the accuracy of the system time zone. The time zone is specified in the jdbc connection string and is consistent with the database time zone. The time_zone parameter is recommended to be set to'+ 8 time_zone 00', without using the easily misunderstood CST. The time zone parameters of each environment database instance remain the same.

Some students may have said that the time_zone parameter in our database chooses the default system value, and there is no inconsistency between the program time and the database time. Do you need to change the time_zone to'+ 8 _ In this case, it is still recommended to change time_zone to'+ 8 libc_lock_lock', especially to query the TIMESTAMP field frequently, because when time_zone=system, querying the timestamp field will call the system's time zone for time zone conversion, which is protected by the global lock _ _ zone, which may result in limited system performance in the thread concurrency environment. Instead of triggering the system time zone conversion by changing to'+ 8 MySQL 00', the performance is greatly improved by using the system itself.

Summary:

After reading this article, do you have a deeper understanding of the database time zone? I hope this article will be helpful to you, especially if you want to know more about MySQL time zone. If you have encountered problems related to other time zones, please feel free to leave a message for discussion.

Cdn.nlark.com/yuque/0/2020/png/119537/1589445155068-6177cbb4-7921-4c23-b040-7f768ec3ae07.png ">

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.