188      阿裏雲  技術社區[雲棲]

MySQL Driver 5.1.X與6.X日期兼容性問題

1.引言

　　最近在一個新項目裏嚐試使用mysql-connector-java-6.0.5.jar，但是從MaxCompute（原名ODPS）中導入MySQL的數據在控製台中看到是正常的，從Java應用裏讀取的卻是相差13或14小時的。甚至SQL裏限定了數據的時間在某一天，應用查出來的數據還能是不在那天的。這就很奇怪了，本著求根問底，踩坑識坑的精神，好好地研究了一把。

　　首先看從https://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，但必須事先調整數據庫服務器時區，做好測試，避免一開始數據庫時區有問題，造成髒數據或數據不一致現象。

最後更新：2017-04-07 21:05:50

  上一篇： 一個golang並行庫源碼解析

  下一篇： Raft