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-110minIdle<0或者minIdle>maxPoolSize,则被重置为maxPoolSize
maxPoolSize-110如果maxPoolSize小于1,则会被重置。当minIdle<=0被重置为DEFAULT_POOL_SIZE则为10;如果minIdle>0则重置为minIdle的值
maxLifetimeMINUTES.toMillis(30) = 18000001800000如果不等于0且小于30秒则会被重置回30分钟
connectionTimeoutSECONDS.toMillis(30) = 3000030000如果小于250毫秒,则被重置回30秒
validationTimeoutSECONDS.toMillis(5) = 50005000如果小于250毫秒,则会被重置回5秒
loginTimeout1030Math.max(1, (int) MILLISECONDS.toSeconds(500L + connectionTimeout)),为connectionTimeout+500ms转为秒数取整 与 1 取最大者
idleTimeoutMINUTES.toMillis(10) = 600000600000如果idleTimeout+1秒>maxLifetime 且 maxLifetime>0,则会被重置为0;如果idleTimeout!=0且小于10秒,则会被重置为10秒
leakDetectionThreshold00如果大于0且不是单元测试,则进一步判断:(leakDetectionThreshold < SECONDS.toMillis(2) or (leakDetectionThreshold > maxLifetime && maxLifetime > 0),会被重置为0 . 即如果要生效则必须>0,而且不能小于2秒,而且当maxLifetime > 0时不能大于maxLifetime
initializationFailTimeout11-
isAutoCommitTRUETRUE-
isReadOnlyFALSEfasle-
isAllowPoolSuspensionFALSEFALSE-
isIsolateInternalQueriesFALSEFALSE-
isRegisterMbeansFALSEFALSE-
sealedFALSETRUE运行启动后这个标志为true,表示不再运行修改
poolNamenullHikariPool-1-
catalognullnull-
connectionInitSqlnullnull-
connectionTestQuerynullnull-
dataSourceClassNamenullnull-
schemanullnull-
transactionIsolationNamenullnull-
dataSourcenullnull-
dataSourceProperties{}{}-
threadFactorynullnull-
scheduledExecutornullnull-
metricsTrackerFactorynullnull-
metricRegistrynullnull-
healthCheckRegistrynullnull-
healthCheckProperties{}{}-
validation-query  validationQuery属性:用来验证数据库连接的语句,这个语句至少是返回一条数据的查询语句。每种数据库都有自己的验证语句。以下是不同数据库对应的验证语句:

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

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

DataBasevalidationQuery
hsqldbselect 1 from INFORMATION_SCHEMA.SYSTEM_USERS
Oracleselect 1 from dual
DB2select 1 from sysibm.sysdummy1
MySqlselect 1
Microsoft SqlServerselect1
postgresqlselect version()
ingresselect 1
derbyvalues 1
H2select 1
Logo

华为开发者空间,是为全球开发者打造的专属开发空间,汇聚了华为优质开发资源及工具,致力于让每一位开发者拥有一台云主机,基于华为根生态开发、创新。

更多推荐