因数据库连接池“污染”，引发的诡异故障排查

因数据库连接池“污染”，引发的诡异故障排查

温卫斌，就职于中国农业银行研发中心，目前负责云原生应用平台设计与开发，主要关注于分布式、Serverless相关领域。

一、诡异问题

最近小伙伴反映了一个很诡异的问题，就是当应用执行完一些SQL后，后续查询的查询SQL有的可以准确返回，而有的返回记录却数只有1个，出现丢数问题。经过排查日志，发现有个SQL在执行前先执行了SET SQL_SELECT_LIMIT=1。

SQL_SELECT_LIMIT 变量用来指定select返回的最大记录数，具体可参见

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_select_limit

应用组反馈并没有直接调用这个SQL，下意识的觉得应该是MySQL驱动自动发送的。查看其源码，很快就定位到是应用方调用的Statement.setMaxRow方法，对应MySQL驱动中com.mysql.cj.jdbc.StatementImpl类。

private boolean executeInternal(String sql, boolean returnGeneratedKeys) throws SQLException {JdbcConnection locallyScopedConn = this.checkClosed();
...locallyScopedConn.setSessionMaxRows(maybeSelect ? this.maxRows : -1);...}

进一步看locallyScopedConn.setSessionMaxRows方法

com.mysql.cj.jdbc.ConnectionImpl

public void setSessionMaxRows(int max) throws SQLException {    try {        synchronized(this.getConnectionMutex()) {            if (this.session.getSessionMaxRows() != max) {                this.session.setSessionMaxRows(max);                this.session.execSQL((Query)null, "SET SQL_SELECT_LIMIT=" + (this.session.getSessionMaxRows() == -1 ? "DEFAULT" : this.session.getSessionMaxRows()), -1, (NativePacketPayload)null, false, this.nullStatementResultSetFactory, (ColumnDefinition)null, false);            }        }    } catch (CJException var6) {        throw SQLExceptionsMapping.translateException(var6, this.getExceptionInterceptor());    }}

可以看到正是该方法发送了SET SQL_SELECT_LIMIT，正是这个SET语句使得后续该连接执行的SQL查询出来的记录数都只有1条，由于Proxy端的连接是一个连接池，因此下一个用到该连接的SQL也只能查到1条记录，这也是文章开头提到问题的原因所在。

写个测试代码很容易重现：

mysql> select * from user;+----+--------+------+| id | name   | age  |+----+--------+------+|  1 | h2     |   10 ||  2 | mysql  |   19 ||  3 | oracle |   20 |+----+--------+------+3 rows in set (0.00 sec)

       HikariConfig config = new HikariConfig();        config.setPoolName("test");        config.setJdbcUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true&useAffectedRows=true");        config.setUsername("root");        config.setPassword("password");        config.setMaximumPoolSize(1);        config.setMinimumIdle(1);        config.setDriverClassName("com.mysql.cj.jdbc.Driver");
        HikariDataSource hikariDataSource = new HikariDataSource(config);        Connection connection1 = hikariDataSource.getConnection();        Statement statement1 = connection1.createStatement();
        statement1.execute("SET SQL_SELECT_LIMIT=1");       // statement.setMaxRows(1);        ResultSet resultSet1 = statement1.executeQuery("select * from user");        System.out.println("After SET SQL_SELECT_LIMIT=1,first query");        while (resultSet1.next()) {            System.out.println(resultSet1.getString("name"));        }        resultSet1.close();        statement1.close();        connection1.close();//release connection
        Connection connection2 = hikariDataSource.getConnection();        Statement statement2 = connection2.createStatement();        ResultSet resultSet2 = statement2.executeQuery("select * from user");        System.out.println("After SET SQL_SELECT_LIMIT=1,second query");        while (resultSet2.next()) {            System.out.println(resultSet2.getString("name"));        }        resultSet2.close();        statement2.close();        connection2.close();

执行结果：

After SET SQL_SELECT_LIMIT=1,first queryh2After SET SQL_SELECT_LIMIT=1,second queryh2

不过问题并没有结束，因为正常情况下，我们往往不直接调用SET SQL_SELECT_LIMIT，而是调用Statement.setMaRows，那么这种方式下会出现类似问题吗？

写个测试代码，发现竟然不会。为什么呢？

       ...       HikariDataSource hikariDataSource = new HikariDataSource(config);        Connection connection1 = hikariDataSource.getConnection();        Statement statement1 = connection1.createStatement();
        //statement1.execute("SET SQL_SELECT_LIMIT=1");        statement1.setMaxRows(1);        ResultSet resultSet1 = statement1.executeQuery("select * from user");        System.out.println("After setMaxRows,first query");        while (resultSet1.next()) {            System.out.println(resultSet1.getString("name"));        }        resultSet1.close();        statement1.close();        connection1.close();//release connection
        Connection connection2 = hikariDataSource.getConnection();        Statement statement2 = connection2.createStatement();        ResultSet resultSet2 = statement2.executeQuery("select * from user");        System.out.println("After setMaxRows,second query");        while (resultSet2.next()) {            System.out.println(resultSet2.getString("name"));        }        resultSet2.close();        statement2.close();        connection2.close();

执行结果：

After setMaxRow,first queryh2After setMaxRow,second queryh2mysqloracle

这是因为当调用Connection.createStatement创建一个新的Statement的时候，因为MySQL驱动会自动比较当前maxRows值与Session的设置是否一样，新建的Statement的maxRows值为-1，跟当前数据库连接的sessionMaxrows不一样，MySQL驱动自动发送SET SQL_SELECT_LIMIT=DEFAULT SQL来重置该变量值。

也可通过测试代码进行验证：

      ....        HikariDataSource hikariDataSource = new HikariDataSource(config);        Connection connection1 = hikariDataSource.getConnection();        Statement statement1 = connection1.createStatement();        statement1.execute("SET SQL_SELECT_LIMIT=1");       // statement.setMaxRows(1);        ResultSet resultSet1 = statement1.executeQuery("select * from user");        System.out.println("After SET SQL_SELECT_LIMIT=1,first query");        while (resultSet1.next()) {            System.out.println(resultSet1.getString("name"));        }        resultSet1.close();        statement1.close();        connection1.close();//release connection
        Connection connection2 = hikariDataSource.getConnection();        Statement statement2 = connection2.createStatement();        statement2.execute("SET SQL_SELECT_LIMIT=DEFAULT");        ResultSet resultSet2 = statement2.executeQuery("select * from user");        System.out.println("After SQL_SELECT_LIMIT=DEFAULT,second query");        while (resultSet2.next()) {            System.out.println(resultSet2.getString("name"));        }        resultSet2.close();        statement2.close();        connection2.close();

执行结果：

After SET SQL_SELECT_LIMIT=1,first queryh2After SQL_SELECT_LIMIT=DEFAULT,second queryh2mysqloracle

这个问题也让我们再次正视一个问题，数据库连接是有状态的，由于连接池中的连接是复用的，不当的设置会导致连接被“污染”，从而带来很多不容易排查的诡异问题，因为这类SESSION级的设置会在连接的生存期会一直生效。除了SQL_SELECT_LIMIT，常见的还有autocommit、SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ、SET CHARACTER SET等等。