CODEWITHSUNDEEP
javaspringspring-bootjdbchikaricp
EMRE BİNNAZ
EMRE BİNNAZ

Reputation: 43

Hikari Pool Connection is not available Error

I have a Spring Boot project at my server. I sometimes face "Hikari pool connection is not available" error. I cannot figure out its reason. I looked many problem like this. Some people suggested that some @Transactions should be closed. However, in my app I have not used any @Transactional annotation. Here is my Hikari pool configs:

# hikari settings
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.maxLifetime=1800000
spring.datasource.hikari.maximumPoolSize=10
# hikari log
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG
logging.level.com.zaxxer.hikari=TRACE

When I check my catalina.out log file, I notice active connections count increase sometimes. For example, yesterday there was no active connections. All connections were idle status. Right now there are 2 active connections at the pool. Am I getting this error because the connection pool is populated by all active connections?

By the way, I use a MySQL database. Should I set maxLifeTime one or two seconds less than mysql wait_timeout? I am asking this question because according to https://github.com/brettwooldridge/HikariCP, maxLifeTime should be several seconds shorter than any database or infrastructure imposed connection time limit. Do you think maxLifeTime related with wait_timeout?

Here is stacktrace of error:

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)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:109)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:136)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:50)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:151)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2082)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2012)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1990)
        at org.hibernate.loader.Loader.doQuery(Loader.java:949)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:351)
        at org.hibernate.loader.Loader.doList(Loader.java:2787)
        at org.hibernate.loader.Loader.doList(Loader.java:2770)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2604)
        at org.hibernate.loader.Loader.list(Loader.java:2599)
        at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
        at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2254)
        at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1069)
        at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:170)
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1506)
        ... 116 more
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30001ms.
        at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:676)
        at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:190)
        at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:155)
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)

Upvotes: 0

Views: 4080

Answers (2)

Drewno90
Drewno90

Reputation: 1

I had similar issue when using HikariCP with AzureSQL. Setting up socketTimeout property value at JDBC level (in connection string) has solved problem.

Similar issue described here: https://github.com/brettwooldridge/HikariCP/issues/1056

You can find more details here: https://github.com/brettwooldridge/HikariCP/wiki/Rapid-Recovery

Upvotes: 0

forrestg
forrestg

Reputation: 347

based on your description, one possible reason is your data connection exceed max pool size(which set to 10). Root cause of this could be connection leak in your code. Do you close the connection properly? Either close it explicitly as:

statement.close();
connection.close();

or

try (Connection connection = ***;
PreparedStatement statement = *** )
catch (SQLException ex) {***}

You could enable Hikari log to inspect if this is the case. logback.xml

<logger name="com.zaxxer.hikari" level="debug" additivity="false">
    <appender-ref ref="STDOUT"/>
</logger>

Upvotes: 1

Related Questions