Hikaricp: HikariPool-1 - Connection is not available, request timed out after 30096ms.

Created on 11 Mar 2018  ·  30Comments  ·  Source: brettwooldridge/HikariCP

Environment

HikariCP version: 2.7.8
JDK version     : 1.8.0_111
Database        : MySQL|

Hey, since long time I have big problem with my database, when I'm doing something I got this:
(HikariPool-1 - Connection is not available, request timed out after 30000ms. | )
`[09:26:26] [Craft Scheduler Thread - 2/WARN]: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
How can I fix that?

not-a-bug
Source
picture yooniks

Most helpful comment

Remove: hikari.addDataSourceProperty("autoReconnect",true);
Remove: hikari.addDataSourceProperty("maxReconnects",5);

Enable the leakDetectionThreshold ... hikari.setLeakDetectionThreshold(60 * 1000) and watch the logs for leak messages. The stacktrace that is logged should lead you to the place in your code that borrowed but did not return a connection.

Enable DEBUG level logging in your logging framework for package com.zaxxer.hikari and watch the pool stats logged every 30 seconds:

DEBUG 2018-03-14 07:06:46 com.zaxxer.hikari.pool.HikariPool - Pool-0 - Before cleanup stats (total=5, active=0, idle=5, waiting=0)
DEBUG 2018-03-14 07:06:46 com.zaxxer.hikari.pool.HikariPool - Pool-0 - After cleanup  stats (total=5, active=0, idle=5, waiting=0)
...

If the active count keeps going up until it hits the total, and then waiting starts piling up, again an indication of a leak.

picture brettwooldridge on 14 Mar 2018
👍24

All 30 comments

Not enough information. Please post your configuration, and report your version of HikariCP. How often does it happen? Does it only happen under load? etc. Please provide as much detail as possible, otherwise nobody can help you.

brettwooldridge picture brettwooldridge on 11 Mar 2018

HikariCP version: 2.7.8
Sometimes I get it:
https://hastebin.com/adujexiqad.md
'Does it only happen under load?' -This happens when I'm spamming with method which executes something to mysql, or 'two users' are using over 2 methods, in 3-5 seconds (i think so)

Example:

final String updateString = "UPDATE `users` SET `group`=? WHERE UUID=?";
try (final Connection con = CorePlugin.getInst().getHikari().getConnection();
      final PreparedStatement update = con.prepareStatement(updateString)) {
   update.setString(1, args[1]);
   update.setString(2, uuid.toString());
   update.executeUpdate();
}

(https://hastebin.com/peyudexedu.swift)

hikari = new HikariDataSource();
hikari.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
hikari.addDataSourceProperty("serverName", getConfig().getString("mysql.host"));
hikari.addDataSourceProperty("port", getConfig().getString("mysql.port"));
hikari.addDataSourceProperty("databaseName", getConfig().getString("mysql.database"));
hikari.addDataSourceProperty("user", getConfig().getString("mysql.user"));
hikari.addDataSourceProperty("password", getConfig().getString("mysql.password"));
hikari.addDataSourceProperty("autoReconnect",true);
hikari.addDataSourceProperty("tcpKeepAlive", true);
hikari.setMaximumPoolSize(100);
hikari.setMinimumIdle(0);
hikari.setIdleTimeout(1);
yooniks picture yooniks on 11 Mar 2018
  • Don't use autoReconnect, it is not meant for pools.
  • Use a smaller pool (try 10-20).
  • Idle timeout = 1 does not mean 1 second, it means 1 millisecond. Try 30 seconds (30000ms).
  • Try the tips on this page.
brettwooldridge picture brettwooldridge on 11 Mar 2018

@brettwooldridge
hikari = new HikariDataSource(); hikari.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource"); hikari.addDataSourceProperty("serverName", getConfig().getString("mysql.host")); hikari.addDataSourceProperty("port", getConfig().getString("mysql.port")); hikari.addDataSourceProperty("databaseName", getConfig().getString("mysql.database")); hikari.addDataSourceProperty("user", getConfig().getString("mysql.user")); hikari.addDataSourceProperty("password", getConfig().getString("mysql.password")); hikari.addDataSourceProperty("autoReconnect",true); hikari.addDataSourceProperty("cachePrepStmts",true); hikari.addDataSourceProperty("prepStmtCacheSize",250); hikari.addDataSourceProperty("prepStmtCacheSqlLimit",2048); hikari.addDataSourceProperty("useServerPrepStmts",true); hikari.addDataSourceProperty("cacheResultSetMetadata",true); hikari.addDataSourceProperty("maxReconnects",5); hikari.addDataSourceProperty("tcpKeepAlive", true); hikari.setMaximumPoolSize(20); hikari.setMinimumIdle(0); hikari.setIdleTimeout(30000);
https://hastebin.com/lesojopizu.scala

final String insertString = "INSERT INTO users VALUES(?,?,?,?,?,?,?,?,?)"; try (final Connection con = CorePlugin.getInst().getHikari().getConnection(); final PreparedStatement insert = con.prepareStatement(insertString)) { insert.setString(1, uuid.toString()); insert.setString(2, p.getName()); insert.setString(3, args[1]); //group insert.setInt(4, 0); insert.setInt(5, 0); insert.setInt(6, 500); insert.setInt(7, 0); insert.setInt(8, 0); insert.setString(9, ""); insert.execute(); }
https://hastebin.com/sotezidiji.swift

and

final String updateString = "UPDATEusersSETgroup=? WHERE UUID=?"; try (final Connection con = CorePlugin.getInst().getHikari().getConnection(); final PreparedStatement update = con.prepareStatement(updateString)) { update.setString(1, args[1]); update.setString(2, uuid.toString()); update.executeUpdate(); }
https://hastebin.com/uqeguqeyec.swift

still not working ;/

[12:30:34] [Craft Scheduler Thread - 1/WARN]: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
https://hastebin.com/odiwiviniv.md

Don't use autoReconnect, it is not meant for pools.'

How can I replace this?

yooniks picture yooniks on 14 Mar 2018

Remove: hikari.addDataSourceProperty("autoReconnect",true);
Remove: hikari.addDataSourceProperty("maxReconnects",5);

Enable the leakDetectionThreshold ... hikari.setLeakDetectionThreshold(60 * 1000) and watch the logs for leak messages. The stacktrace that is logged should lead you to the place in your code that borrowed but did not return a connection.

Enable DEBUG level logging in your logging framework for package com.zaxxer.hikari and watch the pool stats logged every 30 seconds:

DEBUG 2018-03-14 07:06:46 com.zaxxer.hikari.pool.HikariPool - Pool-0 - Before cleanup stats (total=5, active=0, idle=5, waiting=0)
DEBUG 2018-03-14 07:06:46 com.zaxxer.hikari.pool.HikariPool - Pool-0 - After cleanup  stats (total=5, active=0, idle=5, waiting=0)
...

If the active count keeps going up until it hits the total, and then waiting starts piling up, again an indication of a leak.

brettwooldridge picture brettwooldridge on 14 Mar 2018
👍24

@brettwooldridge A lot of thanks! I fixed it.

yooniks picture yooniks on 16 Mar 2018

@yooniks how exactly did you fix it?

apoguy picture apoguy on 23 Jul 2018

@apoguy I don't remember exactly, but I think i was not closing datasource (yes, it is autocloseable, but it helped me) That is what i changed:
``` hikari = new HikariDataSource();
hikari.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
hikari.addDataSourceProperty("serverName", getConfig().getString("mysql.host"));
hikari.addDataSourceProperty("port", getConfig().getString("mysql.port"));
hikari.addDataSourceProperty("databaseName", getConfig().getString("mysql.database"));
hikari.addDataSourceProperty("user", getConfig().getString("mysql.user"));
hikari.addDataSourceProperty("password", getConfig().getString("mysql.password"));
hikari.addDataSourceProperty("autoReconnect", true);
hikari.addDataSourceProperty("cachePrepStmts", true);
hikari.addDataSourceProperty("prepStmtCacheSize", 250);
hikari.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
hikari.addDataSourceProperty("useServerPrepStmts", true);
hikari.addDataSourceProperty("cacheResultSetMetadata", true);

    hikari.setMaximumPoolSize(16);
    hikari.setConnectionTimeout(30000);

it is very old code, but it is working fine now
and i had try/catch in try/catch, i removed it and cleaned my code.
```        if (hikari != null)
            hikari.close();
yooniks picture yooniks on 23 Jul 2018
👍1

Do we know now exactly how to fix this error?

@yooniks Why use hikari.close();? Should it not be conn.close() in the finally after the execution?

kaushik1979 picture kaushik1979 on 25 Jul 2018

I have identified the issue on our end, and it was a connection leak. Detected by using logging and setting the connection leak detection threshold.

We solved it by making all methods that used the DB @Transacted, and returning the connection to the pool with the close() method.

apoguy picture apoguy on 25 Jul 2018
👍4

Update properties spring.datasource.hikari.connection-timeout = 500000, Is it valid solution?

solankivaibhav789 picture solankivaibhav789 on 11 Mar 2019

I am not sure but i think i just didn't close the statement (in another methods)

pon., 11 mar 2019, 13:59 użytkownik solanki vaibhav <
[email protected]> napisał:

Update properties spring.datasource.hikari.connection-timeout = 500000, Is
it valid solution?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/brettwooldridge/HikariCP/issues/1111#issuecomment-471528286,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AcooWjocN6wU77yv3n7nBMEQxA5nHcJCks5vVlMngaJpZM4SlsOR
.

yooniks picture yooniks on 11 Mar 2019

how to use hikari.close(); to fix problem

skydh picture skydh on 17 Jun 2019

i am getting the -SpringBootJPAHikariCP - Connection is not available, request timed out after *.
intermediately. please advice
This is my Hikari CP stetting

HIKARI SETTINGS

spring.datasource.type=com.zaxxer.hikari.HikariDataSource

spring.datasource.hikari.minimumIdle=20

spring.datasource.hikari.maximumPoolSize=100

spring.datasource.hikari.idleTimeout=300000

spring.datasource.hikari.poolName=SpringBootJPAHikariCP

spring.datasource.hikari.maxLifetime=200000

spring.datasource.hikari.connectionTimeout=20000

spring.datasource.hikari.registerMbeans=true

ERROR Message:

8760b748-662d-4434-9c79-15cf8dc44b82 1 Fri Jun 21 2019 07:24:27.261 at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:190) ~[HikariCP-3.2.0.jar!/:na]
8760b748-662d-4434-9c79-15cf8dc44b82 1 Fri Jun 21 2019 07:24:27.261 at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:676) ~[HikariCP-3.2.0.jar!/:na]
8760b748-662d-4434-9c79-15cf8dc44b82 1 Fri Jun 21 2019 07:24:27.261 Caused by: java.sql.SQLTransientConnectionException: SpringBootJPAHikariCP - Connection is not available, request timed out after 20000ms.

Dhanapalva picture Dhanapalva on 23 Jun 2019

I recommend setting the leakDetectionThreshold and watching the log file. Also, enable debug level logging and monitor the pool stats.

brettwooldridge picture brettwooldridge on 23 Jun 2019

i have enabled the debug log, this what i can see with pool state.
Line 480: 17c57c5b-b561-4293-ba2c-da13a3da5a71 1 Mon Jun 24 2019 16:37:09.517 2019-06-24 11:07:09.517 DEBUG 16 --- [ XNIO-2 task-96] com.zaxxer.hikari.pool.HikariPool : SpringBootJPAHikariCP - Timeout failure stats (total=11, active=11, idle=0, waiting=2)
Line 3286: 17c57c5b-b561-4293-ba2c-da13a3da5a71 1 Mon Jun 24 2019 16:37:08.179 2019-06-24 11:07:08.179 DEBUG 16 --- [ XNIO-2 task-24] com.zaxxer.hikari.pool.HikariPool : SpringBootJPAHikariCP - Timeout failure stats (total=12, active=12, idle=0, waiting=2)
Line 5983: 17c57c5b-b561-4293-ba2c-da13a3da5a71 1 Mon Jun 24 2019 16:37:06.502 2019-06-24 11:07:06.502 DEBUG 16 --- [ XNIO-2 task-48] com.zaxxer.hikari.pool.HikariPool : SpringBootJPAHikariCP - Timeout failure stats (total=30, active=30, idle=0, waiting=5)
Line 6472: 17c57c5b-b561-4293-ba2c-da13a3da5a71 1 Mon Jun 24 2019 16:37:06.077 2019-06-24 11:07:06.077 DEBUG 16 --- [XNIO-2 task-100] com.zaxxer.hikari.pool.HikariPool : SpringBootJPAHikariCP - Timeout failure stats (total=30, active=30, idle=0, waiting=5)
Line 7368: 17c57c5b-b561-4293-ba2c-da13a3da5a71 1 Mon Jun 24 2019 16:37:06.060 2019-06-24 11:07:06.060 DEBUG 16 --- [ XNIO-2 task-8] com.zaxxer.hikari.pool.HikariPool : SpringBootJPAHikariCP - Timeout failure stats (total=16, active=16, idle=0, waiting=3)
Line 7369: 17c57c5b-b561-4293-ba2c-da13a3da5a71 1 Mon Jun 24 2019 16:37:06.059 2019-06-24 11:07:06.059 DEBUG 15 --- [ XNIO-2 task-1] com.zaxxer.hikari.pool.HikariPool : SpringBootJPAHikariCP - Timeout failure stats (total=24, active=24, idle=0, waiting=5)
Line 7507: 17c57c5b-b561-4293-ba2c-da13a3da5a71 1 Mon Jun 24 2019 16:37:06.049 2019-06-24 11:07:06.049 DEBUG 16 --- [ XNIO-2 task-60] com.zaxxer.hikari.pool.HikariPool : SpringBootJPAHikariCP - Timeout failure stats (total=16, active=16, idle=0, waiting=4)
Line 7710: 17c57c5b-b561-4293-ba2c-da13a3da5a71 1 Mon Jun 24 2019 16:37:05.815 2019-06-24 11:07:05.815 DEBUG 16 --- [ XNIO-2 task-84] com.zaxxer.hikari.pool.HikariPool : SpringBootJPAHikariCP - Timeout failure stats (total=11, active=11, idle=0, waiting=1)
Line 8495: 17c57c5b-b561-4293-ba2c-da13a3da5a71 1 Mon Jun 24 2019 16:37:05.148 2019-06-24 11:07:05.148 DEBUG 16 --- [ XNIO-2 task-41] com.zaxxer.hikari.pool.HikariPool : SpringBootJPAHikariCP - Timeout failure stats (total=30, active=30, idle=0, waiting=6)
Line 9620: 17c57c5b-b561-4293-ba2c-da13a3da5a71 1 Mon Jun 24 2019 16:37:02.536 2019-06-24 11:07:02.536 DEBUG 15 --- [ XNIO-2 task-5] com.zaxxer.hikari.pool.HikariPool : SpringBootJPAHikariCP - Timeout failure stats (total=24, active=24, idle=0, waiting=5)

Dhanapalva picture Dhanapalva on 24 Jun 2019

Hi guys, I have same issues, not sure what's the proper fix for this. Also I can confirm that my connection has been closed properly.

tianhao-au picture tianhao-au on 27 Aug 2019

same issue

stamanker picture stamanker on 4 Oct 2019

Hi, I'm getting same issue with error code "java.sql.SQLTransientConnectionException / -hikari-pool - Connection is not available, request timed out after "
This is getting randomly and doesn't have relation with heavy load.
Also what do you mean by leak?

ChaminW picture ChaminW on 23 Oct 2019

make sure that you close a connection and statement, when i close only
statement and the connection stays i get this error after some time

śr., 23 paź 2019, 07:40 użytkownik Chamin Wickramarathna <
[email protected]> napisał:

Hi, I'm getting same issue with error code
"java.sql.SQLTransientConnectionException / -hikari-pool - Connection
is not available, request timed out after "
This is getting randomly and doesn't have relation with heavy load.
Also what do you mean by leak?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/brettwooldridge/HikariCP/issues/1111?email_source=notifications&email_token=AHFCQWTCJXXX7PYQC6QSYALQP7PTRA5CNFSM4EUWYOI2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOECAD6QI#issuecomment-545275713,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AHFCQWTUNGXBCFUPOCXN24TQP7PTRANCNFSM4EUWYOIQ
.

yooniks picture yooniks on 23 Oct 2019

Got the same error today
```{:type java.sql.SQLTransientConnectionException
:message db_pool - Connection is not available, request timed out after 30003ms.
:at [com.zaxxer.hikari.pool.HikariPool createTimeoutException HikariPool.java 697]}


Environment:

Postgresql database, clojure project
:dependancies {[hikari-cp "2.9.0"]}
Leiningen 2.9.1 on Java 1.8.0_222 OpenJDK 64-Bit Server VM
openjdk version "1.8.0_222"
OpenJDK Runtime Environment (build 1.8.0_222-8u222-b10-1ubuntu1~18.04.1-b10)
OpenJDK 64-Bit Server VM (build 25.222-b10, mixed mode)

How do I get to the properties 
```hikari.addDataSourceProperty("autoReconnect",true);
Remove: hikari.addDataSourceProperty("maxReconnects",5);

? Are these in my env/dev/resources/config.edn?

CambodianCoder picture CambodianCoder on 29 Oct 2019

I have been seeing this issue occasionally in a low-activity application, and thanks to debug logging I was able to catch it in the act. The log didn't indicate a cause, unfortunately.

The application had been running for 116 days, and then the pool mysteriously ran out of connections. It simply began to not reopen them after they had expired. The application wasn't even in use at the time; it was not performing any database operations.

Normally, the connections would expire and be reopened, with log entries like this:

HikariPool-1 - Closing connection oracle.jdbc.driver.T4CConnection@ea16de1: (connection has passed maxLifetime)
HikariPool-1 - Added connection oracle.jdbc.driver.T4CConnection@71a66e81

This time, they were not reopened and the Pool stats' total value gradually went down to zero:

Pool stats (total=10, active=0, idle=10, waiting=0)
Closing connection oracle.jdbc.driver.T4CConnection@730229a3: (connection has passed maxLifetime)
Pool stats (total=9, active=0, idle=9, waiting=0)
Closing connection oracle.jdbc.driver.T4CConnection@439ae1f9: (connection has passed maxLifetime)
Closing connection oracle.jdbc.driver.T4CConnection@796607cc: (connection has passed maxLifetime)
Pool stats (total=7, active=0, idle=7, waiting=0)
...
Pool stats (total=0, active=0, idle=0, waiting=0)

Two days later someone tried to use the application and the "Connection is not available, request timed out..." error occurred. We restarted it and it began to operate normally as always. I did not have an opportunity to test the database or its network connection while our pool was dying, but I will set up a monitor on our log for next time.

There were 23001 log entries for "Added connection ...".

mrbuddypal picture mrbuddypal on 20 Dec 2019

I faced the same problem. For me the solution seems to be to change the dataSource if you use one. I'm using Spring Boot and switched from DataSourceto HikariDataSource

    @Autowired
    protected HikariDataSource dataSource;
saschabrockel picture saschabrockel on 30 Dec 2019
❤1

Hi, I am getting almost the same ERROR ->

o.h.engine.jdbc.spi.SqlExceptionHelper : IO Error: Socket read timed out
o.h.engine.jdbc.spi.SqlExceptionHelper : HikariPool-1 - Connection is not available, request timed out after 30000ms.

The application is running fine for few days but suddenly sometimes it shows this error. After restarting application it again works fine. Could not trace the issue.

Properties defined are:-
server.connection-timeout=10000
spring.mvc.async.request-timeout=10000ms

spring.datasource.url=jdbc:oracle:thin:@hostname/databasename
spring.datasource.username=username
spring.datasource.password=password
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none

spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.DefaultNamingStrategy
spring.jpa.properties.hibernate.id.new_generator_mappings=false

spring.datasource.connection-test-query=SELECT 1
spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=true

shuchi-ag picture shuchi-ag on 22 Apr 2020

I have been seeing this issue occasionally in a low-activity application, and thanks to debug logging I was able to catch it in the act. The log didn't indicate a cause, unfortunately.

The application had been running for 116 days, and then the pool mysteriously ran out of connections. It simply began to not reopen them after they had expired. The application wasn't even in use at the time; it was not performing any database operations.

Normally, the connections would expire and be reopened, with log entries like this:

HikariPool-1 - Closing connection oracle.jdbc.driver.T4CConnection@ea16de1: (connection has passed maxLifetime)
HikariPool-1 - Added connection oracle.jdbc.driver.T4CConnection@71a66e81

This time, they were not reopened and the Pool stats' total value gradually went down to zero:

Pool stats (total=10, active=0, idle=10, waiting=0)
Closing connection oracle.jdbc.driver.T4CConnection@730229a3: (connection has passed maxLifetime)
Pool stats (total=9, active=0, idle=9, waiting=0)
Closing connection oracle.jdbc.driver.T4CConnection@439ae1f9: (connection has passed maxLifetime)
Closing connection oracle.jdbc.driver.T4CConnection@796607cc: (connection has passed maxLifetime)
Pool stats (total=7, active=0, idle=7, waiting=0)
...
Pool stats (total=0, active=0, idle=0, waiting=0)

Two days later someone tried to use the application and the "Connection is not available, request timed out..." error occurred. We restarted it and it began to operate normally as always. I did not have an opportunity to test the database or its network connection while our pool was dying, but I will set up a monitor on our log for next time.

There were 23001 log entries for "Added connection ...".

@mrbuddypal I have the same error with an application that is live for over a year, and in last month it crashes (the pool ran out of connections) mostly in the night (when there are no users in the application).

Did you manage to find a solution?

mindit-beatrice-luca picture mindit-beatrice-luca on 18 Jun 2020

@mindit-beatrice-luca I did not find a solution. I was able to capture a thread dump when it happened again on June 8.
paddock-pool-empty.log

mrbuddypal picture mrbuddypal on 18 Jun 2020

@mrbuddypal , @mindit-beatrice-luca I am having the same issue.. I recently switched myBattis to use HikariCP from BoneCP.. and I notice the same issue that you guys were seeing..

Is it possible to reopen this issue ?

ravi-b-m picture ravi-b-m on 6 Aug 2020

I am using spring boot and I was facing the same problem, and my solution was to get the connection like this "DataSourceUtils.getConnection(dataSource)".

So I change from dataSource.getConnection() to DataSourceUtils.getConnection(dataSource).

stianrincon picture stianrincon on 8 Aug 2020

Hello Team,

Is this issue still being tracked? and also is there a way I can check/compare fixes done on different releases? release document/notes available?

Basically we have similar issue mentioned in this thread.
Versions used:
2.7.6

Exception stack trace:
org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection at org.springframework.orm.hibernate5.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:542) at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373) Caused by: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:48) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) Caused by: java.sql.SQLTransientConnectionException: lxb2capa02.db-pool - Connection is not available, request timed out after 932300ms. at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:548) at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection timed out (Read failed) at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667) at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1654)

configuration:
public static final int DB_CONN_POOL_SIZE = 50;
public static final int DB_INIT_FAIL_TIME_OUT = 10000;
public static final long CONN_LEAK_THRESHOLD_IN_MILLS = 240000; //4 min
public static final long CONN_IDLE_TIMEOUT_IN_MILLS = 900000; //15 min
public static final long CONN_MAX_LIFE_IN_MILLS = 1800000; //30 min
public static final int CONN_TIMEOUT_IN_MILLS = 10000; //10 sec
public static final int QUERY_TIMEOUT_IN_SECS = 30; //30 secs
config.setMaximumPoolSize(DB_CONN_POOL_SIZE); config.setLeakDetectionThreshold(CONN_LEAK_THRESHOLD_IN_MILLS); //4 mins config.setConnectionTestQuery("SELECT 1"); config.setValidationTimeout(CONN_TIMEOUT_IN_MILLS); config.setIdleTimeout(CONN_IDLE_TIMEOUT_IN_MILLS); config.setMaxLifetime(CONN_MAX_LIFE_IN_MILLS); config.setConnectionTimeout(CONN_TIMEOUT_IN_MILLS); //10 secs DataSource dataSource = new HikariDataSource(config);

To add some more information, we do set 'QUERY_TIMEOUT_IN_SECS ' for most of the GET calls and not to the save/update calls. My question is why connection was released/timed out after 932300ms(15mins) the only correlation i see for this behaviour is 'CONN_IDLE_TIMEOUT_IN_MILLS ' configuration.
I'm enabling the debug logs for hikari cp to check connection leak, just in case if this information helps and someone can throw some light on this topic would be helpful.

sanjaybsm picture sanjaybsm on 24 Aug 2020

I have been seeing this issue occasionally in a low-activity application, and thanks to debug logging I was able to catch it in the act. The log didn't indicate a cause, unfortunately.
The application had been running for 116 days, and then the pool mysteriously ran out of connections. It simply began to not reopen them after they had expired. The application wasn't even in use at the time; it was not performing any database operations.
Normally, the connections would expire and be reopened, with log entries like this:

HikariPool-1 - Closing connection oracle.jdbc.driver.T4CConnection@ea16de1: (connection has passed maxLifetime)
HikariPool-1 - Added connection oracle.jdbc.driver.T4CConnection@71a66e81

This time, they were not reopened and the Pool stats' total value gradually went down to zero:

Pool stats (total=10, active=0, idle=10, waiting=0)
Closing connection oracle.jdbc.driver.T4CConnection@730229a3: (connection has passed maxLifetime)
Pool stats (total=9, active=0, idle=9, waiting=0)
Closing connection oracle.jdbc.driver.T4CConnection@439ae1f9: (connection has passed maxLifetime)
Closing connection oracle.jdbc.driver.T4CConnection@796607cc: (connection has passed maxLifetime)
Pool stats (total=7, active=0, idle=7, waiting=0)
...
Pool stats (total=0, active=0, idle=0, waiting=0)

Two days later someone tried to use the application and the "Connection is not available, request timed out..." error occurred. We restarted it and it began to operate normally as always. I did not have an opportunity to test the database or its network connection while our pool was dying, but I will set up a monitor on our log for next time.
There were 23001 log entries for "Added connection ...".

@mrbuddypal I have the same error with an application that is live for over a year, and in last month it crashes (the pool ran out of connections) mostly in the night (when there are no users in the application).

Did you manage to find a solution?

I face the same issue. the problem appears every 6 days. And my solution was to replace "getSessionFactory().openSession" to “getSessionFactory().getCurentSession” . It never been appears.

wolongjie picture wolongjie on 4 Feb 2021
Was this page helpful?
0 / 5 - 0 ratings

Related issues

manueljordan picture manueljordan  ·  6Comments
sumeetpri picture sumeetpri  ·  3Comments
coexia picture coexia  ·  5Comments
acehead picture acehead  ·  5Comments
Jobarion picture Jobarion  ·  3Comments