背景:
因为是小公司,Springboot集成多数据源事物就使用了atomikos框架,然后在线上发现报CommunicationsException异常的错误,因为晚上没有人使用,所以我猜是因为长时间没有连数据库导致的。错误日志如下:
com.mysql.cj.jdbc.exceptions.CommunicationsException: The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:371)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483)
at com.atomikos.jdbc.AtomikosXAPooledConnection.testUnderlyingConnection(AtomikosXAPooledConnection.java:104)
at com.atomikos.datasource.pool.AbstractXPooledConnection.createConnectionProxy(AbstractXPooledConnection.java:55)
at com.atomikos.datasource.pool.ConnectionPoolWithConcurrentValidation.concurrentlyTryToUse(ConnectionPoolWithConcurrentValidation.java:59)
at com.atomikos.datasource.pool.ConnectionPoolWithConcurrentValidation.retrieveFirstAvailableConnection(ConnectionPoolWithConcurrentValidation.java:41)
at com.atomikos.datasource.pool.ConnectionPool.retrieveFirstAvailableConnectionAndGrowPoolIfNecessary(ConnectionPool.java:153)
at com.atomikos.datasource.pool.ConnectionPool.findOrWaitForAnAvailableConnection(ConnectionPool.java:141)
at com.atomikos.datasource.pool.ConnectionPool.borrowConnection(ConnectionPool.java:132)
at com.atomikos.jdbc.AbstractDataSourceBean.getConnection(AbstractDataSourceBean.java:346)
at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79)
at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:80)
at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:67)
at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:348)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:89)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:64)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:336)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:158)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:110)
at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)
at com.sun.proxy.$Proxy233.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:154)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:142)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:425)
at com.sun.proxy.$Proxy116.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:164)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:152)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
at com.sun.proxy.$Proxy131.getNextDayScheduleList(Unknown Source)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:95)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:750)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:783)
at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:709)
at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:639)
at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:987)
at com.mysql.cj.NativeSession.execSQL(NativeSession.java:666)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:930)
... 66 common frames omitted
druid和atomikos的版本是
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.2.20</version> </dependency>
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jta-atomikos</artifactId> <version>2.7.18</version> </dependency>
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.28</version> </dependency>
经过研究提供以下两种解决方式:
1.如果使用druid连接池
@SneakyThrows
@Bean("dataSourceNetwork")
public DataSource dataSourceNetwork() {
// 设置数据库连接
try (DruidXADataSource druidXADataSource = new DruidXADataSource()) {
druidXADataSource.setDbType(com.alibaba.druid.DbType.mysql);
druidXADataSource.setDriverClassName(dataSourceNetworkParam.getDriverClassName());
druidXADataSource.setUrl(dataSourceNetworkParam.getUrl());
druidXADataSource.setUsername(dataSourceNetworkParam.getUsername());
druidXADataSource.setPassword(dataSourceNetworkParam.getPassword());
//配置初始连接
druidXADataSource.setInitialSize(5);
//配置最小连接
druidXADataSource.setMinIdle(10);
//配置最大连接
druidXADataSource.setMaxActive(20);
//连接等待超时时间
druidXADataSource.setMaxWait(60000);
//间隔多久进行检测,关闭空闲连接
druidXADataSource.setTimeBetweenEvictionRunsMillis(60000);
//一个连接最小生存时间
druidXADataSource.setMinEvictableIdleTimeMillis(300000);
druidXADataSource.setMaxEvictableIdleTimeMillis(900000);
//用来检测是否有效的sql
druidXADataSource.setValidationQuery("select 'x'");
druidXADataSource.setTestWhileIdle(true);
//申请连接时执行validationQuery检测连接是否有效,配置为true会降低性能
druidXADataSource.setTestOnBorrow(false);
//归还连接时执行validationQuery检测连接是否有效,配置为true会降低性能
druidXADataSource.setTestOnReturn(false);
//连接等待超时时间 单位为毫秒 缺省启用公平锁,
//并发效率会有所下降, 如果需要可以通过配置useUnfairLock属性为true使用非公平锁
druidXADataSource.setUseUnfairLock(true);
//打开PSCache,并指定每个连接的PSCache大小启用poolPreparedStatements后,
//PreparedStatements 和CallableStatements 都会被缓存起来复用,
//即相同逻辑的SQL可以复用一个游标,这样可以减少创建游标的数量。
druidXADataSource.setPoolPreparedStatements(false);
druidXADataSource.setMaxPoolPreparedStatementPerConnectionSize(-1);
druidXADataSource.setUseGlobalDataSourceStat(true);
// 事务管理器
AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
atomikosDataSourceBean.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
atomikosDataSourceBean.setXaDataSource(druidXADataSource);
atomikosDataSourceBean.setUniqueResourceName("dataSourceNetwork");
atomikosDataSourceBean.setMinPoolSize(0);// 这一行代码就是解决八小时连接问题
atomikosDataSourceBean.setMaxPoolSize(20);
atomikosDataSourceBean.setBorrowConnectionTimeout(60);
atomikosDataSourceBean.setTestQuery("SELECT 1");
return atomikosDataSourceBean;
}
}
关键代码:atomikosDataSourceBean.setMinPoolSize(0);
2.如果未使用druid作为连接池,使用的是MySQL默认的连接池,代码如下
@SneakyThrows
@Primary
@Bean("dataSourcePr")
public DataSource dataSourcePr() {
// 设置数据库连接
MysqlXADataSource dataSource = new MysqlXADataSource();
dataSource.setUrl(dataSourcePrParam.getDbUrl());
dataSource.setUser(dataSourcePrParam.getUsername());
dataSource.setPassword(dataSourcePrParam.getPassword());
dataSource.setPinGlobalTxToPhysicalConnection(true);
// 事务管理器
AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
atomikosDataSourceBean.setXaDataSource(dataSource);
atomikosDataSourceBean.setUniqueResourceName("dataSourcePr");
atomikosDataSourceBean.setMinPoolSize(5);
atomikosDataSourceBean.setMaxPoolSize(20);
atomikosDataSourceBean.setBorrowConnectionTimeout(60);
atomikosDataSourceBean.setTestQuery("SELECT 1");// 重点
atomikosDataSourceBean.setMaintenanceInterval(28000);
return atomikosDataSourceBean;
}
关键代码是:atomikosDataSourceBean.setTestQuery("SELECT 1");
结论:
使用MySQL默认数据源连接池以上配置是完全没有问题的;但是如果使用druid作为连接池,那么只能保证说我使用的版本暂时还没发现问题,不能保证druid其他版本不会出现这种问题。
参考文章: