Hikaricp: Pool giving out dead connections.

Created on 6 Feb 2019 · 6Comments · Source: brettwooldridge/HikariCP

Environment

HikariCP version: 3.2.x
JDK version     : 1.8.0_111
Database        : MariaDB 10.1.22
Driver version  : x.x.x

Our database is a MariaDB but we use MySql connector if that is a problem.

We have a Java application running on MariaDB 10.1.22,
The database wait time is 8 hours.
We have set the max life time as 30 mins (default). Don't want to tweak the default unless we have to.

Our connections are idle for most of the times. So we see after 30 mins, an error getting logged that maxlifetime has reached. New connections get created in the pool.

We also see before the 30 minutes, if we make a query we get a dead connection. Can someone throw some pointers to us.

The difference in the time between this and the connection getting created seems to be like 12 mins. That makes us believe to reduce the maxLifeTime to 10 mins. But we don't have a reason to do that.

What boggles us is, why are the connections getting dead before the 30 mins has been reached when a query is executed. Does the query invalidates a connection.

Here are the logs

2019-02-05 16:25:55,904 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@6f2faf1c: (connection has passed maxLifetime)
2019-02-05 16:25:55,993 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@404de770
2019-02-05 16:27:29,953 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@35a93856: (connection has passed maxLifetime)
2019-02-05 16:27:30,038 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@2fba8da7
2019-02-05 16:27:33,919 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@19bb1e64: (connection has passed maxLifetime)
2019-02-05 16:27:33,928 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@6e87879c
2019-02-05 16:27:51,153 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:28:20,378 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@5908d6c0: (connection has passed maxLifetime)
2019-02-05 16:28:20,466 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@15f26a5b
2019-02-05 16:28:21,154 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:28:34,541 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@7aa1c361: (connection has passed maxLifetime)
2019-02-05 16:28:34,549 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@2422c3f9
2019-02-05 16:28:51,154 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:28:57,064 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@55d7b56b: (connection has passed maxLifetime)
2019-02-05 16:28:57,113 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@2398836e
2019-02-05 16:28:57,276 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@14eabf50: (connection has passed maxLifetime)
2019-02-05 16:28:57,285 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@62a58955
2019-02-05 16:29:21,154 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:29:46,137 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@68f1ef1a: (connection has passed maxLifetime)
2019-02-05 16:29:46,187 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@1d84d5a9
2019-02-05 16:29:51,155 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:30:03,335 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@33733b77: (connection has passed maxLifetime)
2019-02-05 16:30:03,344 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@34f6559e
2019-02-05 16:30:21,155 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:30:43,252 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@6e3db40: (connection has passed maxLifetime)
2019-02-05 16:30:43,359 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@2860d11c
2019-02-05 16:55:48,613 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@404de770: (connection has passed maxLifetime)
2019-02-05 16:55:48,701 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@40cad438
2019-02-05 16:55:51,174 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:56:21,175 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:56:51,175 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:57:10,404 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@6e87879c: (connection has passed maxLifetime)
2019-02-05 16:57:10,489 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@1bd6ddcf
2019-02-05 16:57:13,600 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@2fba8da7: (connection has passed maxLifetime)
2019-02-05 16:57:13,609 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@5aebd7f4
2019-02-05 16:57:21,176 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:57:51,176 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:57:56,467 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@15f26a5b: (connection has passed maxLifetime)
2019-02-05 16:57:56,598 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@18a2a6fb
2019-02-05 16:58:13,827 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@2422c3f9: (connection has passed maxLifetime)
2019-02-05 16:58:13,834 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@695aa7e5
2019-02-05 16:58:21,176 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:58:27,809 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@2398836e: (connection has passed maxLifetime)
2019-02-05 16:58:27,918 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@37d756e4
2019-02-05 16:58:41,079 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@62a58955: (connection has passed maxLifetime)
2019-02-05 16:58:41,088 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@21dbfc84
2019-02-05 16:58:51,177 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:59:21,177 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 16:59:24,869 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@34f6559e: (connection has passed maxLifetime)
2019-02-05 16:59:25,003 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@6955c13e
2019-02-05 16:59:42,304 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@1d84d5a9: (connection has passed maxLifetime)
2019-02-05 16:59:42,313 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@1fbd4770
2019-02-05 16:59:51,177 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-02-05 17:00:08,386 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@2860d11c: (connection has passed maxLifetime)
2019-02-05 17:00:08,469 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@19cec219

2019-02-05 17:07:52,834 DEBUG [instock-assistant-qa-1b6626bb-f644-4c1a-a165-911b65bbd6c4-StreamThread-2] org.springframework.jdbc.core.JdbcTemplate [JdbcTemplate.java:667] Executing prepared SQL query
2019-02-05 17:07:52,835 DEBUG [instock-assistant-qa-1b6626bb-f644-4c1a-a165-911b65bbd6c4-StreamThread-2] org.springframework.jdbc.core.JdbcTemplate [JdbcTemplate.java:609] Executing prepared SQL statement [SELECT ]
2019-02-05 17:07:52,836 DEBUG [instock-assistant-qa-1b6626bb-f644-4c1a-a165-911b65bbd6c4-StreamThread-2] org.springframework.jdbc.datasource.DataSourceUtils [DataSourceUtils.java:114] Fetching JDBC Connection from DataSource
2019-02-05 17:07:57,843 WARN [instock-assistant-qa-1b6626bb-f644-4c1a-a165-911b65bbd6c4-StreamThread-2] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:176] HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@40cad438 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2019-02-05 17:07:57,844 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@40cad438: (connection is dead)
2019-02-05 17:07:57,928 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@738f888b
2019-02-05 17:07:57,928 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - After adding stats (total=10, active=1, idle=9, waiting=0)
2019-02-05 17:08:02,851 WARN [instock-assistant-qa-1b6626bb-f644-4c1a-a165-911b65bbd6c4-StreamThread-2] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:176] HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@1bd6ddcf (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2019-02-05 17:08:02,852 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@1bd6ddcf: (connection is dead)
2019-02-05 17:08:02,861 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@32b282b0
2019-02-05 17:08:02,861 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - After adding stats (total=10, active=1, idle=9, waiting=0)
2019-02-05 17:08:07,858 WARN [instock-assistant-qa-1b6626bb-f644-4c1a-a165-911b65bbd6c4-StreamThread-2] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:176] HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@5aebd7f4 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2019-02-05 17:08:07,859 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@5aebd7f4: (connection is dead)
2019-02-05 17:08:07,868 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@34af587
2019-02-05 17:08:07,869 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - After adding stats (total=10, active=1, idle=9, waiting=0)
2019-02-05 17:08:12,866 WARN [instock-assistant-qa-1b6626bb-f644-4c1a-a165-911b65bbd6c4-StreamThread-2] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:176] HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@18a2a6fb (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2019-02-05 17:08:12,867 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@18a2a6fb: (connection is dead)
2019-02-05 17:08:12,875 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@529d7dff
2019-02-05 17:08:12,875 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - After adding stats (total=10, active=1, idle=9, waiting=0)
2019-02-05 17:08:17,873 WARN [instock-assistant-qa-1b6626bb-f644-4c1a-a165-911b65bbd6c4-StreamThread-2] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:176] HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@695aa7e5 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2019-02-05 17:08:17,874 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@695aa7e5: (connection is dead)
2019-02-05 17:08:17,883 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:709] HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@7d20aa9e
2019-02-05 17:08:17,883 DEBUG [HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - After adding stats (total=10, active=1, idle=9, waiting=0)
2019-02-05 17:08:21,184 DEBUG [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Pool stats (total=10, active=1, idle=9, waiting=0)
2019-02-05 17:08:22,879 WARN [instock-assistant-qa-1b6626bb-f644-4c1a-a165-911b65bbd6c4-StreamThread-2] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:176] HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@37d756e4 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2019-02-05 17:08:22,879 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@37d756e4: (connection is dead)
2019-02-05 17:08:22,879 DEBUG [instock-assistant-qa-1b6626bb-f644-4c1a-a165-911b65bbd6c4-StreamThread-2] com.zaxxer.hikari.pool.HikariPool [HikariPool.java:411] HikariPool-1 - Timeout failure stats (total=9, active=0, idle=9, waiting=0)
2019-02-05 17:08:22,880 ERROR [instock-assistant-qa-1b6626bb-f644-4c1a-a165-911b65bbd6c4-StreamThread-2] c.w.ims.instockassistant.processor.kafka.EventProcessor [EventProcessor.java:58] Exception [RetryContext: count=1, lastException=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 30043ms., exhausted=false] while processing message:

Source

ssinganamalla

Most helpful comment

@sooyoung32 This is a little difficult to provide a blanket solution to. This is almost always caused by connections being severed before HikariCP's maxLifeTime. Sometimes the cause may be the DB severing the connection (due to timeout or low resource reclaimation), other times it is caused by a load-balancer or proxy, or even the native OS network stack.

The solution is usually to shorten the maxLifeTime. Alternatively, you have to find how/where the TCP connection is being severed. This can be difficult, and is highly specific to each user's environment.

brettwooldridge on 20 Feb 2019

👍5

All 6 comments

One possibility is you have network infrastructure between the database and Java that closes idle TCP connections after 10 minutes.

nunnr on 7 Feb 2019

👍3

@nunnr Thanks for your reply.

Is there a way to find that out in the logs? or is it more of a networking infrastructure. I am thinking of using nc on the database and application to see it it times out in 10 mins.

One more thing we noticed was our acquire time was going up like 20s - 30s ever 30 minutes. so we increased our maxLifeTime to 7 hours 50 mins (< 8 hours of idle time for db), as we didn't find a reason to reduce the maxLifeTime otherwise, we will see an acquire time of 20 - 30s every 10 mins.

Thoughts?

ssinganamalla on 7 Feb 2019

@ssinganamalla
Did you fix the issue by increasing maxlifetime to 7 hours and 50 minutes?

sooyoung32 on 18 Feb 2019

@sooyoung32 It seemed to have fixed our problem, but I don't have a a good explanation. One of our other application also seems to have similar issue, We will apply the same fix and see, if it resolves the issue.

ssinganamalla on 18 Feb 2019

@ssinganamalla
Thank you for answer.
What I do not understand well is that log gives maxLifeTime shorter ,

Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@695aa7e5). Possibly consider using longer maxLifetime value.
2019-02-05 17: 08: 17,874 DEBUG [HikariPool-1 connection closer] com.zaxxer.hikari.pool.PoolBase [PoolBase.java:129] HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl @ 695aa7e5: (connection is dead)

it seems to be solved by giving maxLifeTime longer in your case.

@brettwooldridge
Could you explain to us about this? Because I have a similar issue..