沒想到，JDBC 驅動會偷偷修改 sql_mode 的會話值

iVictor發表於2024-03-04
JDBCSQL會話

最近碰到一個 case，值得分享一下。

現象就是一個 update 操作，在 mysql 客戶端中執行提示 warning，但在 java 程式中執行卻又報錯。

問題重現

mysql> create table test.t1(id int primary key, c1 datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test.t1 values(1,now());
Query OK, 1 row affected (0.00 sec)

mysql> update test.t1 set c1=str_to_date('2024-02-23 01:01:01.0','%Y-%m-%d %H:%i:%s') where id=1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '2024-02-23 01:01:01.0' |
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test.t1;
+----+---------------------+
| id | c1                  |
+----+---------------------+
|  1 | 2024-02-23 01:01:01 |
+----+---------------------+
1 row in set (0.00 sec)

update 語句中使用STR_TO_DATE函式將字串轉換為日期時間格式。

但因為這個格式字串'%Y-%m-%d %H:%i:%s'沒有對日期字串中的毫秒部分.0進行解析，所以這一部分會被 truncate 掉。

可以看到，該語句在 mysql 客戶端中執行時沒有報錯，只是提示 warning。

同樣的 SQL，在下面這段 java 程式碼中跑卻直接報錯。

package com.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcTest {

    private static final String JDBC_URL = "jdbc:mysql://10.0.0.198:3306/information_schema";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD)) {
            try (Statement statement = connection.createStatement()) {
                String updateQuery = "UPDATE test.t1 SET c1 = STR_TO_DATE('2024-02-23 01:01:01.0', '%Y-%m-%d %H:%i:%s') WHERE id=1";
                int rowsAffected = statement.executeUpdate(updateQuery);
                System.out.println("Rows affected: " + rowsAffected);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
# java -jar target/jdbc-test-1.0-SNAPSHOT-jar-with-dependencies.jar
com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Truncated incorrect datetime value: '2024-02-23 01:01:01.0'
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)
        at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1337)
        at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2112)
        at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1247)
        at com.example.JdbcTest.main(JdbcTest.java:17)

問題根因

剛開始以為這個報錯跟 sql_mode 有關，但實際上這個例項的 sql_mode 為空。

mysql> show global variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

所以，一開始就排除了 sql_mode 的可能性。

但萬萬沒想到，JDBC 驅動會偷偷修改 sql_mode 的會話值。

在上面的 java 程式中加了一段程式碼，用來列印 sql_mode 的會話值。

ResultSet resultSet = statement.executeQuery("SELECT @@SESSION.sql_mode");
if (resultSet.next()) {
    String sqlModeValue = resultSet.getString(1);
    System.out.println("Current sql_mode value: " + sqlModeValue);
}

結果發現當前會話的 sql_mode 竟然是STRICT_TRANS_TABLES。

Current sql_mode value: STRICT_TRANS_TABLES

而STRICT_TRANS_TABLES就是導致 update 操作報錯的罪魁禍首！

這一點，很容易在 mysql 客戶端中驗證出來。

mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> update test.t1 set c1=str_to_date('2024-02-23 01:01:01.0','%Y-%m-%d %H:%i:%s') where id=1;
ERROR 1292 (22007): Truncated incorrect datetime value: '2024-02-23 01:01:01.0'

所以，問題來了， sql_mode 是在哪裡修改的？

sql_mode 是在哪裡修改的？

分析 JDBC 驅動程式碼，發現會話的 sql_mode 是在setupServerForTruncationChecks中修改的。

該方法是在連線建立後，初始化時呼叫的。

其主要作用是檢查當前會話的 sql_mode 是否包含STRICT_TRANS_TABLES，如果不包含，則會透過 SET 命令修改當前會話的 sql_mode，使其包含STRICT_TRANS_TABLES。

// src/main/user-impl/java/com/mysql/cj/jdbc/ConnectionImpl.java
private void setupServerForTruncationChecks() throws SQLException {
    synchronized (getConnectionMutex()) {
        // 獲取 JDBC 驅動程式配置中的 jdbcCompliantTruncation 屬性
        RuntimeProperty<Boolean> jdbcCompliantTruncation = this.propertySet.getProperty(PropertyKey.jdbcCompliantTruncation);
        if (jdbcCompliantTruncation.getValue()) {
            // 獲取當前會話的 sql_mode
            String currentSqlMode = this.session.getServerSession().getServerVariable("sql_mode");
            // 檢查 sql_mode 中是否包含 STRICT_TRANS_TABLES 選項
            boolean strictTransTablesIsSet = StringUtils.indexOfIgnoreCase(currentSqlMode, "STRICT_TRANS_TABLES") != -1;
            // 如果 sql_mode 為空，或長度為 0，或不包含 STRICT_TRANS_TABLES 選項，
            // 則構建 SET sql_mode 語句，將 STRICT_TRANS_TABLES 新增到 sql_mode 中
            if (currentSqlMode == null || currentSqlMode.length() == 0 || !strictTransTablesIsSet) {
                StringBuilder commandBuf = new StringBuilder("SET sql_mode='");

                if (currentSqlMode != null && currentSqlMode.length() > 0) {
                    commandBuf.append(currentSqlMode);
                    commandBuf.append(",");
                }
     
                commandBuf.append("STRICT_TRANS_TABLES'");
                // 執行 SET sql_mode 語句
                this.session.execSQL(null, commandBuf.toString(), -1, null, false, this.nullStatementResultSetFactory, null, false);

                jdbcCompliantTruncation.setValue(false); // server's handling this for us now
            } else if (strictTransTablesIsSet) {
                // 如果 sql_mode 中包含 STRICT_TRANS_TABLES 選項，則不做任何調整
                // We didn't set it, but someone did, so we piggy back on it
                jdbcCompliantTruncation.setValue(false); // server's handling this for us now
            }
        }
    }
}

所以，儘管 mysql 服務端的 sql_mode 為空，但由於 JDBC 驅動將會話的 sql_mode 調整為了STRICT_TRANS_TABLES，最後還是導致 update 操作報錯。

如何解決 java 程式中執行報錯的問題

很簡單，在 JDBC URL 中將jdbcCompliantTruncation屬性設定為 false。

jdbc:mysql://10.0.0.198:3306/information_schema?jdbcCompliantTruncation=false

除此之外，也可修改 java 程式碼，在 update 操作之前顯式設定 sql_mode 的會話值，如，

statement.execute("SET @@SESSION.sql_mode = ''");
String updateQuery = "UPDATE test.t1 SET c1 = STR_TO_DATE('2024-02-23 01:01:01.0', '%Y-%m-%d %H:%i:%s') WHERE id=1";

但這種方式對應用程式碼有侵入，不建議這麼做。

實際上，JDBC 驅動支援在 URL 中修改引數的會話值。

在 URL 中修改引數的會話值，有以下好處：

  • 無需在每次 SQL 操作之前顯式執行設定語句。這使得配置變更更為集中化，更容易管理和維護。

  • 避免了對應用程式碼的直接侵入，提高了程式碼的可維護性和靈活性。

JDBC 驅動中如何修改引數的會話值

從 mysql-connector-java 3.1.8 開始，支援透過sessionVariables屬性修改 MySQL 引數的會話值。語法如下：

sessionVariables=variable_name1=variable_value1,variable_name1=variable_value2...variable_nameN=variable_valueN

多個引數之間使用逗號或者分號隔開。

看下面這個示例，同時修改 explicit_defaults_for_timestamp，group_concat_max_len 和 sql_mode 的會話值。

JDBC_URL = "jdbc:mysql://10.0.0.198:3306/information_schema?sessionVariables=explicit_defaults_for_timestamp=OFF,group_concat_max_len=2048,sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE'"

注意，如果jdbcCompliantTruncation為 true（預設值），即使sessionVariables中設定的 sql_mode 不包含STRICT_TRANS_TABLES，最終生效的 sql_mode 的會話值還是會包含STRICT_TRANS_TABLES。

之所以會這樣，主要是因為sessionVariables的設定先於setupServerForTruncationChecks。

JDBC 驅動為什麼要修改 sql_mode 的會話值

這個實際上是 JDBC 規範的要求。

Connector/J issues warnings or throws DataTruncation exceptions as is required by the JDBC specification, unless the connection was configured not to do so by using the property jdbcCompliantTruncation and setting it to false.

參考資料

  1. https://docs.oracle.com/cd/E17952_01/connector-j-8.0-en/connector-j-reference-type-conversions.html
  2. https://dev.mysql.com/doc/connector-j/en/connector-j-connp-props-session.html

相關文章