HikariPool-1 – Connection is not available, request timed out after 30002ms.
After some SQL queries, the system is unable to get any connection from the HikariPool and prompts the following error message
HikariPool-1 - Connection is not available, request timed out after 30002ms.
Tested with
- HikariCP 3.3.1
- mysql-connector-java 5.1.47
- Java 8
1. Solution
1.1 Enable the debug logs for com.zaxxer.hikari, it will print out many useful info.
logback.xml
<!-- this config is for logback framework -->
<logger name="com.zaxxer.hikari" level="debug" additivity="false">
<appender-ref ref="STDOUT"/>
</logger>
Terminal
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - HikariPool-1 - configuration:
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................true
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................none
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............none
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery.............none
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............30000
//...
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl.........................jdbc:mysql://192.168.1.4:3306/wordpress
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........0
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................1800000
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................1
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................none
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........none
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................1
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - password........................<masked>
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"HikariPool-1"
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor...............none
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - schema..........................none
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................internal
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............default
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - username........................"mkyong"
2019-03-20 13:46:23 [main] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............5000
2019-03-20 13:47:25 [main] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Timeout failure stats (total=10, active=10, idle=0, waiting=0)
2019-03-20 13:47:25 [main] ERROR c.mkyong.calculator.StartApplication - [Exception] : HikariPool-1 - Connection is not available, request timed out after 30001ms.
1.2 By default, the connectionTimeout is 30 seconds.
HikariPool-1 - Connection is not available, request timed out after 30001ms.
1.3 This means Hikari pool reached maximum connections total=10, active=10
HikariPool-1 - Timeout failure stats (total=10, active=10, idle=0, waiting=0)
1.4 Mostly is connection leak, normally this is caused by the connection is not closed after borrowing from the pool.
For example, below code, getConnection() is not close, and this will cause connection leak.
@Override
public void update(int postId, String metaValue) throws SQLException {
try (PreparedStatement ps =
dataSource.getConnection().prepareStatement(SQL_UPDATE_POST_META)) {
ps.setString(1, metaValue);
ps.setInt(2, postId);
ps.setString(3, GlobalUtils.META_KEY);
ps.executeUpdate();
} catch (SQLException e) {
logger.error("[UPDATE] [POST_ID] : {}, [META_VALUE] : {}", postId, metaValue);
throw e;
}
}
To fix it, just close the connection.
@Override
public void update(int postId, String metaValue) throws SQLException {
try (Connection connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(SQL_UPDATE_POST_META)) {
ps.setString(1, metaValue);
ps.setInt(2, postId);
ps.setString(3, GlobalUtils.META_KEY);
ps.executeUpdate();
} catch (SQLException e) {
logger.error("[UPDATE] [POST_ID] : {}, [META_VALUE] : {}", postId, metaValue);
throw e;
}
}
References
About Author
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.
Where are we closing the connection in the second example?
I think placing Connection explicitely in the try-with-resource block…
Work’s fine for me but I needed to put the following snippet at the end: connection.close();
I’m seeing this error in my Spring Boot application while during login (while idle). It throws this error every hour, even though the app is idle. This is running on Tomcat 9.
[HikariPool-1 connection adder] com.zaxxer.hikari.pool.HikariPool: HikariPool-1 – Error thrown while acquiring connection from data source.
java.sql.SQLSyntaxErrorException: (conn=6240) Connection.setNetworkTimeout cannot be called on a closed connection.
Subset of application.properties:
server.servlet.session.timeout=10m
spring.datasource.url=jdbc:mariadb://10.xx.xx.xx
spring.datasource.username=xxx
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update
I’m assuming the spring security code is throwing these errors.
Where are we closing connection in second example
Your CP config does not correspond to stats. Maxpool size is 1, logs show 10.
You saved my life.Thanks a lot.You are champ.