MySQLDriver5.1.X與6.X日期相容性問題

激酶發表於2017-04-07
MySql

1.引言

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

　　首先看從http://search.maven.org中可以大概的看到MySQL Driver的釋出歷史：
screenshot.png
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執行效果如下：
screenshot.png
上圖v5.1.36版本的Driver插入日期，雖然都是同一個時間點（誤差一秒以內），但是表現在資料庫中的時間看上去相差很大，一個是東8區的當地時間以yyyy-MM-dd HH:mm:ss格式化後的時間，另一個是西6區格式化後的當地時間，也就相當於是Java中的LocalDateTime那種不含時區的時間。
因此，一般做全球化的應用時，建議時間儲存成BigInt型的，避免相同的時間點，用不同時區帶來的誤差。
screenshot.png
上圖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
screenshot.png
可見，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牽扯進來。
文章1.png

  • 相同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，但必須事先調整資料庫伺服器時區，做好測試，避免一開始資料庫時區有問題，造成髒資料或資料不一致現象。


相關文章