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

現(xiàn)象就是一個 update 操作，在 mysql 客戶端中執(zhí)行提示 warning，但在 java 程序中執(zhí)行卻又報錯。

問題重現(xiàn)

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函數(shù)將字符串轉(zhuǎn)換為日期時間格式。

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

可以看到，該語句在 mysql 客戶端中執(zhí)行時沒有報錯，只是提示 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 有關(guān)，但實際上這個實例的 sql_mode 為空。

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

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

但萬萬沒想到，JDBC 驅(qū)動會偷偷修改 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);
}

結(jié)果發(fā)現(xiàn)當前會話的 sql_mode 竟然是STRICT_TRANS_TABLES。

Current?sql_mode?value:?STRICT_TRANS_TABLES

而STRICT_TRANS_TABLES就是導(dǎo)致 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 驅(qū)動代碼，發(fā)現(xiàn)會話的 sql_mode 是在setupServerForTruncationChecks中修改的。

該方法是在連接建立后，初始化時調(diào)用的。

其主要作用是檢查當前會話的 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?驅(qū)動程序配置中的?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?選項，
????????????//?則構(gòu)建?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'");
????????????????//?執(zhí)行?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?選項，則不做任何調(diào)整
????????????????//?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 服務(wù)端的 sql_mode 為空，但由于 JDBC 驅(qū)動將會話的 sql_mode 調(diào)整為了STRICT_TRANS_TABLES，最后還是導(dǎo)致 update 操作報錯。

如何解決 java 程序中執(zhí)行報錯的問題

很簡單，在 JDBC URL 中將jdbcCompliantTruncation屬性設(shè)置為 false。

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

除此之外，也可修改 java 代碼，在 update 操作之前顯式設(shè)置 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";

但這種方式對應(yīng)用代碼有侵入，不建議這么做。

實際上，JDBC 驅(qū)動支持在 URL 中修改參數(shù)的會話值。

在 URL 中修改參數(shù)的會話值，有以下好處：

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

• 避免了對應(yīng)用代碼的直接侵入，提高了代碼的可維護性和靈活性。

JDBC 驅(qū)動中如何修改參數(shù)的會話值

從 mysql-connector-java 3.1.8 開始，支持通過sessionVariables屬性修改 MySQL 參數(shù)的會話值。語法如下：

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

多個參數(shù)之間使用逗號或者分號隔開。

看下面這個示例，同時修改 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中設(shè)置的 sql_mode 不包含STRICT_TRANS_TABLES，最終生效的 sql_mode 的會話值還是會包含STRICT_TRANS_TABLES。

之所以會這樣，主要是因為sessionVariables的設(shè)置先于setupServerForTruncationChecks。

JDBC 驅(qū)動為什么要修改 sql_mode 的會話值

這個實際上是 JDBC 規(guī)范的要求。

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.文章來源地址http://www.zghlxwxcb.cn/news/detail-837785.html

參考資料

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

到了這里，關(guān)于沒想到，JDBC 驅(qū)動會偷偷修改 sql_mode 的會話值的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！