连接池里那条"脏连接"：SET SESSION sql_mode 泄漏 12 小时、347 行脏数据的根因与加固

作者温若衡-259

MySQL连接池HikariCPsql_mode安全加固会话变量

结论先行：连接池里的"会话变量"是会跨业务复用的——任何一次 SET SESSION（sql_mode、time_zone、max_execution_time、character_set_*）如果不被显式复位，就会沿着这条连接污染下一个使用者。这类故障最危险的地方是：数据写入成功、应用不报错、监控不报警，往往要等对账才发现。

下面是一次真实的事故复盘与加固方案，环境：Spring Boot 3.2 + HikariCP 5.1.0 + MySQL 8.0.36（InnoDB，单库 2.4 亿行订单流水）。

一、现象与背景

事故时间线（全部为生产时间）：

| 时间 | 事件 | |---|---| | 02:14 | DBA 跑一次性数据清洗脚本 cleanup_orphan_orders.sql，脚本开头有 SET SESSION sql_mode=''（目的：让 UPDATE ... SET amount_cents = CAST(x AS BIGINT) 对脏数据不报错） | | 02:31 | 脚本跑完，没执行 SET SESSION sql_mode=@OLD_SQL_MODE 复位（脚本末尾是 COMMIT;，没有 RESET SESSION） | | 02:31 → 14:42 | 这条连接回到 HikariCP 池，被业务代码反复复用 | | 14:42 | 财务对账系统报警：t_order 表有 347 行 amount_cents 与支付回调金额不一致，最大偏差 2.1 倍 |

影响面：347 行订单金额被静默改写（DECIMAL(18,2) 列写入越界值时被截断/取整），涉及 218 个订单，金额偏差合计 ¥18,402.37。没有任何业务侧报警，因为：

应用层：JdbcTemplate.update(...) 返回行数正常，无 SQLException；
数据库层：SHOW WARNINGS 在下一个事务开始时才可见，但没人看；
监控层：QPS、P99、慢查询全部正常（sql_mode 非严格模式只是不报错，不会变慢）。

二、根因分析：为什么"会话变量"会跨业务泄漏

1. MySQL 的 sql_mode 是会话级变量

SHOW VARIABLES LIKE 'sql_mode';
-- 默认（MySQL 8.0）：
-- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
-- ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

STRICT_TRANS_TABLES 开启时，向 DECIMAL(18,2) 列写入越界值会抛 1264 Out of range value；关闭后，MySQL 静默截断并产生 Warning，INSERT/UPDATE 照常成功，affected_rows 也照常返回 1。

2. HikariCP 的会话变量复位策略是"按需 + 可被覆盖"

HikariCP 在建连时执行 connectionInitSql（如果配置了），在归还时执行 rollback（如果事务未提交）——但它不会在每次 checkout 时主动 RESET SESSION，也不会主动校验 sql_mode。这意味着：

如果一条连接被 SET SESSION sql_mode='' 污染，且这条连接没有被关闭，它会带着这个污染值继续服务下一个业务请求；
HikariCP 的 minimumIdle 默认等于 maximumPoolSize（我们配的是 20），这条"脏连接"会常驻池里，直到被 connectionTimeout 淘汰或 maxLifetime（默认 30 分钟）到期——但 maxLifetime 到期后，HikariCP 会复用这条连接（只是标记为"即将回收"），而不是立刻断开。

3. 为什么"复位"这么难？

MySQL 的会话变量复位需要显式：

-- 方式 A：显式设回默认值（但默认值因版本而异，且 MySQL 8.0 默认 sql_mode 比 5.7 长，硬编码容易漏）
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- 方式 B：RESET SESSION（MySQL 8.0 支持，会把所有会话变量重置为全局值）
RESET SESSION;

-- 方式 C：SET SESSION sql_mode = @@GLOBAL.sql_mode（显式对齐全局值）
SET SESSION sql_mode = @@GLOBAL.sql_mode;

方式 B 最干净，但 RESET SESSION 会把所有会话变量（包括 character_set_client、collation_connection、time_zone）都重置，如果业务依赖某些非默认会话变量（比如 time_zone='+08:00'），会被一并打回去。所以方式 C 是更安全的折中。

三、具体做法：四层加固

第 1 层：HikariCP checkout 时强制对齐 sql_mode

@Bean
public HikariDataSource dataSource() {
    HikariConfig cfg = new HikariConfig();
    cfg.setJdbcUrl("jdbc:mysql://...");
    cfg.setMaximumPoolSize(20);
    cfg.setMinimumIdle(20);
    cfg.setMaxLifetime(1800000); // 30 min

    // 关键：每次 checkout 时执行，对齐 sql_mode 到全局值
    cfg.addConnectionCustomizer(conn -> {
        try (Statement st = conn.createStatement()) {
            st.execute("SET SESSION sql_mode = @@GLOBAL.sql_mode");
            st.execute("SET SESSION time_zone = '+08:00'"); // 业务约定
            st.execute("SET SESSION max_execution_time = 5000"); // 兜底
        } catch (SQLException e) {
            throw new RuntimeException("Failed to reset session vars", e);
        }
    });
    return new HikariDataSource(cfg);
}

注意：addConnectionCustomizer 在每次 checkout 时执行（不是建连时），所以能兜住"脏连接"。这是 HikariCP 4.0+ 的 API，5.1.0 已稳定。

第 2 层：MySQL 服务端 `init_connect` 兜底

MySQL 的 init_connect 变量会在每个非 SUPER 权限的会话建立时自动执行：

SET GLOBAL init_connect = 'SET SESSION sql_mode = @@GLOBAL.sql_mode';

限制：init_connect 只对新建连接生效，对池里已存在的连接无效——所以它是第 2 层兜底，不能替代第 1 层。另外，拥有 SUPER 或 SYSTEM_USER 权限的账号会跳过 init_connect，DBA 账号不受保护。

第 3 层：DBA 脚本的"会话卫生"规范

给所有 DBA 脚本加一个强制头部（用 shell 包装，不靠人自觉）：

#!/usr/bin/env bash
# 强制保存/恢复 sql_mode
OLD_SQL_MODE=$(mysql -N -e "SELECT @@SESSION.sql_mode" | tr ',' '\n' | tr '\n' ',')
mysql -e "SET SESSION sql_mode='${OLD_SQL_MODE//,/,}'; ${1}"  # 跑脚本
mysql -e "SET SESSION sql_mode='${OLD_SQL_MODE//,/,}';"       # 显式复位

更稳妥的做法：让 DBA 脚本走独立的、用完即断的连接（不走业务连接池），从根上避免污染池。

第 4 层：可观测性——把 sql_mode 纳入连接池健康检查

// 定时任务：每 5 分钟对池里每条连接做一次 sql_mode 自检
@Scheduled(fixedRate = 300000)
void auditSqlMode() {
    HikariPool pool = ds.getHikariPoolMXBean();
    // 通过 pool 的代理连接执行
    try (Connection c = ds.getConnection();
         ResultSet rs = c.createStatement().executeQuery("SELECT @@SESSION.sql_mode")) {
        if (rs.next()) {
            String mode = rs.getString(1);
            if (!mode.contains("STRICT_TRANS_TABLES")) {
                log.error("Dirty connection detected! sql_mode={}", mode);
                // 上报 + 主动关闭这条连接
            }
        }
    }
}

四、踩过的坑与反直觉之处

connectionInitSql ≠ checkout 时执行：很多人以为 HikariCP 的 connectionInitSql 会在每次借用时执行，实际上它只在建连时执行一次。如果脏连接是池里已存在的，connectionInitSql 救不了你——必须用 addConnectionCustomizer。
RESET SESSION 不是万能的：它会把 time_zone、character_set_client 等一并重置，如果你的业务依赖非默认的 time_zone（比如 +08:00），RESET SESSION 会把它打回系统时区（通常是 UTC），导致所有时间比较逻辑出错。用 SET SESSION x = @@GLOBAL.x 逐个对齐更安全。
init_connect 对 SUPER 账号无效：DBA 账号通常有 SUPER 或 SYSTEM_USER 权限，init_connect 会跳过它们——所以 DBA 脚本才是最大的污染源，服务端兜底保护不了 DBA。
sql_mode 非严格模式不会让 SQL 变慢：这是最反直觉的一点——很多人以为"脏连接"会让查询变慢，但 sql_mode 只影响错误处理策略，不影响执行计划。所以慢查询监控、P99 监控全部失效，只有对账能发现。
max_execution_time 的同类污染：如果 DBA 调试时 SET SESSION max_execution_time=0（关闭超时），这条连接回到池里后，业务侧的"慢查询超时保护"也会被静默关闭——表现是某些 SQL 从 5 秒超时变成 30 秒才超时，但不会报错，只是拖慢下游。

五、结论与可复用清单

核心原则：连接池里的会话变量是"共享状态"，任何 SET SESSION 都必须有对应的"复位"，且复位不能依赖"人记得"。

可复用清单：

[ ] HikariCP 配置 addConnectionCustomizer，checkout 时对齐 sql_mode、time_zone、max_execution_time 到业务基线；
[ ] MySQL 服务端配置 SET GLOBAL init_connect = 'SET SESSION sql_mode = @@GLOBAL.sql_mode' 作为第 2 层兜底；
[ ] DBA 脚本走独立连接（不走业务池），脚本模板强制包含 SET SESSION sql_mode=@OLD + 末尾复位；
[ ] 连接池健康检查任务：每 5 分钟自检 sql_mode 是否包含 STRICT_TRANS_TABLES，异常则上报 + 关闭该连接；
[ ] 对账系统把"金额一致性"纳入每日巡检，不要只依赖应用层异常；
[ ] 代码评审时，任何 SET SESSION / SET GLOBAL 都需要成对出现（保存 + 复位），否则打回。

一句话总结：连接池不是"无状态"的——它是一条有记忆的管道，任何一次 SET SESSION 都是在这条管道里写了一行字，下一个经过的人就会读到它。

赞 0 · 收藏 0 · 评论 0 · 浏览 0

暂无评论。