最近碰到一个 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
DataTruncationexceptions as is required by the JDBC specification, unless the connection was configured not to do so by using the propertyjdbcCompliantTruncationand setting it tofalse.
参考资料
- https://docs.Oracle.com/cd/E17952_01/connector-j-8.0-en/connector-j-reference-type-conversions.html
- https://dev.mysql.com/doc/connector-j/en/connector-j-connp-props-session.html
