【 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:
数据库连接池配置
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万左右，在遍历时高频出现如下异常导致游标断开：
【背景】 做过哪些操作
视图中的相关表有加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
