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

来源:https://www.cnblogs.com/ivictor/p/18051155
-Advertisement-
Play Games

最近碰到一個 case，值得分享一下。 現象就是一個 update 操作，在 mysql 客戶端中執行提示 warning，但在 java 程式中執行卻又報錯。 問題重現 mysql> create table test.t1(id int primary key, c1 datetime);Que ...


最近碰到一個 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

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Windows下CMD和Tomcat設置編碼為UTF-8
    由於日常編寫代碼，都是使用UTF-8編碼，而CMD預設是GBK編碼，導致執行mvn命令時，出現中文亂碼問題，而chcp命令只在當次打開有效，每次打開都要重新切換代碼頁十分不便。 對於CMD，可以通過修改註冊表的方式，永久修改預設代碼頁。打開註冊表編輯器，定位到\HKEY_CURRENT_USER\C ...
  • Linux_Centos_yum報錯總結
    STM32ADC單通道轉換 1. 初始化 ADC功能初始化主要分三部分，GPIO初始化、ADC模式初始化與NVIC初始化。 1.1初始化GPIO void ADC_GPIO_Config(void) // 配置ADC通道引腳 { GPIO_InitTypeDef GPIO_InitStructure ...
  • VMware vSphere 8.0 Update 2b 下載 - Broadcom VMware 首次重大更新
    STM32FATFS文件系統移植 1。 FATFS簡介 FATFS文件系統是一個用於在微控制器上運行的開源文件系統，支持FAT/FATFS、NTFS、exFAT等主流文件系統，且一直保持更新。在此以FatFs官網最新版本v0.15進行移植。 2. 移植具體操作 2.1 下載FatFs源碼 FATFS ...
  • linux伺服器文件實時同步
    linux伺服器文件實時同步 1 背景說明 在做系統集群部署時,涉及到兩個或多個伺服器之間文件同步.在軟體層面linux服務環境找到以下兩種同步方式 利用linux NFS功能將網路共用文件掛載成本地目錄 採用文件監聽,實時推送 伺服器資源如下 伺服器1 10.2.4.51 ,作為主伺服器 伺服器2 ...
  • 關於88e1111 phy模塊的配置說明
    關於88e1111 phy模塊的配置說明 1、前言 ​ 本次主要是參考了88e1111的phy晶元的數據手冊，對於88e1111這款經典的 10M/100M/1000M 乙太網晶元的一些基礎軟體硬體配置做一些說明，拋磚引玉，有不對之處，請多多指教。 2、88e1111 phy晶元的硬體相關 1、ph ...
  • MBP下必備效率工具Alfred5配置攻略
    STM32SPIFLASH讀寫 1.1 SPI註意事項 SPI是同步通信，即通信雙方每次信息交互必會帶有一問一答，這代表在正常的單核MCU（例如STM32）中很難實現軟體模擬的雙向SPI通信（TFT屏幕一類的外設不算，那些頂多屬於單向SPI），因為無法同時發送和接收數據。而在STM32中，硬體實現同 ...
  • signature hdr data: BAD, no. of btyes(9088) out of range 問題排查與解決方案
    在使用yum工具安裝gcc的時候，報出了signature hdr data: BAD, no. of btyes(9088) out of range 的問題 這是由於centos8中rpm工具存在的一個bug，在校驗安裝包頭部大小的時候，應當限製為64M，但是實際限制了64k 這個問題存在於 r ...
  • RDS for MySQL Serverless公測上線：彈性伸縮，最高可降成本超80%
    本文分享自華為雲社區《RDS for MySQL Serverless公測上線：彈性伸縮，最高可降成本超80%》，作者：GaussDB 資料庫。 隨著科技的快速發展，我們正在迅速步入一個全新的數字化時代。數字化時代，數據是最寶貴的資源。資料庫作為存儲數據的倉庫，重要性更是不言而喻。 一、業務背景及痛 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類