MySQL Driver 5.1.X与6.X日期兼容性问题

1.引言

　　最近在一个新项目里尝试使用mysql-connector-java-6.0.5.jar，但是从MaxCompute（原名ODPS）中导入MySQL的数据在控制台中看到是正常的，从Java应用里读取的却是相差13或14小时的。甚至SQL里限定了数据的时间在某一天，应用查出来的数据还能是不在那天的。这就很奇怪了，本着求根问底，踩坑识坑的精神，好好地研究了一把。

　　首先看从http://search.maven.org中可以大概的看到MySQL Driver的发布历史：

5.1.X和6.X版本现在正在双线演进。

2.复现问题

　　那么首先写一个小的JDBC程序来验证一下两个版本Driver行为的不一致，MySQL中的表如下：

CREATE TABLE `demo_table` (
    `id` INT(11) NULL DEFAULT NULL,
    `ts` TIMESTAMP NULL DEFAULT NULL
) COMMENT='演示用' ENGINE=InnoDB;

程序如下：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.TimeZone;

public class Tester {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://host:port/db_name?characterEncoding=UTF-8&autoReconnect=true";
        String user = "username";
        String password = "password";
        //1.在应用默认的GMT+8:00时区的环境下执行插入语句
        try (Connection conn = DriverManager.getConnection(url, user, password); PreparedStatement psmt =
            conn.prepareStatement("insert into demo_table (id , ts) values (1, ?)");) {
            System.out.println("Driver version：" + conn.getMetaData().getDriverVersion());
            Date date = new Date();
            System.out.println(date.toString());
            psmt.setTimestamp(1, new Timestamp(date.getTime()));
            psmt.executeUpdate();
        }
        //2.把应用默认时区改为GMT-6:00时区，再执行一遍一样的插入语句
        System.setProperty("user.timezone", "GMT-6");
        TimeZone.setDefault(TimeZone.getTimeZone("GMT-6"));
        try (Connection conn = DriverManager.getConnection(url, user, password); PreparedStatement psmt =
            conn.prepareStatement("insert into demo_table (id , ts) values (2, ?)");) {
            Date date = new Date();
            System.out.println(new Date());
            psmt.setTimestamp(1, new Timestamp(date.getTime()));
            psmt.executeUpdate();
        }
        //3.执行一遍写死的SQL
        try (Connection conn = DriverManager.getConnection(url, user, password); PreparedStatement psmt =
            conn.prepareStatement("insert into demo_table (id , ts) values (3, '2017-03-01 15:00:30')");) {
            psmt.executeUpdate();
        }
    }
}

使用两个不同版本的Driver执行效果如下：

上图v5.1.36版本的Driver插入日期，虽然都是同一个时间点（误差一秒以内），但是表现在数据库中的时间看上去相差很大，一个是东8区的当地时间以yyyy-MM-dd HH:mm:ss格式化后的时间，另一个是西6区格式化后的当地时间，也就相当于是Java中的LocalDateTime那种不含时区的时间。
因此，一般做全球化的应用时，建议时间存储成BigInt型的，避免相同的时间点，用不同时区带来的误差。

上图v6.0.5版本的Driver插入日期，在同一个时间点，插入数据库中的时间一致，不管是哪个时区，都以数据库服务器所在时区进行重新格式化。
而Demo程序中第三个用例插入的数据效果都是一样的，是因为SQL文本本身不含时区信息，SQL中的日期被当做数据库服务器的当地时间。

3.问题原因探寻

友情提示：此处源码较多，如果无耐心，可以假装已经看了源码，直接看结论就行了。

为了证明问题确实存在，我们上MySQL网站看了mysql-connector-java 5.1的文档，文档第16章节选如下：
https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-known-issues-limitations.html

可见，v5.1.X版中的行为，在官方开发者看来是一个issue，在v6.X中进行了fix。

3.1深入源码：mysql-connector-java-v5.1.35

深入MySQL Driver的代码，以PreparedStatement.setTimestamp(int, Timestamp)为例我们可以发现：

v5.1.35, com.mysql.jdbc.ServerPreparedStatement, line: 2127~2132
public void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException {
    synchronized (checkClosed().getConnectionMutex()) {
        setTimestampInternal(parameterIndex, x, null, this.connection.getDefaultTimeZone(), false);
    }
}

connection.getDefaultTimeZone跟进去，核心实现是：

v5.1.35, com.mysql.jdbc.TimeUtil, line: 72~74
public static final TimeZone getDefaultTimeZone(boolean useCache) {
    return (TimeZone) (useCache ? DEFAULT_TIMEZONE.clone() : TimeZone.getDefault().clone());
}

也就是setTimestampInternal中传入的第四个参数TimeZone是应用服务器的时区信息。那么再看setTimestampInternal具体做了什么事：

v5.1.35, com.mysql.jdbc.ServerPreparedStatement, line: 2155~2175
private void setTimestampInternal(int parameterIndex, java.sql.Timestamp x, Calendar targetCalendar, TimeZone tz, boolean rollForward) throws SQLException {
    if (x == null) {
        setNull(parameterIndex, java.sql.Types.TIMESTAMP);
    } else {
        BindValue binding = getBinding(parameterIndex, false);
        setType(binding, MysqlDefs.FIELD_TYPE_DATETIME);

        if (!this.useLegacyDatetimeCode) {
            binding.value = x;
        } else {
            Calendar sessionCalendar = this.connection.getUseJDBCCompliantTimezoneShift() ? this.connection.getUtcCalendar()
                    : getCalendarInstanceForSessionOrNew();

            binding.value = TimeUtil.changeTimezone(this.connection, sessionCalendar, targetCalendar, x, tz, this.connection.getServerTimezoneTZ(),
                    rollForward);

            binding.isNull = false;
            binding.isLongData = false;
        }
    }
}

可以看到，在没有设置useLegacyDatetimeCode连接参数的情况下，binding.value = TimeUtil.changeTimezone(this.connection, sessionCalendar, targetCalendar, x, tz, this.connection.getServerTimezoneTZ(), rollForward);传入了应用服务器的时区信息，和貌似数据库服务器端的时区信息（从getServerTimezoneTZ()名字猜测，有兴趣的话可以追朔一下com.mysql.jdbc.ConnectionPropertiesImpl的1050行）。那么TimeUtil.changeTimezone()的实现如下：

v5.1.35, com.mysql.jdbc.TimeUtil, line: 158~193
public static Timestamp changeTimezone(MySQLConnection conn, Calendar sessionCalendar, Calendar targetCalendar, Timestamp tstamp, TimeZone fromTz,
        TimeZone toTz, boolean rollForward) {
    if ((conn != null)) {
        if (conn.getUseTimezone()) {
            // Convert the timestamp from GMT to the server's timezone
            Calendar fromCal = Calendar.getInstance(fromTz);
            fromCal.setTime(tstamp);

            int fromOffset = fromCal.get(Calendar.ZONE_OFFSET) + fromCal.get(Calendar.DST_OFFSET);
            Calendar toCal = Calendar.getInstance(toTz);
            toCal.setTime(tstamp);

            int toOffset = toCal.get(Calendar.ZONE_OFFSET) + toCal.get(Calendar.DST_OFFSET);
            int offsetDiff = fromOffset - toOffset;
            long toTime = toCal.getTime().getTime();

            if (rollForward || (conn.isServerTzUTC() && !conn.isClientTzUTC())) {
                toTime += offsetDiff;
            } else {
                toTime -= offsetDiff;
            }

            Timestamp changedTimestamp = new Timestamp(toTime);

            return changedTimestamp;
        } else if (conn.getUseJDBCCompliantTimezoneShift()) {
            if (targetCalendar != null) {

                Timestamp adjustedTimestamp = new Timestamp(jdbcCompliantZoneShift(sessionCalendar, targetCalendar, tstamp));

                adjustedTimestamp.setNanos(tstamp.getNanos());

                return adjustedTimestamp;
            }
        }
    }

    return tstamp;
}

显然，没有设置useTimezone连接参数的话，直接返回的就是我们一开始setTimestamp(int, Timestamp)时的那个Timestamp的值。在PreparedStatement设置完所有参数后，一般调用的是executeUpdate()，细究里面代码，略去一堆中间的，拎出最核心的代码：

v5.1.35, com.mysql.jdbc.ServerPreparedStatement, line: 2353~2416
private void storeDateTime413AndNewer(Buffer intoBuf, java.util.Date dt, int bufferType) throws SQLException {
    synchronized (checkClosed().getConnectionMutex()) {
        Calendar sessionCalendar = null;

        if (!this.useLegacyDatetimeCode) {
            if (bufferType == MysqlDefs.FIELD_TYPE_DATE) {
                sessionCalendar = getDefaultTzCalendar();
            } else {
                sessionCalendar = getServerTzCalendar();
            }
        } else {
            sessionCalendar = (dt instanceof Timestamp && this.connection.getUseJDBCCompliantTimezoneShift()) ? this.connection.getUtcCalendar()
                    : getCalendarInstanceForSessionOrNew();
        }

        java.util.Date oldTime = sessionCalendar.getTime();

        try {
            sessionCalendar.setTime(dt);

            if (dt instanceof java.sql.Date) {
                sessionCalendar.set(Calendar.HOUR_OF_DAY, 0);
                sessionCalendar.set(Calendar.MINUTE, 0);
                sessionCalendar.set(Calendar.SECOND, 0);
            }

            byte length = (byte) 7;

            if (dt instanceof java.sql.Timestamp) {
                length = (byte) 11;
            }

            intoBuf.ensureCapacity(length);

            intoBuf.writeByte(length); // length

            int year = sessionCalendar.get(Calendar.YEAR);
            int month = sessionCalendar.get(Calendar.MONTH) + 1;
            int date = sessionCalendar.get(Calendar.DAY_OF_MONTH);

            intoBuf.writeInt(year);
            intoBuf.writeByte((byte) month);
            intoBuf.writeByte((byte) date);

            if (dt instanceof java.sql.Date) {
                intoBuf.writeByte((byte) 0);
                intoBuf.writeByte((byte) 0);
                intoBuf.writeByte((byte) 0);
            } else {
                intoBuf.writeByte((byte) sessionCalendar.get(Calendar.HOUR_OF_DAY));
                intoBuf.writeByte((byte) sessionCalendar.get(Calendar.MINUTE));
                intoBuf.writeByte((byte) sessionCalendar.get(Calendar.SECOND));
            }

            if (length == 11) {
                //    MySQL expects microseconds, not nanos
                intoBuf.writeLong(((java.sql.Timestamp) dt).getNanos() / 1000);
            }

        } finally {
            sessionCalendar.setTime(oldTime);
        }
    }
}

可以看到，应用端的Driver实质上是把年、月、日、时、分、秒、毫秒信息分别写入到服务器端。
如果以应用服务器端的时区来读取年月日时分秒信息，那就是应用服务器的时间，去掉时区信息，给了数据库服务器。
那么如果东8区的2017/03/31 01:02:03和西4区的2017/03/31 01:02:03发送给数据库服务器，数据库服务器收到的数据是一样的，而真实的这两个时间应该相差12小时才对。

3.2深入源码：mysql-connector-java-v6.0.5

对于v6.0.5版本的Driver的行为是怎样呢，照着v5.1.35的经验来探索一下：

v6.0.5, com.mysql.cj.jdbc.ServerPreparedStatement, line: 1812~1816
public void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException {
    synchronized (checkClosed().getConnectionMutex()) {
        setTimestampInternal(parameterIndex, x, this.session.getDefaultTimeZone());
    }
}

和v5.1.35相比，把this.connection.getDefaultTimeZone()改为了this.session.getDefaultTimeZone()，而session.getDefaultTimeZone()中defaultTimeZone参数初始化时默认给了TimeZone.getDefault()，在com.mysql.cj.jdbc.ConnectionImpl新建连接时，会调用到com.mysql.cj.mysqla.MysqlaSession.configureTimezone()，把session的默认时区设置为数据库服务器的默认时区：

v6.0.5, com.mysql.cj.mysqla.MysqlaSession, line: 280~313
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 ExceptionFactory.createException(WrongArgumentException.class, Messages.getString("Connection.9", new Object[] { canonicalTimezone }),
                    getExceptionInterceptor());
        }
    }

    this.defaultTimeZone = this.serverTimezoneTZ;
}

再看setTimestampInternal()的实现：

v6.0.5, com.mysql.cj.jdbc.ServerPreparedStatement, line: 1825~1839
private void setTimestampInternal(int parameterIndex, java.sql.Timestamp x, TimeZone tz) throws SQLException {
    if (x == null) {
        setNull(parameterIndex, MysqlType.TIMESTAMP);
    } else {
        BindValue binding = getBinding(parameterIndex, false);
        resetToType(binding, MysqlaConstants.FIELD_TYPE_DATETIME);

        if (!this.sendFractionalSeconds.getValue()) {
            x = TimeUtil.truncateFractionalSeconds(x);
        }

        binding.value = x;
        binding.tz = tz;
    }
}

这里和v5.1.35的区别是，binding里除了存放value，还能存放TimeZone的信息。默认情况下，传入的是数据库服务器的TimeZone。
那么再看executeUpdate()相关的实现，跟进去深入，依然可以追朔到com.mysql.cj.jdbc.ServerPreparedStatement.storeDateTime(PacketPayload, Date, TimeZone, int)方法：

v6.0.5, com.mysql.cj.jdbc.ServerPreparedStatement, line: 1954~1999
    private void storeDateTime(PacketPayload intoBuf, java.util.Date dt, TimeZone tz, int bufferType) throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            Calendar cal = Calendar.getInstance(tz);

            cal.setTime(dt);

            if (dt instanceof java.sql.Date) {
                cal.set(Calendar.HOUR_OF_DAY, 0);
                cal.set(Calendar.MINUTE, 0);
                cal.set(Calendar.SECOND, 0);
            }

            byte length = (byte) 7;

            if (dt instanceof java.sql.Timestamp) {
                length = (byte) 11;
            }

            intoBuf.ensureCapacity(length);

            intoBuf.writeInteger(IntegerDataType.INT1, length); // length

            int year = cal.get(Calendar.YEAR);
            int month = cal.get(Calendar.MONTH) + 1;
            int date = cal.get(Calendar.DAY_OF_MONTH);

            intoBuf.writeInteger(IntegerDataType.INT2, year);
            intoBuf.writeInteger(IntegerDataType.INT1, month);
            intoBuf.writeInteger(IntegerDataType.INT1, date);

            if (dt instanceof java.sql.Date) {
                intoBuf.writeInteger(IntegerDataType.INT1, 0);
                intoBuf.writeInteger(IntegerDataType.INT1, 0);
                intoBuf.writeInteger(IntegerDataType.INT1, 0);
            } else {
                intoBuf.writeInteger(IntegerDataType.INT1, cal.get(Calendar.HOUR_OF_DAY));
                intoBuf.writeInteger(IntegerDataType.INT1, cal.get(Calendar.MINUTE));
                intoBuf.writeInteger(IntegerDataType.INT1, cal.get(Calendar.SECOND));
            }

            if (length == 11) {
                //  MySQL expects microseconds, not nanos
                intoBuf.writeInteger(IntegerDataType.INT4, ((java.sql.Timestamp) dt).getNanos() / 1000);
            }
        }
    }

这里的前几行直接把日期对象转化为基于数据库服务器时区的对象，然后再写入年、月、日、时、分、秒、毫秒的信息。
在这种情况下，东8区的2017/03/31 01:02:03和西4区的2017/03/31 01:02:03发送给东7区的数据库服务器，数据库收到前者的时间是2017/03/31 00:02:03，后者的时间是2017/03/30 12:02:03，恰好相差12小时，与实际相符。

3.3此处不深入源码直接讲结论

对于从数据库取日期时间，和写入日期时间类似：

• 在v5.1.X的Driver中，取到的是数据库存储的年月日时分秒字面上的时间再附上应用服务器的时区信息；
• 在v6.X的Driver中，取到的是数据库存储的年月日时分秒字面上的时间和数据库服务器的时区信息，然后再转换为应用服务器所在时区的年月日时分秒；

不同解决方案对比分析

既然发现了问题，那么就根据当前的情况来分析不同情况下使用不同的方案可能带来的结果或问题。因为多数同学系统中多多少少会用到MaxCompute（ODPS），所以这里也把MaxCompute牵扯进来。

• 相同Driver版本的读写：

  • v5.1.X中，完全无问题，整条链路时间一致；
  • v6.X中，应用服务器读写的时间一致，但是从数据库服务器到MaxCompute时时间会发生异常，需要同步到MaxCompute时使用Long型时间戳来解决问题；

• 不同Driver版本的读写：

  • 不同应用不同版本Driver下，读取同一个数据源，可能发生时间错乱，需要整条链路各服务器、服务器上的程序时区保持一致才能避免，或者让v5.1.X带上useTimeZone参数，以便行为与6.X一致；

• 第三方系统作为数据源的应用：

  • 第三方系统过来的时间，有数据从MaxCompute同步到MySQL时会发生时区异常，从而导致v6.X下应用读取时间异常。这时候需要设置MySQL的时区与第三方系统的时区保持一致。

5.结论与建议

从上述分析来看，5.1.X的确存在一些问题，只是我们没有开发用于多时区的应用，或者已经习以为常认为合理罢了，甚至总结出了用BigInt存储跨时区的信息那种经验。
6.X彻底解决了跨时区的应用问题，让我们开发中顾虑更少。

针对实际的开发，建议：

• 数据库服务器的时间设置：

  • 对于纯产生数据的应用，没有数据回流到数仓的话，数据库时区随意设置，知道机制就可以了。
  • 对于需要数据回流到数仓，或者数仓的数据会回流到前台应用数据库的话，那么需要设置成和数仓的时区一致，避免两个库之间同步时发生时区异常的问题；

• 对于应用：

  • 老应用用5.1.X，如果没有useTimezone的参数，那么谨慎升级Driver到6.X，如果要升级，记得做好测试，先修改数据库的TimeZone，然后升级Driver；
  • 新的应用建议使用6.X的Driver，但必须事先调整数据库服务器时区，做好测试，避免一开始数据库时区有问题，造成脏数据或数据不一致现象。