不指定时区会踩坑：MySQL Java 驱动升级遇到的 Bug 分析

前言

旧项目 MySQL Java 升级驱动，本来一切都好好的，但是升级到 8.x 的驱动后，发现入库的时间比实际时间相差 13 个小时，这就很奇怪了，如果相差 8 小时，那么还可以说是时区不对，从驱动源码分析看看。

1. Demo

pom 依赖，构造一个真实案例，这里的 8.0.22 版本。

org.springframework.boot

<artifactId>spring-boot-starter-web

2.5.4

org.mybatis.spring.boot

mybatis-spring-boot-starter

2.2.0

slf4j-api

org.slf4j

mysql

mysql-connector-java

8.0.22

runtime

随意写一个 da o controller main 。

@SpringBootApplication

@MapperScan("com.feng.mysql.rep")

public class MySQLDateMain {

public static void main(String[] args) {

SpringApplication.run(MySQLDateMain.class, args);

}

}

@RestController

public class UserController {

@Autowired

private UserRepository userRepository;

@RequestMapping(value = "/Users/User", method = RequestMethod.POST)

public String addUser(){

UserEntity userEntity = new UserEntity();

userEntity.setAge(12);

userEntity.setName("tom");

userEntity.setCreateDate(new Date(System.currentTimeMillis()));

userEntity.setUpdateDate(new Timestamp(System.currentTimeMillis()));

userRepository.insertUser(userEntity);

return "ok";

}

}

@Mapper

public interface UserRepository {

@Insert("insert into User (name, age, createDate, updateDate) values (#{name}, #{age}, #{createDate}, #{updateDate})")

int insertUser(UserEntity userEntity);

}

数据库设计：

CREATE TABLE `work`.`User` (

`id` int(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,

`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

`age` int NULL DEFAULT NULL,

`createDate` timestamp NULL DEFAULT NULL,

`updateDate` datetime NULL DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 29 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

1.1 验证

系统时间

调用 HTTP 接口 http://localhost:8080/Users/User

可以看到与真实时间相差 13 小时，诡异了，明明数据库时间是正确的， 而且我的系统时间也是正确的，那么我们可以就只能在驱动找原因，因为当我使用。

2. 问题原因分析

2.1 时区获取

上一步，我们看见系统时间，数据库时间都是正确的，那么做文章的推断就是驱动造成的，以 8.0.22 驱动为例。

使用的驱动是  com.mysql.cj.jdbc.Driver 。

当应用启动后，首次发起数据库操作，就会创建 JDBC 的代码，MyBatis 把这事情干了，获取连接，从连接池，笔者使用  HikariDataSource,HikariPool连接池。

在 com.mysql.cj.jdbc.ConnectionImpl 里面会初始化 session 的拦截器，属性Variables、列映射、自动提交信息等等，其中有一行代码初始化时区：

this.session.getProtocol().initServerSession();

com.mysql.cj.protocol.a.NativeProtocol

public void configureTimezone() {
    //获取MySQL server端的时区
    String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone");


    //如果是SYSTEM，则获取系统时区
    if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {
        configuredTimeZoneOnServer = this.serverSession.getServerVariable("system_time_zone");
    }


    //配置文件获取时区serverTimezone配置，即可以手动配置，这是一个解决问题的手段
    String canonicalTimezone = getPropertySet().getStringProperty(PropertyKey.serverTimezone).getValue();


    //未指定时区，且读取到MySQL时区，就
    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 {
                //规范时区？难道直接读取的不规范:sweat_smile:，这步很重要
                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...
        //时区不规范，比如不是GMT，然而ID标识GMT
        if (!canonicalTimezone.equalsIgnoreCase("GMT") && this.serverSession.getServerTimeZone().getID().equals("GMT")) {
            throw ExceptionFactory.createException(WrongArgumentException.class, Messages.getString("Connection.9", new Object[] {
                    canonicalTimezone
                }),
                getExceptionInterceptor());
        }
    }
}

规范时区：

/**
* Returns the 'official' Java timezone name for the given timezone
* 
* @param timezoneStr
*            the 'common' timezone name
* @param exceptionInterceptor
*            exception interceptor
* 
* @return the Java timezone name for the given timezone
*/
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);
}

比如我的数据库时区是CST，拿到了：

这是系统时区，拿到的是 CST，根源是读取了内置的时区值：

然而这个文件没有 CST 时区定义，需要去 JDK 去拿，然后缓存。这就说明一个道理，CST 这个时区定义不明确。

时区就是 CST 了，仅仅是 CST 时区而已，这里并不能说明 CST 有什么问题， 真正的问题是 CST 怎么比东八区少 13 个小时呢？

this.serverSession.setServerTimeZone(TimeZone.getTimeZone(canonicalTimezone));

根源就是这几句代码：

public static TimeZone getTimeZone(String var0) {
    return ZoneInfoFile.getZoneInfo(var0);
}

开始初始化，sun.timezone.ids.oldmapping 这个一般不会设置。

读取 $JAVA_HOME/lib/tzdb.dat，这是一个 JDK 时区存储文件。

其中 PRC 就是中国时区，但是这个文件并未定义 CST。

CST在这里定义的： addOldMapping();

private static void addOldMapping() {
    String[][] var0 = oldMappings;
    int var1 = var0.length;


    for (int var2 = 0; var2 < var1; ++var2) {
        String[] var3 = var0[var2];
        //这里就把CST时区设置为芝加哥时区
        aliases.put(var3[0], var3[1]);
    }


    if (USE_OLDMAPPING) {
        aliases.put("EST", "America/New_York");
        aliases.put("MST", "America/Denver");
        aliases.put("HST", "Pacific/Honolulu");
    } else {
        zones.put("EST", new ZoneInfo("EST", -18000000));
        zones.put("MST", new ZoneInfo("MST", -25200000));
        zones.put("HST", new ZoneInfo("HST", -36000000));
    }
}

oldMappings 是啥呢？

private static String[][] oldMappings = new String[][] {
    {
        "ACT",
        "Australia/Darwin"
    }, {
        "AET",
        "Australia/Sydney"
    }, {
        "AGT",
        "America/Argentina/Buenos_Aires"
    }, {
        "ART",
        "Africa/Cairo"
    }, {
        "AST",
        "America/Anchorage"
    }, {
        "BET",
        "America/Sao_Paulo"
    }, {
        "BST",
        "Asia/Dhaka"
    }, {
        "CAT",
        "Africa/Harare"
    }, {
        "CNT",
        "America/St_Johns"
    }, {
        "CST",
        "America/Chicago"
    }, {
        "CTT",
        "Asia/Shanghai"
    }, {
        "EAT",
        "Africa/Addis_Ababa"
    }, {
        "ECT",
        "Europe/Paris"
    }, {
        "IET",
        "America/Indiana/Indianapolis"
    }, {
        "IST",
        "Asia/Kolkata"
    }, {
        "JST",
        "Asia/Tokyo"
    }, {
        "MIT",
        "Pacific/Apia"
    }, {
        "NET",
        "Asia/Yerevan"
    }, {
        "NST",
        "Pacific/Auckland"
    }, {
        "PLT",
        "Asia/Karachi"
    }, {
        "PNT",
        "America/Phoenix"
    }, {
        "PRT",
        "America/Puerto_Rico"
    }, {
        "PST",
        "America/Los_Angeles"
    }, {
        "SST",
        "Pacific/Guadalcanal"
    }, {
        "VST",
        "Asia/Ho_Chi_Minh"
    }
};

{"CST", "America/Chicago"}    :sob:

private static ZoneInfo getZoneInfo0(String var0) {
    try {
        //缓存获取
        ZoneInfo var1 = (ZoneInfo) zones.get(var0);
        if (var1 != null) {
            return var1;
        } else {
            String var2 = var0;
            if (aliases.containsKey(var0)) {
                var2 = (String) aliases.get(var0);
            }


            int var3 = Arrays.binarySearch(regions, var2);
            if (var3 < 0) {
                return null;
            } else {
                byte[] var4 = ruleArray[indices[var3]];
                DataInputStream var5 = new DataInputStream(new ByteArrayInputStream(var4));
                var1 = getZoneInfo(var5, var2);
                //首次获取，存缓存
                zones.put(var0, var1);
                return var1;
            }
        }
    } catch (Exception var6) {
        throw new RuntimeException("Invalid binary time-zone data: TZDB:" + var0 + ", version: " + versionId, var6);
    }
}

就这样 CST 时区就被 JDK 认为是 美国芝加哥的时区 了，:confounded:

 2.2 时区设置

那么 JDBC 在哪里设置时间的呢?

进一步可以看到在服务器上时区都是 OK 的。

但是，在 com.mysql.cj.ClientPreparedQueryBindings 的 setTimestamp 方法中，获取了 session 时区，然后 format，:sweat_smile:

时间从此丢失 13 小时，原因是 format 的锅，因为 用的美国芝加哥时间格式化 ，如果使用 long 时间的话或者什么都不处理就没有问题。

SimpleDateFormat 设置 CST 时区，前面已经分析了，这个时区就是美国芝加哥时区。

JDK 会认为 CST 是美国芝加哥的时区，UTC-5，但是我们的时间是 UTC+8，换算成 US的时间就是，当前时间 - 8 - 5，即时间少 13 小时。这里不设置时区（即使用客户端时区）即可正常返回时间。

那么 CST 时区是什么呢？笔者写博客的时间 是2021-09-22，是 CST 的夏令时 。

CST 是中部标准时间，现在是 UTC-5，即夏令时，冬季还会变成 UTC-6。

标准的 US 的 CST 时间是 UTC-6，我当前的时间是 23:56。

关键在于 CST 定义非常模糊，而 MySQL 驱动调用 SimpleDateFormat，使用的 CST 为美国芝加哥时区，当前的季节为 UTC-5。

3. 解决办法

根据上面的分析，解决 CST 时区的方法非常多。

• 设置 MySQL Server 的时区为非 CST 时区；

• 设置 MySQL 的系统时区为非 CST 时区； 

• 通过参数增加 serverTimezone设 置为明确的 MySQL 驱动的 properties 定义的时区；

• 修改 MySQL Java 驱动，获取时区通过客户端获取，比如当前运行环境，通过 JDK 获取。

3.1 解决办法详细说明

设置 MySQL Server 的时区

 set global time_zone = '+08:00';

或者修改 MySQL 的配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf。

[mysqld] 节点下增加：

default-time-zone = '+08:00'

设置系统时区

以 Ubuntu 为例：

timedatectl set-timezone Asia/Shanghai

参数增加 serverTimezone

jdbc:mysql://localhost:3306/work?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=Asia/Shanghai

修改MySQL驱动

比如获取时区通过 client 端获取，Date 数据使用什么时区，就使用这个时区 format，但是一般而言我们不会自己发布驱动，跟随 MySQL 官方更新，只有大厂有机会自己运营 MySQL 驱动。

3.2 官方解决方案

笔者在浏览 MySQL 8.0.x 驱动发布的时候在 8.0.23 版本发现了特别的发布记录，笔者在初始时使用 8.0.22 版本是有深意的，:smile:

MySQL :: MySQL Connector/J 8.0 Release Notes :: Changes in MySQL Connector/J 8.0.23 (2021-01-18, General Availability)

看来官方修复了。:smile: 

来源码看看。果然，不配置就客户端获取时区了TimeZone.getDefault();

public void configureTimeZone() {
    //先读配置connectionTimeZone
    String connectionTimeZone = getPropertySet().getStringProperty(PropertyKey.connectionTimeZone).getValue();


    TimeZone selectedTz = null;
    //如果没配参数，或者参数配LOCAL，就取客户端时区
    //配置其他选择，基本上参数决定了时区，不再MySQL server去获取时区了
    if (connectionTimeZone == null || StringUtils.isEmptyOrWhitespaceOnly(connectionTimeZone) || "LOCAL".equals(connectionTimeZone)) {
        selectedTz = TimeZone.getDefault();


    } else if ("SERVER".equals(connectionTimeZone)) {
        // Session time zone will be detected after the first ServerSession.getSessionTimeZone() call.
        return;


    } else {
        selectedTz = TimeZone.getTimeZone(ZoneId.of(connectionTimeZone)); // TODO use ZoneId.of(String zoneId, Map<String, String> aliasMap) for custom abbreviations support
    }


    //设置时区
    this.serverSession.setSessionTimeZone(selectedTz);


    //默认不再强制把时区塞进session 的 Variables中
    if (getPropertySet().getBooleanProperty(PropertyKey.forceConnectionTimeZoneToSession).getValue()) {
        // TODO don't send 'SET SESSION time_zone' if time_zone is already equal to the selectedTz (but it requires time zone detection)


        StringBuilder query = new StringBuilder("SET SESSION time_zone='");


        ZoneId zid = selectedTz.toZoneId().normalized();
        if (zid instanceof ZoneOffset) {
            String offsetStr = ((ZoneOffset) zid).getId().replace("Z", "+00:00");
            query.append(offsetStr);
            this.serverSession.getServerVariables().put("time_zone", offsetStr);
        } else {
            query.append(selectedTz.getID());
            this.serverSession.getServerVariables().put("time_zone", selectedTz.getID());
        }


        query.append("'");
        sendCommand(this.commandBuilder.buildComQuery(null, query.toString()), false, 0);
    }
}

再看看设置参数的地方，这里设计有点改变，通过 QueryBindings 接口抽象了处理逻辑：

public void setTimestamp(int parameterIndex, Timestamp x) throws java.sql.SQLException {
    synchronized(checkClosed().getConnectionMutex()) {
        ((PreparedQuery << ? > ) this.query).getQueryBindings().setTimestamp(getCoreParameterIndex(parameterIndex), x, MysqlType.TIMESTAMP);
    }
}

实现 com.mysql.cj.ClientPreparedQueryBindings：

public void bindTimestamp(int parameterIndex, Timestamp x, Calendar targetCalendar, int fractionalLength, MysqlType targetMysqlType) {
   if (fractionalLength < 0) {
       // default to 6 fractional positions
       fractionalLength = 6;
   }


   x = TimeUtil.adjustNanosPrecision(x, fractionalLength, !this.session.getServerSession().isServerTruncatesFracSecs());


   StringBuffer buf = new StringBuffer();


   if (targetCalendar != null) {
       buf.append(TimeUtil.getSimpleDateFormat("''yyyy-MM-dd HH:mm:ss", targetCalendar).format(x));
   } else {
       this.tsdf = TimeUtil.getSimpleDateFormat(this.tsdf, "''yyyy-MM-dd HH:mm:ss",
           targetMysqlType == MysqlType.TIMESTAMP && this.preserveInstants.getValue() ? this.session.getServerSession().getSessionTimeZone() :
           this.session.getServerSession().getDefaultTimeZone());
       buf.append(this.tsdf.format(x));
   }


   if (this.session.getServerSession().getCapabilities().serverSupportsFracSecs() && x.getNanos() > 0) {
       buf.append('.');
       buf.append(TimeUtil.formatNanos(x.getNanos(), 6));
   }
   buf.append('\'');


   setValue(parameterIndex, buf.toString(), targetMysqlType);
}

时区就是刚刚设置的，亚洲/上海。

总结

一个时区问题，居然里面有这么多玩头，MySQL 居然在 8.0.23 才修复这个，难道 MySQL 认为大家都会配置时区，还是服务器都不使用 CST 时区。 另外如果使用 UTC 时区，是一个精准的时区，表示 0 区时间，就会从一个坑跳另一个坑。 所以， 还是精准用 Asia/Shanghai 吧，或者驱动升级 8.0.23 及 以上版本，不配置时区。