没想到，JDBC 驱动会偷偷修改 sql_mode 的会话值

Posted on by hackdl

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

现象就是一个 update 操作，在 mysql 客户端中执行提示 warning，但在 java 程序中执行却又报错。

问题重现

mysql>createtabletest.t1(idintprimarykey,c1datetime);
QueryOK,0rowsaffected(0.01sec)

mysql>insertintotest.t1values(1,now());
QueryOK,1rowaffected(0.00sec)

mysql>updatetest.t1setc1=str_to_date('2024-02-2301:01:01.0','%Y-%m-%d%H:%i:%s')whereid=1;
QueryOK,1rowaffected,1warning(0.00sec)
Rowsmatched:1Changed:1Warnings:1

mysql>showwarnings;
+---------+------+-------------------------------------------------------------+
|Level|Code|Message|
+---------+------+-------------------------------------------------------------+
|Warning|1292|Truncatedincorrectdatetimevalue:'2024-02-2301:01:01.0'|
+---------+------+-------------------------------------------------------------+
1rowinset(0.00sec)

mysql>select*fromtest.t1;
+----+---------------------+
|id|c1|
+----+---------------------+
|1|2024-02-2301:01:01|
+----+---------------------+
1rowinset(0.00sec)

update 语句中使用STR_TO_DATE函数将字符串转换为日期时间格式。

但因为这个格式字符串'%Y-%m-%d %H:%i:%s'没有对日期字符串中的毫秒部分.0进行解析，所以这一部分会被 truncate 掉。

可以看到，该语句在 mysql 客户端中执行时没有报错，只是提示 warning。

同样的 SQL，在下面这段 java 代码中跑却直接报错。

packagecom.example;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.SQLException;
importjava.sql.Statement;

publicclassJdbcTest{

privatestaticfinalStringJDBC_URL="jdbc:mysql://10.0.0.198:3306/information_schema";
privatestaticfinalStringUSER="root";
privatestaticfinalStringPASSWORD="123456";

publicstaticvoidmain(String[]args){
try(Connectionconnection=DriverManager.getConnection(JDBC_URL,USER,PASSWORD)){
try(Statementstatement=connection.createStatement()){
StringupdateQuery="UPDATEtest.t1SETc1=STR_TO_DATE('2024-02-2301:01:01.0','%Y-%m-%d%H:%i:%s')WHEREid=1";
introwsAffected=statement.executeUpdate(updateQuery);
System.out.println("Rowsaffected:"+rowsAffected);
}
}catch(SQLExceptione){
e.printStackTrace();
}
}
}
#java-jartarget/jdbc-test-1.0-SNAPSHOT-jar-with-dependencies.jar
com.mysql.cj.jdbc.exceptions.MysqlDataTruncation:Datatruncation:Truncatedincorrectdatetimevalue:'2024-02-2301:01:01.0'
atcom.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)
atcom.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1337)
atcom.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2112)
atcom.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1247)
atcom.example.JdbcTest.main(JdbcTest.java:17)

问题根因

刚开始以为这个报错跟 sql_mode 有关，但实际上这个实例的 sql_mode 为空。

mysql>showglobalvariableslike'%sql_mode%';
+---------------+-------+
|Variable_name|Value|
+---------------+-------+
|sql_mode||
+---------------+-------+
1rowinset(0.00sec)

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

但万万没想到，JDBC 驱动会偷偷修改 sql_mode 的会话值。

在上面的 java 程序中加了一段代码，用来打印 sql_mode 的会话值。

ResultSetresultSet=statement.executeQuery("SELECT@@SESSION.sql_mode");
if(resultSet.next()){
StringsqlModeValue=resultSet.getString(1);
System.out.println("Currentsql_modevalue:"服务器托管网+sqlModeValue);
}

结果发现当前会话的 sql_mode 竟然是STRICT_TRANS_TABLES。

Currentsql_modevalue:STRICT_TRANS_TABLES

而STRICT_TRANS_TABLES就是导致 update 操作报错的罪魁祸首！

这一点，很容易在 mysql 客户端中验证出来。

mysql>setsessionsql_mode='STRICT_TRANS_TABLES';
QueryOK,0rowsaffected,1warning(0.00sec)

mysql>updatetest.t1setc1=str_to_date('2024-02-2301:01:01.0','%Y-%m-%d%H:%i:%s')whereid=1;
ERROR1292(22007):Truncatedincorrectdatetimevalue:'2024-02-2301: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
privatevoidsetupServerForTruncationChecks()throwsSQLException{
synchronized(getConnectionMutex()){
//获取JDBC驱动程序配置中的jdbcCompliantTruncation属性
RuntimePropertyjdbcCompliantTruncation=this.propertySet.getProperty(PropertyKey.jdbcCompliantTruncation);
if(jdbcCompliantTruncation.getValue()){
//获取当前会话的sql_mode
StringcurrentSqlMode=this.session.getServerSession().getServerVariable("sql_mode");
//检查sql_mode中是否包含STRICT_TRANS_TABLES选项
booleanstrictTransTablesIsSet=StringUtils.indexOfIgnoreCase(currentSqlMode,"STRICT_TRANS_TABLES")!=-1;
//如果sql_mode为空，或长度为0，或不包含STRICT_TRANS_TABLES选项，
//则构建SETsql_mode语句，将STRICT_TRANS_TABLES添加到sql_mode中
if(currentSqlMode==null||currentSqlMode.length()==0||!strictTransTablesIsSet){
StringBuildercommandBuf=newStringBuilder("SETsql_mode='");

if(currentSqlMode!=null&&currentSqlMode.length()>0){
commandBuf.append(currentSqlMode);
commandBuf.append(",");
}

commandBuf.append("STRICT_TRANS_TABLES'");
//执行SETsql_mode语句
this.session.execSQL(null,commandBuf.toString(),-1,null,false,this.nullStatementResultSetFactory,null,false);

jdbcCompliantTruncation.setValue(false);//server'shandlingthisforusnow
}elseif(strictTransTablesIsSet){
//如果sql_mode中包含STRICT_TRANS_TABLES选项，则不做任何调整
//Wedidn'tsetit,butsomeonedid,sowepiggybackonit
jdbcCompliantTruncation.setVa服务器托管网lue(false);//server'shandlingthisforusnow
}
}
}
}

所以，尽管 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=''");
StringupdateQuery="UPDATEtest.t1SETc1=STR_TO_DATE('2024-02-2301:01:01.0','%Y-%m-%d%H:%i:%s')WHEREid=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 throwsDataTruncationexceptions as is required by the JDBC specification, unless the connection was configured not to do so by using the propertyjdbcCompliantTruncationand setting it tofalse.

参考资料

  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

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: