Spring Caused By Java Sql Sqltransientconnectionexception Hikaripool 1 Connection Is Not Available Request Timed Out After 30001ms

Find the data you need here

We provide programming data of 20 most popular languages, hope to help you!

Previous PostNext Post

ProgrammerAH

### Error updating database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
    at sun.reflect.GeneratedMethodAccessor380.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    ... 24 more
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81)
    at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:82)
    at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68)
    at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:338)
    at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:84)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
    at sun.reflect.GeneratedMethodAccessor381.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
    at com.sun.proxy.$Proxy521.update(Unknown Source)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
    ... 28 more
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:669)
    at com.zaxxer.hikari.pool.HikariPool.getConnection (HikariPool.java: 183 )
    at com.zaxxer.hikari.pool.HikariPool.getConnection (HikariPool.java: 148 )
    at com.zaxxer.hikari.HikariDataSource.getConnection (HikariDataSource.java: 128 )
    at com.zaxxer.hikari.HikariDataSource$$FastClassBySpringCGLIB$$eeb1ae86.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
    at com.zaxxer.hikari.HikariDataSource$$EnhancerBySpringCGLIB$$f68c05a.getConnection(<generated>)
    at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:151)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78)
spring:
  datasource:
    hikari:
      connection-test-query: SELECT 1 FROM DUAL
      connection-timeout: 30000
      maximum-pool-size: 20
      max-lifetime: 1800000
      minimum-idle: 5
      connection-init-sql: SET NAMES utf8mb4
spring:
  datasource:
    hikari:
      connection-test-query: SELECT 1 FROM DUAL
      connection-timeout: 600000
      maximum-pool-size: 500
      max-lifetime: 1800000
      minimum-idle: 20
      validation-timeout: 3000
      idle-timeout: 60000
      connection-init-sql: SET NAMES utf8mb4
//Your database is not obtaining connection 
//within (30000 milliseconds that is default connectionTimeout property) 
//because of network latency or some of the queries which are taking too 
//long to execute(more than 30000 milliseconds).

//Please try to increase value of property connectionTimeout.

//YML configuration example:

spring:
  datasource:
    hikari:
      minimumIdle: 2
      maximumPoolSize: 20  //increase if you guess that you will have more than 20
      idleTimeout: 120000
      connectionTimeout: 300000
      leakDetectionThreshold: 300000
//Java Config example:

HikariConfig config = new HikariConfig();
        config.setMaximumPoolSize(20);
        config.setConnectionTimeout(300000);
        config.setConnectionTimeout(120000);
        config.setLeakDetectionThreshold(300000);
spring.datasource.hikari.maximum-pool-size=xx
spring:
  datasource:        
    hikari:                    
      leak-detection-threshold: 2000
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]
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
@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();
            }
        }
    }
}
INFO   | jvm 1    | 2019/07/15 09:10:00 | java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 3600000ms.
    @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);
            }
        }
    }
java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:602)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:195)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:145)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:85)
 HikariConfig config = new HikariConfig();
            config.setJdbcUrl("jdbc:mysql://localhost:3306/****"); 
            config.setUsername("***"); 
            config.setPassword("*****");      
            config.setMaximumPoolSize(20);  
spring:
  datasource:
    hikari:
      minimumIdle: 2
      maximumPoolSize: 10
      idleTimeout: 120000
      connectionTimeout: 300000
      leakDetectionThreshold: 300000
HikariConfig config = new HikariConfig();
        config.setMaximumPoolSize(20);
        config.setConnectionTimeout(300000);
        config.setConnectionTimeout(120000);
        config.setLeakDetectionThreshold(300000);
try (
    Connection connection = dataSource.getConnection();
    Statement statement = …
) {
…
}

try (Connection connection = DataSourceUtils.getConnection(jdbcTemplate.getDataSource())) {
    // some code here
}
Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
### The error may exist in com/sangfor/mapper/QuoteListMapper.java (best guess)
### The error may involve com.sangfor.mapper.QuoteListMapper.insert
### The error occurred while executing an update
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
	at sun.reflect.GeneratedMethodAccessor985.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	... 121 common frames omitted
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81)
	at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:82)
	at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68)
	at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:336)
	at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
	... 126 common frames omitted
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
	at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:669)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:183)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:148)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
	at com.zaxxer.hikari.HikariDataSource$$FastClassBySpringCGLIB$$eeb1ae86.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
	at com.zaxxer.hikari.HikariDataSource$$EnhancerBySpringCGLIB$$b64982c2.getConnection(<generated>)
	at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:157)
	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115)
	at org.springframework.jdbc.datasourc
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: root
    url: jdbc:mysql://xxx.xxx.x.xxx:3306/db
   # 连接池
    hikari:
      #连接池名
      pool-name: DateHikariCP
      #最小空闲连接数
      minimum-idle: 10
      # 空闲连接存活最大时间,默认600000(10分钟)
      idle-timeout: 180000
      # 连接池最大连接数,默认是10
      maximum-pool-size: 60
      # 此属性控制从池返回的连接的默认自动提交行为,默认值:true
      auto-commit: true
      # 此属性控制池中连接的最长生命周期,值0表示无限生命周期,默认1800000即30分钟
      max-lifetime: 1800000
      # 数据库连接超时时间,默认30秒,即30000
      connection-timeout: 30000
      connection-test-query: SELECT 1

HikariCP version: 3.0.0
JDK version     : 1.8.0_111
Database        : MySQL
Driver version  : Not sure.
try {
   conn = hikari.getConnection();  <-- Possible leak.  If an exception is thrown, the Connection is never closed.
   sql = conn.prepareStatement(...);
   ...

   conn = hikari.getConnection();   <-- Leak.  Previous Connection referenced by conn is overwritten and never closed.
   sql = conn.prepareStatement(...);
} catch (SQLException e) {
   ...
} finally {
}
try (Connection conn = hikari.getConnection();
      PreparedStatement sql1 = conn.prepareStatement(...);
      PreparedStatement sql2 = conn.prepareStatement(...)) {

   sql1.setXXX(...);
   ...
   sql1.executeUpdate();

   // No need to close 'conn', 'sql1' or 'sql2' explicitly.  The try guarantees they will be closed.
}
catch (SQLException e) {
   ...
}
		@Override
		public void run() {
			try (Connection conn = hikari.getConnection();
				      PreparedStatement sql1 = conn.prepareStatement("INSERT INTO `Players` values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?");
				      PreparedStatement sql2 = conn.prepareStatement("INSERT INTO `KitCustomizer` values(?,?,?,?,?,?,?,?,?,?,?,?,?)")) {
				sql1.setString(1, uuid);
				sql1.setInt(2, 1000);
				sql1.setInt(3, 1000);
				sql1.setInt(4, 1000);
				sql1.setInt(5, 0);
				sql1.setInt(6, 0);
				sql1.setInt(7, 0);
				sql1.setInt(8, 1);
				sql1.setString(9, "1|0|0");
				sql1.setString(10, "1|0|0");
				sql1.setString(11, "0|0|0");
				sql1.setString(12, "0|0|0");
				sql1.setString(13, "0|0|0");
				sql1.setString(14, "0|0|0");
				sql1.setString(15, "0|0|0");
				sql1.setString(16, "0|0|0");
				sql1.setString(17, "0|0|0");
				sql1.setString(18, "0|0|0");
				sql1.setString(19, "0|0|0");
				sql1.setString(20, "0|0|0");
				sql1.setString(21, "1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|WOOD");
				sql1.setString(22, "1|0|0|0|0|NONE");
				sql1.setString(23, "1|0|0|0|0|0|0|0|0|0|NONE");
				sql1.setString(24, name);
				sql1.execute();
				sql1.close();

				sql2.setString(1, "1|2|3|4|5|6|7|8|9");
				sql2.setString(2, "1|2|3|4|5|6|7|8|9");
				sql2.setString(3, "1|2|3|4|5|6|7|8|9");
				sql2.setString(4, "1|2|3|4|5|6|7|8|9");
				sql2.setString(5, "1|2|3|4|5|6|7|8|9");
				sql2.setString(6, "1|2|3|4|5|6|7|8|9");
				sql2.setString(7, "1|2|3|4|5|6|7|8|9");
				sql2.setString(8, "1|2|3|4|5|6|7|8|9");
				sql2.setString(9, "1|2|3|4|5|6|7|8|9");
				sql2.setString(10, "1|2|3|4|5|6|7|8|9");
				sql2.setString(11, "1|2|3|4|5|6|7|8|9");
				sql2.setString(12, "1|2|3|4|5|6|7|8|9");
				sql2.setString(13, uuid);
				sql2.execute();
				sql2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}, 0L);`
HikariCP version: 2.7.8
JDK version     : 1.8.0_111
Database        : MySQL|
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();
}
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);
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)
...
        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);
            hikari.close();
   :message db_pool - Connection is not available, request timed out after 30003ms.
   :at [com.zaxxer.hikari.pool.HikariPool createTimeoutException HikariPool.java 697]}
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)
Remove: hikari.addDataSourceProperty("maxReconnects",5);
	@Autowired
	protected HikariDataSource dataSource;

Previous PostNext Post

Related code examples