一次OceanBase数据库 Connection reset
- 基础环境 1.数据库连接池1.1.16
2.mysql驱动版本号5.1.47
3.数据库dao操作spring+mybatis
4.数据库mysql - 软件基础框架
- 其中APP的数据库连接池使用druid，druid的配置后文体现，配置了空闲连接池的探测，1分钟进行一次数据空闲连接销毁，keepAlive的探测时间默认为2分钟。SLB到dbproxy的默认探测时间是15分钟，dbproxy的探测时间是12分钟。
- 业务代码 public class CheckBatchStatus implements Tasklet {
private static final Logger logger = LoggerFactory.getLogger(CheckBatchStatus.class);
@Autowired
private AccessRepository accessRepository;
@Override
public RepeatStatus execute(StepContribution contribution, ChunkContext chunkContext) {
boolean runing = true;
int count = 0;
String status = null;
while (runing && count < 30) {
long counts = accessRepository.count();
logger.info("{}", counts);
}
return RepeatStatus.FINISHED;
}
} - 使用了springbatch的Tasklet任务类，出错就在其中的while循环。
- 出错现象Caused by: java.sql.SQLException: Server connection execute error: Connection reset
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242)
at com.p6spy.engine.wrapper.PreparedStatementWrapper.execute(PreparedStatementWrapper.java:362)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167) - druid的主要配置信息
- "initialSize" value="5"/>
"maxActive" value="200"/>
"minIdle" value="5"/>
"validationQuery" value="SELECT 1"/>
"testOnBorrow" value="false"/>
"testOnReturn" value="false"/>
"testWhileIdle" value="true"/>
"testWhileIdle" value="true"/> - 打开druid的监控显示，数据库连接keepAliveCount的次数每隔两分钟增加5，由此可见数据库连接探测的生效。
- 原因分析 //springbatch的Tasklet开启事务,导致后续的数据库连接的connection对象都是一个
TransactionStatus status = this.transactionManager.getTransaction(this);
T result;
try {
result = action.doInTransaction(status);
}
catch (RuntimeException | Error ex) {
// Transactional code threw application exception -> rollback
rollbackOnException(status, ex);
throw ex;
}
