mysql-connect-java驱动从5.x升级到8.x的CST时区问题

前言

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

1. demo

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

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.5.4</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
            <exclusions>
                <exclusion>
                    <artifactId>slf4j-api</artifactId>
                    <groupId>org.slf4j</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

随意写一个dao 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 {
                    //规范时区？难道直接读取的不规范😅，这步很重要
                    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));

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"}    😭

    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认为是美国芝加哥的时区了，😖

 

 2.2 时区设置

那么jdbc在哪里设置时间的呢

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

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

时间从此丢失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版本是有深意的，😄MySQL :: MySQL Connector/J 8.0 Release Notes :: Changes in MySQL Connector/J 8.0.23 (2021-01-18, General Availability)

看来官方修复了。😄 

来源码看看，😄，果然，不配置就客户端获取时区了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及以上版本，不配置时区。