关于mysql:数据库时间慢了14个小时Mybatis说这个锅我不背

239次阅读

共计 10563 个字符，预计需要花费 27 分钟才能阅读完成。

共事反馈一个问题：Mybatis 插入数据库的工夫是昨天的，是不是因为生成 Mybatis 逆向工程生成的代码有问题？

大家都晓得，对于这类 Bug 自己是很感兴趣的。直觉通知我，应该不是 Mybatis 的 Bug，很可能是时区的问题。

很好，明天又能够带大家一起来排查 Bug 了，看看从这次的 Bug 排查中你能 Get 什么技能。

这次钻研的问题有点深奥，但论断很重要。Let’s go!

问题猜测

共事反馈问题的时候，带了本人的猜测：是不是数据库字段设置为 datetime 导致？是不是 Mybatis 逆向工程生成的代码中类型不统一导致的？

共事还要把 datetime 改为 varchar……马上被我禁止了，说：先排查问题，再说解决方案，下午我也抽时间看看。

问题核查

第一步，查看数据库字段类型，是 datetime 的，没问题。

第二步，查看实体类中类型，是 java.util.Date 类型，没问题。

第三步，Bug 复现。

在 Bug 复现这一步，用到了单元测试。话说之前还跟敌人探讨过单元测试的魅力，当初自己是越来越喜爱单元测试了。

我的项目基于 Spring Boot 的，单元测试如下（代码已脱敏）：

@SpringBootTest
class DateTimeTests {

    @Resource
    private UserMapper userMapper;

    @Test
    public void testDate(){User  user = new User();
        // 省略其余字段
        user.setCreateDate(new Date());
        userMapper.insertSelective(user);
    }
}

执行单元测试，查看数据库中插入的数据。Bug 复现，工夫确实是前一天的，与以后工夫相差 14 个小时。

通过下面三步的排查，核实了数据库字段和代码中类型没问题。单元测试也复现了问题，共事没有坑骗我，总要眼见为实，哈哈~

于是根本确定是时区问题。

时区排查

查看服务器工夫

登录测试服务器，执行 date 命令，查看服务器工夫和时区：

[root@xxx ~]# date
2021 年 11 月 25 日 星期四 09:26:25 CST
[root@xxx ~]# date -R
Thu, 25 Nov 2021 09:33:34 +0800

显示工夫是以后工夫，采纳 CST 工夫，最初的 +0800，即东 8 区，没问题。

查看数据库时区

连贯数据库，执行 show 命令：

show variables like '%time_zone%';

+----------------------------+
|Variable         | Value |
+----------------------------+
|system_time_zone    |CST    |
|time_zone          |SYSTEM |

system_time_zone：全局参数，零碎时区，在 MySQL 启动时会查看以后零碎的时区并依据零碎时区设置全局参数 system_time_zone 的值。值为 CST，与零碎工夫的时区统一。

time_zone：全局参数，设置每个连贯会话的时区，默认为 SYSTEM，应用全局参数 system_time_zone 的值。

查看代码中时区

在单元测试的办法内再增加打印时区的代码：

@Test
    public void testDate(){System.out.println(System.getProperty("user.timezone"));
        User  user = new User();
        // 省略其余字段
        user.setCreateDate(new Date());
        userMapper.insertSelective(user);
    }

打印的时区为：

Asia/Shanghai

也就是说 Java 中应用的是 UTC 时区进行业务逻辑解决的，也是东八区的工夫。

那么问题到底出在哪里呢？

问题根本出现

通过上述排查，基本上确定是时区的问题。这里，再补充一下上述相干的时区知识点。

UTC 工夫

UTC 工夫：世界协调工夫（UTC）是世界上不同国家用来调节时钟和工夫的次要工夫规范，也就是零时区的工夫。

UTC, Coordinated Universal Time 是一个规范，而不是一个时区。UTC 是一个全球通用的工夫规范。寰球各地都批准将各自的工夫进行同步协调 (coordinated)，这也是 UTC 名字的起源：Universal Coordinated Time。

CST 工夫

CST 工夫：中央标准工夫。

CST 能够代表如下 4 个不同的时区：

  • Central Standard Time (USA) UT-6:00，美国
  • Central Standard Time (Australia) UT+9:30，澳大利亚
  • China Standard Time UT+8:00，中国
  • Cuba Standard Time UT-4:00，古巴

再次剖析

很显然，这里与 UTC 工夫无关，它只是工夫规范。目前 Mysql 中的 system_time_zone 是 CST，而 CST 能够代表 4 个不同的时区，那么，Mysql 把它当做哪个时区进行解决了呢？

简略推算一下，中国工夫是 UT+8:00，美国是 UT-6:00，当传入中国工夫，间接转换为美国工夫（未思考时区问题），工夫便慢了 14 个小时。

既然晓得了问题，那么解决方案也就有了。

解决方案

针对上述问题可通过数据库层面和代码层面进行解决。

计划一：批改数据库时区

既然是 Mysql 了解错了 CST 指定的时区，那么就将其设置为正确的。

连贯 Mysql 数据库，设置正确的时区：

[root@xxxxx ~]# mysql -uroot -p
mysql> set global time_zone = '+8:00';
mysql> set time_zone = '+8:00'
mysql> flush privileges;

再次执行 show 命令：

show variables like '%time_zone%';

+----------------------------+
|Variable         | Value |
+----------------------------+
|system_time_zone    |CST    |
|time_zone          |+08:00 |

能够看到时区曾经成为东八区的工夫了。再次执行单元测试，问题失去解决。

此种计划也能够间接批改 MySQL 的 my.cnf 文件进行指定时区。

计划二：批改数据库连贯参数

在代码连贯数据库时，通过参数指定所应用的时区。

在配置数据库连贯的 URL 前面增加上指定的时区serverTimezone=Asia/Shanghai：

url: jdbc:mysql://xx.xx.xx.xx:3306/db_name?useUnicode=true&characterEncoding=utf8&autoReconnect=true&serverTimezone=Asia/Shanghai

再次执行单元测试，问题同样能够失去解决。

问题完了？

通过上述剖析与操作，时区的问题曾经解决了。问题就这么完事了吗？为什么是这样呢？

为了验证时区问题，在时区谬误的数据库中，创立了一个字段，该字段类型为 datetime，默认值为 CURRENT_TIMESTAMP。

那么，此时插入一条记录，让 Mysql 主动生成该字段的工夫，你猜该字段的工夫是什么？中国工夫。

神奇不？为什么同样是 CST 时区，零碎主动生成的工夫是正确的，而代码插入的工夫就有时差问题呢？

到底是 Mysql 将 CST 时区了解为美国工夫了，还是 Mybatis、连接池或驱动程序将其了解为美国工夫了？

重头戏开始

为了追究到底是代码中哪里出了问题，先开启 Mybatis 的 debug 日志，看看 insert 时是什么值：

2021-11-25 11:05:28.367 [|1637809527983|] DEBUG 20178 --- [scheduling-1] c.h.s.m.H.listByCondition                : ==> Parameters: 2021-11-25 11:05:27(String), 0(Integer), 1(Integer), 2(Integer), 3(Integer), 4(Integer)

下面是 insert 时的参数，也就是说在 Mybatis 层面工夫是没问题的。排除一个。

那是不是连接池或驱动程序的问题？连接池自身来讲跟数据库连贯的具体操作关系不大，就间接来排查驱动程序。

Mybatis 是 xml 中定义日期字段类型为 TIMESTAMP，扒了一下 mysql-connector-Java-8.0.x 的源码，发现SqlTimestampValueFactory 是用来解决 TIMESTAMP 类型的。

在 SqlTimestampValueFactory 的构造方法上打上断点，执行单元测试：

能够明确的看到，Calendar 将时区设置为 Locale.US，也就是美国工夫，时区为 CST，offset 为 -21600000。-21600000 单位为毫秒，转化为小时，恰好是“-6:00”，这与北京工夫“GMT+08:00”恰好相差 14 个小时。

于是一路往上最终追溯调用链路，该 TimeZone 来自 NativeServerSession 的 serverTimeZone，而 serverTimeZone 的值是由 NativeProtocol 类的 configureTimezone 办法设置的。

public void configureTimezone() {String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone");

        if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {configuredTimeZoneOnServer = this.serverSession.getServerVariable("system_time_zone");
        }

        String canonicalTimezone = getPropertySet().getStringProperty(PropertyKey.serverTimezone).getValue();

        if (configuredTimeZoneOnServer != null) {
            // user can override this with driver properties, so don't detect if that's the case
            if (canonicalTimezone == null || StringUtils.isEmptyOrWhitespaceOnly(canonicalTimezone)) {
                try {canonicalTimezone = TimeUtil.getCanonicalTimezone(configuredTimeZoneOnServer, getExceptionInterceptor());
                } catch (IllegalArgumentException iae) {throw ExceptionFactory.createException(WrongArgumentException.class, iae.getMessage(), getExceptionInterceptor());
                }
            }
        }

        if (canonicalTimezone != null && canonicalTimezone.length() > 0) {
            // 此处设置 TimeZone
            this.serverSession.setServerTimeZone(TimeZone.getTimeZone(canonicalTimezone));

            if (!canonicalTimezone.equalsIgnoreCase("GMT") && this.serverSession.getServerTimeZone().getID().equals("GMT")) {throw ExceptionFactory.createException(WrongArgumentException.class, Messages.getString("Connection.9", new Object[] {canonicalTimezone}),
                        getExceptionInterceptor());
            }
        }

    }

debug 跟踪一下上述代码，显示信息如下：

至此，通过 canonicalTimezone 值的获取，能够看出 URL 前面配置 serverTimezone=Asia/Shanghai 的作用了。其中，下面第一个代码块获取 time_zone 的值，第二个代码块中获取 system_time_zone 的值。这与查询数据库取得的值统一。

因为出问题时并未在 url 中增加参数serverTimezone=Asia/Shanghai，所以走 canonicalTimezone 为 null 的状况。随后逻辑中调用了 TimeUtil.getCanonicalTimezone 办法：

public static String getCanonicalTimezone(String timezoneStr, ExceptionInterceptor exceptionInterceptor) {if (timezoneStr == null) {return null;}

        timezoneStr = timezoneStr.trim();

        // handle '+/-hh:mm' form ...
        if (timezoneStr.length() > 2) {if ((timezoneStr.charAt(0) == '+' || timezoneStr.charAt(0) == '-') && Character.isDigit(timezoneStr.charAt(1))) {return "GMT" + timezoneStr;}
        }

        synchronized (TimeUtil.class) {if (timeZoneMappings == null) {loadTimeZoneMappings(exceptionInterceptor);
            }
        }

        String canonicalTz;
        if ((canonicalTz = timeZoneMappings.getProperty(timezoneStr)) != null) {return canonicalTz;}

        throw ExceptionFactory.createException(InvalidConnectionAttributeException.class,
                Messages.getString("TimeUtil.UnrecognizedTimezoneId", new Object[] {timezoneStr}), exceptionInterceptor);
    }

上述代码中最终走到了 loadTimeZoneMappings(exceptionInterceptor); 办法：

private static void loadTimeZoneMappings(ExceptionInterceptor exceptionInterceptor) {timeZoneMappings = new Properties();
        try {timeZoneMappings.load(TimeUtil.class.getResourceAsStream(TIME_ZONE_MAPPINGS_RESOURCE));
        } catch (IOException e) {throw ExceptionFactory.createException(Messages.getString("TimeUtil.LoadTimeZoneMappingError"), exceptionInterceptor);
        }
        // bridge all Time Zone ids known by Java
        for (String tz : TimeZone.getAvailableIDs()) {if (!timeZoneMappings.containsKey(tz)) {timeZoneMappings.put(tz, tz);
            }
        }
    }

该办法加载了配置文件 ”/com/mysql/cj/util/TimeZoneMapping.properties” 外面的值，通过转换，timeZoneMappings 中，对应 CST 的为 ”CST”。

最终失去 canonicalTimezone 为“CST”，而 TimeZone 取得是通过 TimeZone.getTimeZone(canonicalTimezone)办法取得的。

也就是说 TimeZone.getTimeZone(“CST”)的值为美国工夫。写个单元测试验证一下：

public class TimeZoneTest {

    @Test
    public void testTimeZone(){System.out.println(TimeZone.getTimeZone("CST"));
    }
}

打印后果：

sun.util.calendar.ZoneInfo[id="CST",offset=-21600000,dstSavings=3600000,useDaylight=true,transitions=235,lastRule=java.util.SimpleTimeZone[id=CST,offset=-21600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]]

很显然，该办法传入 CST 之后，默认是美国工夫。

至此，问题起因根本清朗：

  • Mysql 中设置的 server_time_zone 为 CST，time_zone 为 SYSTEM。
  • Mysql 驱动查问到 time_zone 为 SYSTEM，于是应用 server_time_zone 的值，为”CST“。
  • JDK 中 TimeZone.getTimeZone(“CST”)取得的值为美国时区；
  • 以美国时区结构的 Calendar 类；
  • SqlTimestampValueFactory 应用上述 Calendar 来格式化零碎获取的中国工夫，时差问题便呈现了；
  • 最终反映在数据库数据上就是谬误的工夫。

serverVariables 变量

再延长一下，其中 server_time_zone 和 time_zone 都来自于 NativeServerSession 的 serverVariables 变量，该变量在 NativeSession 的 loadServerVariables 办法中进行初始化，要害代码：

if (versionMeetsMinimum(5, 1, 0)) {StringBuilder queryBuf = new StringBuilder(versionComment).append("SELECT");
                queryBuf.append("@@session.auto_increment_increment AS auto_increment_increment");
                queryBuf.append(", @@character_set_client AS character_set_client");
                queryBuf.append(", @@character_set_connection AS character_set_connection");
                queryBuf.append(", @@character_set_results AS character_set_results");
                queryBuf.append(", @@character_set_server AS character_set_server");
                queryBuf.append(", @@collation_server AS collation_server");
                queryBuf.append(", @@collation_connection AS collation_connection");
                queryBuf.append(", @@init_connect AS init_connect");
                queryBuf.append(", @@interactive_timeout AS interactive_timeout");
                if (!versionMeetsMinimum(5, 5, 0)) {queryBuf.append(", @@language AS language");
                }
                queryBuf.append(", @@license AS license");
                queryBuf.append(", @@lower_case_table_names AS lower_case_table_names");
                queryBuf.append(", @@max_allowed_packet AS max_allowed_packet");
                queryBuf.append(", @@net_write_timeout AS net_write_timeout");
                queryBuf.append(", @@performance_schema AS performance_schema");
                if (!versionMeetsMinimum(8, 0, 3)) {queryBuf.append(", @@query_cache_size AS query_cache_size");
                    queryBuf.append(", @@query_cache_type AS query_cache_type");
                }
                queryBuf.append(", @@sql_mode AS sql_mode");
                queryBuf.append(", @@system_time_zone AS system_time_zone");
                queryBuf.append(", @@time_zone AS time_zone");
                if (versionMeetsMinimum(8, 0, 3) || (versionMeetsMinimum(5, 7, 20) && !versionMeetsMinimum(8, 0, 0))) {queryBuf.append(", @@transaction_isolation AS transaction_isolation");
                } else {queryBuf.append(", @@tx_isolation AS transaction_isolation");
                }
                queryBuf.append(", @@wait_timeout AS wait_timeout");

                NativePacketPayload resultPacket = sendCommand(this.commandBuilder.buildComQuery(null, queryBuf.toString()), false, 0);
                Resultset rs = ((NativeProtocol) this.protocol).readAllResults(-1, false, resultPacket, false, null,
                        new ResultsetFactory(Type.FORWARD_ONLY, null));
                Field[] f = rs.getColumnDefinition().getFields();
                if (f.length > 0) {ValueFactory<String> vf = new StringValueFactory(this.propertySet);
                    Row r;
                    if ((r = rs.getRows().next()) != null) {for (int i = 0; i < f.length; i++) {this.protocol.getServerSession().getServerVariables().put(f[i].getColumnLabel(), r.getValue(i, vf));
                        }
                    }
                }

在上述 StringBuilder 的 append 操作中，有 ”@@time_zone AS time_zone” 和 ”@@system_time_zone AS system_time_zone” 两个值，而后查询数据库，从数据库取得值之后，put 到 serverVariables 中。

再来 debug 一下：

能够看出 system_time_zone 的值为 CST。

同样 time_zone 的值为“SYSTEM”。

依据代码中的提醒，拼接与代码一样的 SQL 查问一下数据库：

select @@time_zone;
SYSTEM

值确实是“SYSTEM”。此时，咱们又得出另外一个查问 Mysql 以后时区的办法。

至此，该问题的排查完满收官。大出一口气~~~

小结

在上述问题排查的过程中，屡次用到单元测试，这也是单元测试的魅力所在，用最简略的代码，最轻量的逻辑，最节省时间的形式来验证和追踪谬误。

再回顾一下上述 Bug 排查中用到和学到的知识点：

  • Linux 日期查看，时区查看及衍生如何配置时区；
  • Mysql 时区查看；
  • Spring Boot 单元测试；
  • Java 时区获取；
  • UTC 工夫和 CST 工夫；
  • 两种解决时区问题的计划；
  • 浏览、debug Mysql 驱动源代码；
  • TimeZone.getTimeZone(“CST”)默认时区为美国时区；
  • Mysql 驱动中解决时区问题根本流程逻辑；
  • Mybatis debug 日志相干打印；
  • 其余相干常识。

通过本篇 Bug 查找的文章，你学到了什么？如果有那么一点启发，不要悭吝，给点个赞吧！

博主简介：《SpringBoot 技术底细》技术图书作者，热爱钻研技术，写技术干货文章。

公众号：「程序新视界」，博主的公众号，欢送关注~

技术交换：请分割博主微信号：zhuan2quan

<center>“程序新视界”，一个 100% 技术干货的公众号 </center>

正文完
mysql
发表至： mysql
2021-11-27
 0
关于mysql:数据库表设置外键VS不设置外键哪个更好
关于后端:大白话mysql之深入浅出索引原理-下
关于mysql:HHDBCS便捷功能简介
关于mysql:MySQL-中锁-GETLOCKRELEASELOCK-怎么使用