HikariPool Connection is not available 30000ms

Support
1

Hi,

Frequently i am getting HikariPool Connection is not available 30000ms when executing sql or native query.

java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 30000ms.

I tried adding below parameter but still getting timeout in 30000ms

spring.datasource.hikari.max-lifetime=900000

Can we stop connection getting timeout, if not can we add retry connection.

Anyreason why Jpql query are working fine and sql queries are facing issues of connection in same DB.

Thank you in advance.

3

Hi Adnan.

Sorry for the late reply.

Jmix uses another properties to configure datastore
main.datasource.hikari.max-lifetime should work in your case.

Unfortunately, It’s difficult to guess what caused the problem without seeing the project. If you could provide a simple example project where the problem can be reproduced I will be able to investigate the reason of it.

Regards,
Dmitry

1 Like
4

thank you

5

Still getting the same error.

main.datasource.hikari.max-lifetime = 900000

6

Hi,

I had added below line but still getting timeout 30000 it occurs when we try to insert data using jdbctemplate is it possible to disable hikari from project because most of the time due to timeout some functionality gets impacted due to timeout.

Any also can we implement any other connection pooling in jmix.

main.datasource.hikari.max-lifetime = 900000



java.sql.SQLTransientConnectionException: HikariPool-62 - Connection is not available, request timed out after 30000ms.
	at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
java.sql.SQLTransientConnectionException: HikariPool-62 - Connection is not available, request timed out after 30000ms.
	at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
java.sql.SQLTransientConnectionException: HikariPool-62 - Connection is not available, request timed out after 30000ms.
	at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
java.sql.SQLTransientConnectionException: HikariPool-62 - Connection is not available, request timed out after 30000ms.
	at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
7

Hello Adnan,

About your problem:

As I can see from the stacktrace you added, maybe the next property will be more suitable:
main.datasource.hikari.connection-timeout ?

If not, it makes sense to check another timeout hikary properties.

Another thing to be checked: is this problem occur for the main datasource? In case of additional datasource its configuration will look like this:
<datasourceName>.datasource.hikari.*

If none of this helps I would recommend putting breakpoints inside com.zaxxer.hikari.HikariConfig#setConnectionTimeout and com.zaxxer.hikari.HikariConfig#validateNumerics to see why this 30000 ms is set to com.zaxxer.hikari.HikariConfig#connectionTimeout.

After timeout will be increased:

If increasing timeout will not help to solve the problem, please check whether the connections hang somehow because of locks, too long operations, or pool depletion. That’s all I can guess without seeing a reproducible example.

If you still want to try another connection pool:

Please, take note that Jmix has not been fully tested with other connection pools and flawless work is not guaranteed with them.

But if you want to use another connection pool you can configure it like for any other spring boot application. Replace the next lines in your <ProjectName>Application.java:

    @Bean
    @Primary
    @ConfigurationProperties("main.datasource")
    DataSourceProperties dataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("main.datasource.hikari")
    DataSource dataSource(final DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

With this lines:

    @Bean
    @Primary
    @ConfigurationProperties("main.datasource")
    DataSourceProperties dataSourceProperties() {
        DataSourceProperties dataSourceProperties = new DataSourceProperties();
        dataSourceProperties.setType(<datasource_class>); // e.g. org.apache.commons.dbcp2.BasicDataSource.class
        return dataSourceProperties;
    }

    @Bean
    @Primary
    @ConfigurationProperties("main.datasource.<suffix>") // e.g. main.datasource.dbcp2
    DataSource dataSource(final DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

See this spring boot class to get datasource class names and config prefixes.
See also Spring boot documentation about supported connection pools

Regards,
Dmitry

8

Thank you for detailed solution.

Currently i had set main.datasource.hikari.connection-timeout and same is visible indebug point.