springboot-How to solve java.sql.SQLTransientConnectionException: HikariPool Connection is not available, request timed out after 3600000ms
1. Introduction
When we use HikariCP connection pool in SpringBoot applications, sometimes, we would encounter this error:
INFO | jvm 1 | 2019/07/15 09:10:00 | java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 3600000ms.
INFO | jvm 1 | 2019/07/15 09:10:00 | at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:548) ~[HikariCP-2.5.1.jar:na]
INFO | jvm 1 | 2019/07/15 09:10:00 | at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186) ~[HikariCP-2.5.1.jar:na]
INFO | jvm 1 | 2019/07/15 09:10:00 | at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:145) ~[HikariCP-2.5.1.jar:na]
INFO | jvm 1 | 2019/07/15 09:10:00 | at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:99) ~[HikariCP-2.5.1.jar:na]
2. Environments
- SpringBoot 1.x and 2.x
3. The code
The SpringBoot application.properties
spring.datasource.jdbcUrl=jdbc:mysql://10.1.1.8/test
spring.datasource.username=bswen
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.maximumPoolSize=20
spring.datasource.connectionTimeout=3600000
spring.datasource.idleTimeout=600000
spring.datasource.minimumIdle=5
spring.datasource.test-on-borrow=true
spring.datasource.test-while-idle=true
spring.datasource.time-between-eviction-runs-millis=3600000
Here we use a HikariCP connection pool with connection size from 5 to 20.
The SpringBoot command:
@Component
public class MyCityCommand2 implements CommandLineRunner {
private static Logger logger = LoggerFactory.getLogger(MyCityCommand2.class);
@Autowired
@Qualifier("cityDao")
private CityDao cityDao;
@Override
public void run(String... strings) throws Exception {
while(true) {
Thread.sleep(2000);
JdbcTemplate jdbcTemplate = cityDao.getJdbcTemplateObject();//get a jdbcTemplate
try {
//get metadata from the connection
String dbName = jdbcTemplate.getDataSource().getConnection().getMetaData().getDatabaseProductName();
logger.info("dbName:"+dbName);
}catch (Exception ex) {
logger.error("",ex);
}finally {
//close the connection
jdbcTemplate.getDataSource().getConnection().close();
}
}
}
}
Pay attention to the line jdbcTemplate.getDataSource().getConnection().getMetaData().getDatabaseProductName(), and the line jdbcTemplate.getDataSource().getConnection().close().
First, we retrieve a connection from jdbcTemplate’s datasource , then we close it by call jdbcTemplate.getDataSource().getConnection().close().
When we run the code, we get this error:
INFO | jvm 1 | 2019/07/15 09:10:00 | java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 3600000ms.
4. The solution
We can solve this problem by these ways:
Get and close specific connection In the above code sample, we can see that we get a connection from the datasource and then close a connection from the datasource, they may be different connection! e.g. We get a connection and close another one.
We can change the code as follows:
@Override
public void run(String... strings) throws Exception {
while(true) {
Thread.sleep(2000);
JdbcTemplate jdbcTemplate = cityDao.getJdbcTemplateObject();
try (Connection conn = jdbcTemplate.getDataSource().getConnection()) {
String dbName = conn.getMetaData().getDatabaseProductName();
logger.info("dbName:"+dbName);
}
}
}
5. Summary
When we use a connection pool in SpringBoot Applications, pay attention to the connection leak problem, if it occurs, try to check if you have close the right connection when you use it.