JDBC游标遍历TiDB视图出现游标断开

【 TiDB 使用环境】
服务器硬件:
10.160.60.18 Cpu 40核,256G内存,机械硬盘
10.160.60.19 Cpu 40核,256G内存,机械硬盘
10.160.60.20 Cpu 40核,256G内存,机械硬盘

HAProxy: 2.4.0

SQL:select * from VIEW_PAT_VISIT

JDBC Driver Dependency:
jdbc

数据库连接池配置

  datasource:
    dynamic:
      primary: JHDL2 #设置默认的数据源或者数据源组,默认值即为master
      strict: false #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候回抛出异常,不启动会使用默认数据源.
      druid: #以下是全局默认值,可以全局更改
        # 初始化大小,最小,最大
        initial-size: 10
        min-idle: 3
        max-active: 30
        # 配置获取连接等待超时的时间
        max-wait: 60000
        #指明连接是否被空闲连接回收器(如果有)进行检验,如果检测失败,则连接将被从池中去除
        test-while-idle: true
        # SQL查询,用来验证从连接池取出的连接
        validation-query: SELECT 1
        # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
        time-between-eviction-runs-millis: 6000
        # 配置一个连接在池中最小生存的时间,单位是毫秒
        min-evictable-idle-time-millis: 300000
        keep-alive: true
        test-on-borrow: false
        test-on-return: false
      datasource:
        #系统管理数据库
        JHDL2:
          url: jdbc:mysql://jhmk08:3390/JHDL4T?serverTimezone=GMT%2B8&useUnicode=true&useSSL=false&autoReconnect=true&rewriteBatchedStatements=true&allowMultiQueries=true&net_read_timeout=300&net_write_timeout=300
          #url: jdbc:mysql://jhmk08:4000/JHDL4T?serverTimezone=GMT%2B8&useUnicode=true&useSSL=false&autoReconnect=true&rewriteBatchedStatements=true&allowMultiQueries=true
          username: root
          password: ******
          driver-class-name: com.mysql.jdbc.Driver

游标遍历代码:

targetJdbcTemplate.execute("set @@session.tidb_isolation_read_engines = 'tikv'");
targetJdbcTemplate.query(con -> {
    PreparedStatement preparedStatement =
            con.prepareStatement(jdbcSql,
                    ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_READ_ONLY);
    preparedStatement.setFetchSize(Integer.MIN_VALUE);
    preparedStatement.setFetchDirection(ResultSet.FETCH_FORWARD);
    return preparedStatement;
}, rsJdbc -> {
    ...
});

【概述】 场景 + 问题概述
该视图总记录数为3500万左右,在遍历时高频出现如下异常导致游标断开:

【背景】 做过哪些操作

set%20param

视图中的相关表有加TiFlash副本。

【TiDB 版本】 5.2.1

【附件】 相关日志及监控

2021-09-17 15:08:43,316 [pool-4-thread-2] ERROR com.alibaba.druid.pool.DruidDataSource - discard connection
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Application was streaming results when the connection failed. Consider raising value of 'net_write_timeout' on the server.
	at sun.reflect.GeneratedConstructorAccessor61.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:403)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3517)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3417)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3860)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:864)
	at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1992)
	at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:366)
	at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:346)
	at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6303)
	at com.alibaba.druid.filter.FilterChainImpl.resultSet_next(FilterChainImpl.java:917)
	at com.alibaba.druid.wall.WallFilter.resultSet_next(WallFilter.java:1312)
	at com.alibaba.druid.filter.FilterChainImpl.resultSet_next(FilterChainImpl.java:913)
	at com.alibaba.druid.filter.FilterAdapter.resultSet_next(FilterAdapter.java:1918)
	at com.alibaba.druid.filter.FilterChainImpl.resultSet_next(FilterChainImpl.java:913)
	at com.alibaba.druid.proxy.jdbc.ResultSetProxyImpl.next(ResultSetProxyImpl.java:881)
	at com.alibaba.druid.pool.DruidPooledResultSet.next(DruidPooledResultSet.java:68)
	at com.jhdl.cdc.service.OracleService.lambda$tidb2HBas2$1(OracleService.java:474)
	at org.springframework.jdbc.core.JdbcTemplate$RowCallbackHandlerResultSetExtractor.extractData(JdbcTemplate.java:1607)
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:679)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:694)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)
	at com.jhdl.cdc.service.OracleService.tidb2HBas2(OracleService.java:458)
	at com.jhdl.cdc.controller.CdcController.lambda$tidbTest2$1(CdcController.java:123)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 310 bytes, read 88 bytes before connection was unexpectedly lost.
	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2969)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3477)
	... 28 common frames omitted

看描述是在 tidb 前端使用了 haproxy ,麻烦尝试下应用直连 TiDB 是否也会出现这个现象,看下是否可能是 haproxy 导致的问题。

把jdbc 降版本试试 ,降到 5.1.8

image

尝试降到5.1.8的JDBC驱动后,程序启动时出现如下错误:

555

8

再换成5.1.49版本的JDBC驱动,可以正常启动,我再看看有没出现游标断开的异常…

感觉不是驱动包的问题,我用5.1.49版本的驱动包,还是会出现同样的问题:

再试下绕开HAProxy直接连TiDB(保持5.1.49版本驱动包)…

请问绕开 haproxy 直连 tidb 还会出现这个现象吗?如果直连没有问题,那说明 haproxy 有问题,可以尝试调整下 haproxy 中一些网络相关的 timeout 参数。

问一下,问题解决了吗?

TiFlash 的表游标遍历异常 可以参考一下这个帖子中给出的建议