Hikaricp: Pool seems to be handing out dead connections

[imran-arshad]

Environment

HikariCP version: 3.1.0
JDK version     : 1.8.0_65
Database        : MySQL
Driver version  : 6.0.6

Hi guys,

I am having a hard time figuring out this issue. When my app fetches a connection from the Data Source, sometimes i get an error Connection is not available, request timed out after 2012ms.. I have tried to replicate the issue on my local environment, but it never happens. On the server it starts happening a few minutes after the deployment. Not sure if it is an issue on the server or my app. All of the connections are created and used in a try-with-resources block.

Any help is greatly appreciated. Thanks!

My logs (notice the ERROR towards the end):

00:05:42.731 [main] WARN com.zaxxer.hikari.HikariConfig - HikariPool-1 - idleTimeout is close to or more than maxLifetime, disabling it.
00:05:42.732 [main] DEBUG com.zaxxer.hikari.HikariConfig - HikariPool-1 - configuration:
00:05:42.735 [main] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
00:05:42.735 [main] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................true
00:05:42.735 [main] DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................none
00:05:42.735 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............none
00:05:42.736 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery.............none
00:05:42.736 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............2000
00:05:42.736 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSource......................none
00:05:42.736 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName.............none
00:05:42.736 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI..................none
00:05:42.739 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties............{cacheServerConfiguration=true, useServerPrepStmts=true, elideSetAutoCommits=true, cachePrepStmts=true, rewriteBatchedStatements=true, cacheResultSetMetadata=true, useLocalSessionState=true, maintainTimeStats=false, prepStmtCacheSize=250, prepStmtCacheSqlLimit=2048, password=, useLocalTransactionState=true}
00:05:42.739 [main] DEBUG com.zaxxer.hikari.HikariConfig - driverClassName................."com.mysql.cj.jdbc.Driver"
00:05:42.740 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties...........{}
00:05:42.740 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry.............none
00:05:42.740 [main] DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout.....................0
00:05:42.740 [main] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailTimeout.......1
00:05:42.740 [main] DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries..........false
00:05:42.740 [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl.........................
00:05:42.741 [main] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........2000
00:05:42.741 [main] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................300000
00:05:42.741 [main] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................5
00:05:42.741 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................none
00:05:42.741 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........none
00:05:42.741 [main] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................5
00:05:42.742 [main] DEBUG com.zaxxer.hikari.HikariConfig - password........................
00:05:42.742 [main] DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"HikariPool-1"
00:05:42.742 [main] DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
00:05:42.742 [main] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
00:05:42.742 [main] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor...............none
00:05:42.742 [main] DEBUG com.zaxxer.hikari.HikariConfig - schema..........................none
00:05:42.743 [main] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................internal
00:05:42.743 [main] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............default
00:05:42.743 [main] DEBUG com.zaxxer.hikari.HikariConfig - username........................
00:05:42.743 [main] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............1000
00:05:42.745 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
00:15:43.398 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=5, active=0, idle=5, waiting=0)
00:16:13.399 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=5, active=0, idle=5, waiting=0)
00:16:43.399 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=5, active=0, idle=5, waiting=0)
00:17:13.399 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=5, active=0, idle=5, waiting=0)
00:17:43.400 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=5, active=0, idle=5, waiting=0)
00:18:13.400 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=5, active=0, idle=5, waiting=0)
00:18:43.400 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=5, active=0, idle=5, waiting=0)
00:19:13.401 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=5, active=0, idle=5, waiting=0)
00:19:43.401 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=5, active=0, idle=5, waiting=0)
00:20:13.401 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=5, active=0, idle=5, waiting=0)
00:20:26.642 [HikariPool-1 connection closer] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@2afaea13: (connection has passed maxLifetime)
00:20:26.898 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@1eea7ae
00:20:27.895 [http-nio-8080-exec-4] WARN com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@6bc7e028 (No operations allowed after connection closed.)
00:20:27.895 [HikariPool-1 connection closer] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@6bc7e028: (connection is dead)
00:20:27.900 [HikariPool-1 connection closer] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@6bc7e028 failed
java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:526)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:505)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:479)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:489)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:66)
at com.mysql.cj.jdbc.ConnectionImpl.setNetworkTimeout(ConnectionImpl.java:4395)
at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:548)
at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:131)
at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1(HikariPool.java:441)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.cj.core.exceptions.ConnectionIsClosedException: No operations allowed after connection closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:54)
at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:93)
at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:133)
at com.mysql.cj.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:931)
at com.mysql.cj.jdbc.ConnectionImpl.setNetworkTimeout(ConnectionImpl.java:4392)
... 6 common frames omitted
00:20:28.176 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@261cc047
00:20:28.176 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - After adding stats (total=5, active=1, idle=4, waiting=0)
00:20:28.896 [http-nio-8080-exec-4] WARN com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@5a66ab13 (No operations allowed after connection closed.)
00:20:28.897 [HikariPool-1 connection closer] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@5a66ab13: (connection is dead)
00:20:28.897 [http-nio-8080-exec-4] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Timeout failure stats (total=4, active=0, idle=4, waiting=0)
00:20:28.897 [HikariPool-1 connection closer] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@5a66ab13 failed
java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:526)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:505)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:479)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:489)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:66)
at com.mysql.cj.jdbc.ConnectionImpl.setNetworkTimeout(ConnectionImpl.java:4395)
at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:548)
at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:131)
at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1(HikariPool.java:441)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.cj.core.exceptions.ConnectionIsClosedException: No operations allowed after connection closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:54)
at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:93)
at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:133)
at com.mysql.cj.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:931)
at com.mysql.cj.jdbc.ConnectionImpl.setNetworkTimeout(ConnectionImpl.java:4392)
... 6 common frames omitted
00:20:28.897 [http-nio-8080-exec-4] ERROR HikariPool-1 - Connection is not available, request timed out after 2012ms.
00:20:29.173 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@628c700c
00:20:29.173 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - After adding stats (total=5, active=1, idle=4, waiting=0)

[imran-arshad]

I have gone through most of the related issues here and on Stackoverflow, and ended up reducing the connectionTimeout and validationTimeout but that didn't help.
Not sure if the warning in the beginning WARN com.zaxxer.hikari.HikariConfig - HikariPool-1 - idleTimeout is close to or more than maxLifetime, disabling it. has something to do with it

[brettwooldridge]

@imran-arshad The warning idleTimeout is close to or more than maxLifetime... is meaningless. We should probably not emit it in this case, because when minimumIdle == maximumPoolSize, idle connection retirement is disabled.

What is the MySQL wait_timeout? Is there a firewall, IPS, or load-balancer between the application and MySQL?

What I would like to see, in terms of debug logging, is all Added connection..., Closing connection..., and Failed to validate connection... messages. In particular, what I am interested in is matching up the connection creation/destruction events by object identity, e.g. ConnectionImpl@261cc047.

This will tell us exactly what the lifetimes really are, and if there is a pattern among the connections that are discovered as dead during validation.

[imran-arshad]

@brettwooldridge I have attached all the logs so far since deployment around 17 hours ago (i took out the application specific logs but all of the logs related to Hikari are there.

My Mysql database is on AWS RDS, and the application is running in a tomcat docker container on Azure. The database has a firewall, and the app server also has an outbound firewall in place. The wait_timeout on the Mysql server is the default 28800.

I also have another application running in the same tomcat container, which uses the Tomcat JNDI datasource for pooling (and connects to the same RDS instance), and it does not have issues with the connections (so probably not a networking issue). Not sure if having a JNDI resource in another application running in the same container has something to do with it.

Thanks a lot!

hikari_logs.txt

[brettwooldridge]

I grabbed just a few Added/Failed pairs, based object identity, e.g. ConnectionImpl@6bc7e028:

00:15:37.014 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@6bc7e028
00:20:27.895 [http-nio-8080-exec-4] WARN com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@6bc7e028 (No operations allowed after connection closed.)

00:15:38.201 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@5a66ab13
00:20:28.896 [http-nio-8080-exec-4] WARN com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@5a66ab13 (No operations allowed after connection closed.)

00:45:07.914 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@6971d4e4
00:50:05.476 [http-nio-8080-exec-10] WARN com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@6971d4e4 (No operations allowed after connection closed.)

01:00:00.516 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@74163014
01:04:55.740 [http-nio-8080-exec-12] WARN com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@74163014 (No operations allowed after connection closed.)

01:29:33.968 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@31e4003a
01:34:30.884 [http-nio-8080-exec-18] WARN com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@31e4003a (No operations allowed after connection closed.)

Now comparing the timestamps:

00:20:27.895 - 00:15:37.014 = ~4m50s
00:20:28.896 - 00:15:38.201 = ~4m50s
00:50:05.476 - 00:45:07.914 = ~4m58s
01:04:55.740 - 01:00:00.516 = ~4m55s
01:34:30.884 - 01:29:33.968 = ~4m47s

These are to be expected. Basically, when you set a 5min maxLifetime, HikariCP injects some minor random variation to ensure that not all connections are retired at the same instant.

But what is also clear is that by 4m50s, a substantial number of connections are already dead. This to me implies there is some infrastructure timeout occurring, possibly at the firewall.

The reason that Tomcat is not encountering the issue is that Tomcat runs a validation query on all connections once a minute -- which is keeping the connections alive.

Why doesn't HikariCP do that? Because reasons.

If the DB is on AWS and the App on Azure, then a couple of things:

• 1000ms validation timeout provides a pretty small margin of error given the amount of infrastructure between those two services.
• Accessing RDS from outside AWS definitely has idle connection timeout limitations. See this guide for help.

It is my suspicion that the AWS timeout could be as short as 1 minute, but that is fairly easy to determine empirically but slowing decreasing the maxLifetime by 30sec increments until the warnings stop.

[brettwooldridge]

@imran-arshad Closing this for now, as it is likely a timeout issue within the infrastruture of AWS or Azure. It is not uncommon for stateful firewalls to terminate idle connections after several minutes (or even one minute), regardless of the timeout setting of the database.

If you are unable to resolve this issue by shortening the maxLifetime to less than that of the infrastructure, feel free to reopen it.

[imran-arshad]

@brettwooldridge you were right. I lowered down the maxLifetime to 3 mins and the warnings never happened again. To further validate the hypothesis, I deployed the application on AWS in the same zone as RDS, and it works even with higher lifetimes. So it was definitely happening due to infrastructure restrictions.
Thank you so much for your help! Really appreciate it!

[brettwooldridge]

I suspected that. Glad to hear you solved it.

[rohitgupta10]

@brettwooldridge thanks for the explanation on this thread.

We connect to a postgres server running on cloud provider resources and we are possibly running into the same issue and our hypothesis is that the SNAT is closing the idle tcp connections without signaling to the client or the server.

I looked at the tcp connections from the client connection to the postgres server
netstat -o | grep "postgres", and this is the output I see

tcp 0 0 CLIENT-HOST:60245 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:58155 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:56459 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:56139 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:60063 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:55241 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:56595 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:38783 <X.X.X.X>:postgresql ESTABLISHED keepalive (6029.43/0/0) tcp 0 0 CLIENT-HOST:59946 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:58922 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:57041 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:59022 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:60384 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:60554 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:58899 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:60611 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:57910 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:33148 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:59742 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:58841 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:56409 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:57993 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:58823 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:57235 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:33251 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0) tcp 0 0 CLIENT-HOST:58163 <X.X.X.X>:postgresql ESTABLISHED off (0.00/0/0)

which makes me wonder if there was a way to set the tcp keepalive in the hikaricp driver, that would solve the problem of stale idle connections.
Thoughts?

[brettwooldridge]

@rohitgupta10 The postgres driver has a keepAlive property. See here. See also the Rapid Recovery guide.

[yoeb]

Is setting idleTimeout useful?

[AllanRM]

I have three databases in the RDS aws.

• Database 01 uses mysql 5.5.
• Database 02 uses mysql 5.7
• Database 03 uses mysql 8.0

When linking my application individually to each of these databases, I have as a result:

• mysql 5.5 has no connection problem.
• mysql 5.7 returns the error presented above.
• mysql 8.0 returns the error presented above.

The time_out settings for both databases are configured to their default values.