Mysql8.0 jdbc驱动时区问题

Posted on Edited on

背景

我们公司在升级Apollo1.8.1版本之后，发Item表的创建时间和更新时间字段与portal上展示差了13个小时，现象如下：

Item表结构：

排查过程

  1. 刚开始我们想到肯定是数据库的时区与Adminservice服务器时间的时区不一致导致的。

    Adminservice所在的服务器时区是CST中国标准时间:

    Mysql服务器的时区如下：

    发现服务器和mysql服务器的时区是一致的，在加上和运维沟通后他们最近也没有升级服务器相关，应该不是这里的问题。

  2. 排查jpa关于时间戳的转换

    查看关于jpa时间戳的准换还是用了最基本的方式去转换时间戳，不涉及时区的转换，代码如下

    org.hibernate.type.TimestampType

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    public class TimestampType
    		extends AbstractSingleColumnStandardBasicType<Date>
    		implements VersionType<Date>, LiteralType<Date> {
      public static final TimestampType INSTANCE = new TimestampType();

      public TimestampType() {
        super( TimestampTypeDescriptor.INSTANCE, JdbcTimestampTypeDescriptor.INSTANCE );
      }

      @Override
      public String getName() {
        return "timestamp";
      }

      @Override
      public String[] getRegistrationKeys() {
        return new String[] { getName(), Timestamp.class.getName(), java.util.Date.class.getName() };
      }

      @Override
      public Date next(Date current, SharedSessionContractImplementor session) {
        return seed( session );
      }

      @Override
      public Date seed(SharedSessionContractImplementor session) {
        return new Timestamp( System.currentTimeMillis() );
      }

      @Override
      public Comparator<Date> getComparator() {
        return getJavaTypeDescriptor().getComparator();
      }

      @Override
      public String objectToSQLString(Date value, Dialect dialect) throws Exception {
        final Timestamp ts = Timestamp.class.isInstance( value )
            ? ( Timestamp ) value
            : new Timestamp( value.getTime() );
        // TODO : use JDBC date literal escape syntax? -> {d 'date-string'} in yyyy-mm-dd hh:mm:ss[.f...] format
        return StringType.INSTANCE.objectToSQLString( ts.toString(), dialect );
      }

      @Override
      public Date fromStringValue(String xml) throws HibernateException {
        return fromString( xml );
      }
    }

  3. mysql jdbc连接排查

    由于之前的apollo1.5.2版本和1.8.1版本，升级了myql驱动，由之前的5.1.46升级到8.0.16,因此怀疑是不是mysql驱动导致的时区问题。然后看到网上确实还真是这个原因，参考文档https://blog.csdn.net/weixin_41787459/article/details/105790044

    查看驱动关于timezone的源码如下

    com.mysql.cj.jdbc.ConnectionImpl

    1
    2
    3
    4
    5
    private void initializePropsFromServer() throws SQLException {
      //......
      this.session.getProtocol().initServerSession();
      //......
    }

    com.mysql.cj.protocol.a.NativeProtocol

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    public void initServerSession() {
      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) {
        this.serverSession.setServerTimeZone(TimeZone.getTimeZone(canonicalTimezone));

        //
        // The Calendar class has the behavior of mapping unknown timezones to 'GMT' instead of throwing an exception, so we must check for this...
        //
        if (!canonicalTimezone.equalsIgnoreCase("GMT") && this.serverSession.getServerTimeZone().getID().equals("GMT")) {
          throw ExceptionFactory.createException(WrongArgumentException.class, Messages.getString("Connection.9", new Object[] { canonicalTimezone }),
                                                 getExceptionInterceptor());
        }
      }

      this.serverSession.setDefaultTimeZone(this.serverSession.getServerTimeZone());
    }

    追踪代码可知，当 MySQL 的 time_zone 值为 SYSTEM 时，会取 system_time_zone 值作为协调时区。

    重点在这里！若 String configuredTimeZoneOnServer 得到的是 CST 那么 Java 会误以为这是 CST -0500 ，因此 TimeZone.getTimeZone(canonicalTimezone) 会给出错误的时区信息。

    本机默认时区是 Asia/Shanghai +0800 ，误认为服务器时区为 CST -0500 ，实际上服务器是 CST +0800 。

    Timestamp 被转换为会话时区的时间字符串了。问题到此已然明晰：

    JDBC 误认为会话时区在 CST-5
    JBDC 把 Timestamp+0 转为 CST-5 的 String-5
    MySQL 认为会话时区在 CST+8，将 String-5 转为 Timestamp-13
    最终结果相差 13 个小时！如果处在冬令时还会相差 14 个小时。

解决方案

  1. jdbc配置连接指定时区(推荐)

    1
    2
    3
    4
    5
    6
    7
    8
    #原有配置
    #jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8

    #指定为东八区（北京时间）
    jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&serverTimezone=GMT%2B8

    #或指定为上海时间
    #jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai

  2. 修改数据库时区（不推荐使用，可能影响其他程序）

    1
    2
    3
    4
    5
    #查询数据库时区
    show variables like '%time_zone'

    #在my.cnf文件里指定时区，添加下行代码
    default-time_zone = '+8:00