最近在用Spring Boot + Mybatis搭建后台服务提供给Android端;发现隔几分钟后再去请求服务出现如下错误:
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@21c910d8] was not registered for synchronization because synchronization is not active
2018-05-07 22:58:32.034 WARN 24096 --- [nio-8090-exec-5] com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@5f40b2cd (No operations allowed after connection closed.)
2018-05-07 22:58:37.035 WARN 24096 --- [nio-8090-exec-5] com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@5a2d567 (No operations allowed after connection closed.)
2018-05-07 22:58:42.041 WARN 24096 --- [nio-8090-exec-5] com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@1b10bcf8 (No operations allowed after connection closed.)
2018-05-07 22:58:47.043 WARN 24096 --- [nio-8090-exec-5] com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@5bffdf24 (No operations allowed after connection closed.)
2018-05-07 22:58:52.045 WARN 24096 --- [nio-8090-exec-5] com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@568cb249 (No operations allowed after connection closed.)
2018-05-07 22:58:57.047 WARN 24096 --- [nio-8090-exec-5] com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@6271f224 (No operations allowed after connection closed.)
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@21c910d8]
2018-05-07 22:58:57.061 ERROR 24096 --- [nio-8090-exec-5] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30014ms.
### The error may exist in file [F:\03 JAVA\luxihe\target\classes\mybatis\mapper\AttendanceMapper.xml]
### The error may involve com.lanweihong.dao.IAttendanceDao.queryAll
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30014ms.] with root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
......
后来查询了相关资料才知道是因为MySQL 5.0以后针对超长时间连接数据库做了处理,就是MySQL服务器默认的wait_timeout
是8小时,如果一个connection空闲超过8小时,MySQL将自动断开该connection,而连接池并不知道该connection已经失效,如果此时还请求connection,连接池将提供该失效的connection,最终导致异常。
所以配置datasource时需要配置相应的连接池参数,定时去检查连接的有效性,定时清理无效的连接。
解决方法:
在application.yml
中添加配置:
spring:
datasource:
dbcp2:
validation-query: SELECT 1
time-between-eviction-runs-millis: 18800
如果使用的是tomcat
连接池,那么修改dbcp2
为tomcat
即可。