No operations allowed after connection closed数据库连接关闭后不允许操作 HikariDataSource 数据库连接池相关配置

2021-12-10 13:48:53.114  INFO 31435 --- [trap-executor-0] c.n.d.s.r.aws.ConfigClusterResolver      : Resolving eureka endpoints via configuration
2021-12-10 13:48:56.766  INFO 31435 --- [pool-8-thread-1] c.w.s.c.FeignBasicAuthRequestInterceptor : eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzUxMiJ9.eyJzdWIiOiJBcHBBY2NvdW50OnNwcmluZ2Nsb3VkIiwiaWF0IjoxNjM5MTE1MDM2LCJleHAiOjE2MzkxMTg2MzZ9._1b5tYYXNJcjGpNPWFE0Q_eFhR3--Nh1EKzKkkLg4RUrz6ebuMDGPgIokjrf8rhMQHmk6VA5sjUm4fCeGmh_PA
2021-12-10 13:48:57.346  WARN 31435 --- [nio-9810-exec-4] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@21313314 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2021-12-10 13:49:02.354  WARN 31435 --- [nio-9810-exec-4] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@607ba4d8 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2021-12-10 13:49:07.360  WARN 31435 --- [nio-9810-exec-4] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@7243a0ed (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2021-12-10 13:49:12.367  WARN 31435 --- [nio-9810-exec-4] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@f1b78d1 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2021-12-10 13:49:17.375  WARN 31435 --- [nio-9810-exec-4] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@2e35b707 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2021-12-10 13:49:22.382  WARN 31435 --- [nio-9810-exec-4] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@129692c6 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2021-12-10 13:49:22.473 ERROR 31435 --- [nio-9810-exec-4] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is 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 30102ms.] with root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_251]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_251]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_251]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_251]
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:403) ~[mysql-connector-java-5.1.49.jar!/:5.1.49]
	at com.mysql.jdbc.Util.getInstance(Util.java:386) ~[mysql-connector-java-5.1.49.jar!/:5.1.49]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) ~[mysql-connector-java-5.1.49.jar!/:5.1.49]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) ~[mysql-connector-java-5.1.49.jar!/:5.1.49]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) ~[mysql-connector-java-5.1.49.jar!/:5.1.49]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) ~[mysql-connector-java-5.1.49.jar!/:5.1.49]
	at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1182) ~[mysql-connector-java-5.1.49.jar!/:5.1.49]
	at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1177) ~[mysql-connector-java-5.1.49.jar!/:5.1.49]
	at com.mysql.jdbc.ConnectionImpl.setNetworkTimeout(ConnectionImpl.java:5464) ~[mysql-connector-java-5.1.49.jar!/:5.1.49]
	at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:560) ~[HikariCP-3.4.5.jar!/:na]
	at com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:173) ~[HikariCP-3.4.5.jar!/:na]
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186) ~[HikariCP-3.4.5.jar!/:na]
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162) ~[HikariCP-3.4.5.jar!/:na]
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-3.4.5.jar!/:na]
	at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE]
	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE]
	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:612) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:763) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:844) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE]

问题背景：

项目运行过程中，会偶尔出现页面查不出数据的情况，刷新页面之后再进行查询可以查出数据，后来查看运行日志，发现了以上错误日志；然后写了一个定时任务，定时连接数据库进行查询，结果每隔一段时间就会报以上错误日志。

问题分析：

根据日志信息，可以很清楚的确定问题：数据库连接池中的某个连接还在，然后有数据库请求进来之后，数据库连接池将此连接分配给当前请求，但是此连接池中的连接与数据库的连接已经被数据库断开，最终导致当前连接请求数据库时，一直等待，直到超时。

解决办法：

初步判断，数据库连接池配置的连接的最大生命周期或者空闲最大时间比数据库配置的空闲时间要大，所以连接空闲时间达到数据库配置之后，数据库会主动把当前连接断开，但是连接池的连接空闲最大时间还没到，所以最终导致当前连接失效。经过验证，确实如此，数据库配置的空闲最大时间是20分钟，而我们代码中的数据库连接池配置的最大生命周期是30分钟，所以导致以上问题，既然如此，我们只需要修改数据库连接池的连接的最大生命时间小于20分钟，连接空闲时间小于20分钟即可。

修改后的配置：

spring.datasource.cdn.minimum-idle=5
spring.datasource.cdn.maximum-pool-size=100
spring.datasource.cdn.auto-commit=true
spring.datasource.cdn.idle-timeout=30000
spring.datasource.cdn.pool-name=DatebookHikariCP
spring.datasource.cdn.max-lifetime=600000
spring.datasource.cdn.connection-timeout=30000
spring.datasource.cdn.connection-test-query=SELECT 1

小插曲：

最开始的时候，我们修改数据库连接池的连接空闲时间是一直不生效的，后来无意间发现，由于我们将springboot进行了升级，从1.5版本升到了2.3版本，升级之后，springboot默认的数据源从

org.apache.tomcat.jdbc.pool.DataSource变成了com.zaxxer.hikari.HikariDataSource，两个数据源的数据库连接池的配置参数是不一致的，而我们升级springboot版本之后，并没有修改数据库连接池的配置参数，所以，HikariDataSource的连接池参数数据用的都是缺省值，而最大生命时间参数的缺省值是30分钟，所以导致我们修改原来DataSource数据库连接池的参数是永远不会生效的。最后，我们将数据库连接池的配置参数改为HikariDataSource需要的配置就可以了（实在是很无奈）。

HikariDataSource 数据库连接池相关配置

#数据源类型
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
#连接池名称，默认HikariPool-1
spring.datasource.hikari.pool-name=KevinHikariPool
#最大连接数，小于等于0会被重置为默认值10；大于零小于1会被重置为minimum-idle的值
spring.datasource.hikari.maximum-pool-size=12
#连接超时时间:毫秒，小于250毫秒，否则被重置为默认值30秒
spring.datasource.hikari.connection-timeout=60000
#最小空闲连接，默认值10，小于0或大于maximum-pool-size，都会重置为maximum-pool-size
spring.datasource.hikari.minimum-idle=10
#空闲连接超时时间，默认值600000（10分钟），大于等于max-lifetime且max-lifetime>0，会被重置为0；不等于0且小于10秒，会被重置为10秒。
# 只有空闲连接数大于最大连接数且空闲时间超过该值，才会被释放
spring.datasource.hikari.idle-timeout=500000
#连接最大存活时间.不等于0且小于30秒，会被重置为默认值30分钟.设置应该比mysql设置的超时时间短
spring.datasource.hikari.max-lifetime=540000
#连接测试查询
spring.datasource.hikari.connection-test-query=SELECT 1

name	构造器默认值	默认配置validate之后的值	validate重置
minIdle	-1	10	minIdle<0或者minIdle>maxPoolSize,则被重置为maxPoolSize
maxPoolSize	-1	10	如果maxPoolSize小于1，则会被重置。当minIdle<=0被重置为DEFAULT_POOL_SIZE则为10;如果minIdle>0则重置为minIdle的值
maxLifetime	MINUTES.toMillis(30) = 1800000	1800000	如果不等于0且小于30秒则会被重置回30分钟
connectionTimeout	SECONDS.toMillis(30) = 30000	30000	如果小于250毫秒，则被重置回30秒
validationTimeout	SECONDS.toMillis(5) = 5000	5000	如果小于250毫秒，则会被重置回5秒
loginTimeout	10	30	Math.max(1, (int) MILLISECONDS.toSeconds(500L + connectionTimeout))，为connectionTimeout+500ms转为秒数取整 与 1 取最大者
idleTimeout	MINUTES.toMillis(10) = 600000	600000	如果idleTimeout+1秒>maxLifetime 且 maxLifetime>0，则会被重置为0；如果idleTimeout!=0且小于10秒，则会被重置为10秒
leakDetectionThreshold	0	0	如果大于0且不是单元测试，则进一步判断：(leakDetectionThreshold < SECONDS.toMillis(2) or (leakDetectionThreshold > maxLifetime && maxLifetime > 0)，会被重置为0 . 即如果要生效则必须>0，而且不能小于2秒，而且当maxLifetime > 0时不能大于maxLifetime
initializationFailTimeout	1	1	-
isAutoCommit	TRUE	TRUE	-
isReadOnly	FALSE	fasle	-
isAllowPoolSuspension	FALSE	FALSE	-
isIsolateInternalQueries	FALSE	FALSE	-
isRegisterMbeans	FALSE	FALSE	-
sealed	FALSE	TRUE	运行启动后这个标志为true，表示不再运行修改
poolName	null	HikariPool-1	-
catalog	null	null	-
connectionInitSql	null	null	-
connectionTestQuery	null	null	-
dataSourceClassName	null	null	-
schema	null	null	-
transactionIsolationName	null	null	-
dataSource	null	null	-
dataSourceProperties	{}	{}	-
threadFactory	null	null	-
scheduledExecutor	null	null	-
metricsTrackerFactory	null	null	-
metricRegistry	null	null	-
healthCheckRegistry	null	null	-
healthCheckProperties	{}	{}	-
validation-query			validationQuery属性：用来验证数据库连接的语句，这个语句至少是返回一条数据的查询语句。每种数据库都有自己的验证语句。以下是不同数据库对应的验证语句：

validation-query配置数据库时，属性validationQuery默认值为“select 1”，对于oracle值应为“select 1 from dual”

validationQuery属性：用来验证数据库连接的语句，这个语句至少是返回一条数据的查询语句。每种数据库都有自己的验证语句。以下是不同数据库对应的验证语句：

DataBase	validationQuery
hsqldb	select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
Oracle	select 1 from dual
DB2	select 1 from sysibm.sysdummy1
MySql	select 1
Microsoft SqlServer	select1
postgresql	select version()
ingres	select 1
derby	values 1
H2	select 1