最近在用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连接池,那么修改dbcp2tomcat即可。

文章目录