MySQL CST时区协商问题导致的数据库时间戳错误

痛定思痛。 2023-01-19 07:13 4阅读 0赞

### 1. 背景 ###

插入`timestamp`类型与`datetime`类型数据比预计结果早`13小时`

### 2. 原因 ###

如果说相差`8`小时不够让人惊讶，那相差`13`小时可能会让很多人摸不着头脑。出现这个问题的原因是`JDBC`与`MySQL`对 “`CST`” 时区协商不一致。因为`CST`时区是一个很混乱的时区，有四种含义：

*  美国中部时间 `Central Standard Time (USA) UTC-05:00`或`UTC-06:00`
 *  澳大利亚中部时间 `Central Standard Time (Australia) UTC+09:30`
 *  中国标准时 `China Standard Time UTC+08:00`
 *  古巴标准时 `Cuba Standard Time UTC-04:00`

`MySQL`中，如果`time_zone`为默认的`SYSTEM`值，则时区会继承为系统时区`CST`，`MySQL`内部将其认为是`UTC+08:00`。而`jdbc`会将`CST`认为是美国中部时间 `UTC -05:00`，这就导致会相差`13`小时，如果处在冬令时还会相差`14`个小时，`这个时间与美中是(=13)否(=14)采用夏令时息息相关`。

### 3. 排错过程 ###

在项目中，偶然发现数据库中存储的 `Timestamp` 字段的 `unix_timestamp()` 值比真实值少了 `13` 个小时。通过调试追踪，发现了 `com.mysql.cj.jdbc` 里的`时区协商`有问题。

当 `JDBC` 与 `MySQL` 开始建立连接时，会调用 `com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer()` 获取服务器参数，其中我们看到调用 `this.session.configureTimezone()` 函数，它负责配置时区。

public void configureTimezone() {
        String configuredTimeZoneOnServer = getServerVariable("time_zone");
     
        if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {
            configuredTimeZoneOnServer = getServerVariable("system_time_zone");
        }
     
        String canonicalTimezone = getPropertySet().getStringReadableProperty(PropertyDefinitions.PNAME_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.serverTimezoneTZ = 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.serverTimezoneTZ.getID().equals("GMT")) {
                throw ...
            }
        }
     
        this.defaultTimeZone = this.serverTimezoneTZ;
    }

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

让我们登录到 `MySQL` 服务器验证这两个值：

重点在这里！若 `String configuredTimeZoneOnServer` 得到的是 `CST` 那么 `Java` 会误以为这是`美国中部时间` `UTC -0500`，因此 `TimeZone.getTimeZone(canonicalTimezone)` 会给出错误的时区信息。  
![在这里插入图片描述][20210508144558411.png_pic_center]

如图所示，本机默认时区是 `Asia/Shanghai +0800`，误认为服务器时区为 `UTC -0500`，实际上服务器是 `UTC +0800`。

我们会想到，即便时区有误解，如果 `Timestamp` 是以 `long` 表示的时间戳传输，也不会出现问题，下面让我们追踪到`com.mysql.cj.jdbc.PreparedStatement.setTimestamp()`。

public void setTimestamp(int parameterIndex, Timestamp x) throws java.sql.SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            setTimestampInternal(parameterIndex, x, this.session.getDefaultTimeZone());
        }
    }

注意到这里 `this.session.getDefaultTimeZone()` 得到的是刚才那个 `UTC -0500`。

private void setTimestampInternal(int parameterIndex, Timestamp x, TimeZone tz) throws SQLException {
        if (x == null) {
            setNull(parameterIndex, MysqlType.TIMESTAMP);
        } else {
            if (!this.sendFractionalSeconds.getValue()) {
                x = TimeUtil.truncateFractionalSeconds(x);
            }
     
            this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = MysqlType.TIMESTAMP;
     
            if (this.tsdf == null) {
                this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss", Locale.US);
            }
     
            this.tsdf.setTimeZone(tz);
     
            StringBuffer buf = new StringBuffer();
            buf.append(this.tsdf.format(x));
            if (this.session.serverSupportsFracSecs()) {
                buf.append('.');
                buf.append(TimeUtil.formatNanos(x.getNanos(), true));
            }
            buf.append('\'');
     
            setInternal(parameterIndex, buf.toString());
        }
    }

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

*  `JDBC` 误认为会话时区在`美国中部时间` `UTC-5`
 *  `JBDC` 把 `Timestamp+0` 转为 `UTC-5` 的 `String-5`
 *  `MySQL` 认为会话时区在 `UTC+8`，将 `String-5` 转为 `Timestamp-13`  
    最终结果相差 13 个小时！如果处在冬令时还会相差 14 个小时！

### 4. 解决方案 ###

解决此问题的方法也很简单，我们可以明确指定`MySQL`数据库的时区，不使用引发误解的`CST`，可以将`time_zone`改为`'+8:00'`，同时`jdbc`连接串中也可以增加`serverTimezone=Asia/Shanghai`。

mysql> set global time_zone = '+08:00';
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> set time_zone = '+08:00';
    Query OK, 0 rows affected (0.00 sec)

或者修改 `my.cnf` 文件，在 \[`mysqld`\] 节下增加 `default-time-zone = '+08:00'`。修改时区操作影响深远，需要重启 `MySQL` 服务器，建议在维护时间进行。

如何避免上述时区问题，可能你心里也有了些方法，简要总结几点如下：

*  首先保证系统时区准确。
 *  `jdbc`连接串中指定时区，并与数据库时区一致。
 *  `time_zone`参数建议设置为`'+8:00'`，不使用容易误解的`CST`。
 *  各环境数据库实例时区参数保持相同。

# 查看MySQL版本
    select VERSION();
    # 查看默认时区
    show variables like "%time_zone";

[20210508144558411.png_pic_center]: /images/20221021/02243a55a34f48eb99557f1b4c49e3d2.png