Connection is not available, request timed out - Hikari CP 4.0.3

[lijubsgit]

Our application stack consists of the following

Hikari CP 4.0.3
Mariadb driver 2.7.5
Aurora MySQL 8.0 
AWS ECS Docker 
Spring Boot 2.5

We are noticing Connection is not available, request timed out very frequently in the logs. "Date":"2022-05-26T17:58:10,612" , Failed to validate connection org.mariadb.jdbc.MariaDbConnection@4f35428b ((conn=2965307).Connection.setNetworkTimeout cannot be called on a closed connection). Possibly consider using a shorter maxLifetime value."} "Date":"2022-05-26T17:58:10,613","com.zaxxer.hikari.pool.HikariPool","Msg":"accomDataSource - Timeout failure stats (total=17, active=2, idle=15, waiting=0)"} "Date":"2022-05-26T17:58:10,614",Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: accomDataSource - Connection is not available, request timed out after 237517ms." "Date":"2022-05-26T17:58:10,614",Closing connection org.mariadb.jdbc.MariaDbConnection@4f35428b: (connection is dead)"

The active connections are very low and there are enough number of idle connections present. I could discover similar issues reported in the past, but most of them were related to the wait timeout in the MySQL database. In my case the ide timeout is much less that the wait timeout configured in the database. Therefore that should be not a problem.

Let me know if there are any suggestions ?

The Hikari connection parameters are as follows.

"allowPoolSuspension................................false"
"autoCommit................................true"
"catalog................................none"
"connectionInitSql................................none"
connectionTestQuery................................\"select 1 from dual\""
connectionTimeout................................30000"
"dataSource................................none"
"dataSourceClassName................................none"
"dataSourceJNDI................................none"
"dataSourceProperties................................{password=<masked>, prepStmtCacheSqlLimit=2048, cachePrepStmts=true, prepStmtCacheSize=1000, useServerPrepStmts=true}"
"driverClassName................................\"org.mariadb.jdbc.Driver\""
"exceptionOverrideClassName................................none"
"healthCheckProperties................................{}"}
"healthCheckRegistry................................none"}
"idleTimeout................................600000"}
"initializationFailTimeout................................1"}
"isolateInternalQueries................................false"}
"jdbcUrl................................jdbc:mariadb:aurora//hostname:port/<db name>?useSSL=true&autoReconnect=false&requireSSL=true&verifyServerCertificate=true&serverSslCert=rds-combined-ca-bundle.pem
"keepaliveTime................................0"}
"leakDetectionThreshold................................0"}
"maxLifetime................................1800000"}
"maximumPoolSize................................60"}
"metricRegistry................................none"}
"minimumIdle................................15"}
"Msg":"readOnly................................false"}
validationTimeout................................1000"}

[zeimao77]

Has your problem been solved? I have a similar timeout problem

[icodemo]

Is the connection closed when the query had completed?

[lijubsgit]

The problem is still not resolved @zeimao77. @icodemo the connection is closed by the Spring framework. we are planning to migrate to tomcat jdbc instead

[huyiyu]

I also has similar problem, jstack shows many thread park on handoffQueue timed_waiting.

[zuzuviewer]

I have a similar timeout problem

[catalinbalasoiu]

I have a similar problem as well.. I am seeing that there are many issues raised on this matter, but there's seems to be no solution for this..

[expe-elenigen]

Reducing the maxLifetime to 10 min or less works for me.

[sakonn]

Hello, there I have the same problem. I am using MariaDB version 10.11.6. The maxLifetime is set to 20 minutes and the database wait_timeout to faaar higher value but still getting these error messages. Except for that I have discovered that there is a number of connections starving in the database. So it seems to me that connections are not even closed after a lifetime has expired.

The message I am getting:

| 2024-02-14 09:14:35.318  WARN [,,] 7 --- [_ClusterManager] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection org.mariadb.jdbc.Connection@397a3370 ((conn=412545) Connection.setNetworkTimeout cannot be called on a closed connection). Possibly consider using a shorter maxLifetime value.
| 2024-02-14 09:14:35.318  WARN [,,] 7 --- [_ClusterManager] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection org.mariadb.jdbc.Connection@2897a56b ((conn=412546) Connection.setNetworkTimeout cannot be called on a closed connection). Possibly consider using a shorter maxLifetime value.

But at the same time, there is a connection with that ID shown in the database as existing:

412546  user_name   IP:40624    database    Sleep   1815        NULL    0.000   
412546  user_name   IP:40624    database    Sleep   1815        NULL    0.000   

So I don't know where the problem might but the advised solution to adjust the values have not helped me.