hikaripool连接保持,HikariPool-1 – 连接不可用，请求超时后[通俗易懂]

I’m using HikariCP 2.4.7 for connection pool. Everything is fine just after starting the application but after some time without invoking getConnection() I get this error when I’m trying to getConnection():

java.sql.SQLTransientConnectionException: HikariPool-1 – Connection is not available, request timed out after 42734ms.

at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:555)

at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:188)

at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:147)

at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:99)

at pl.airly.database.ComplexOperations.getSensorDataFromArea(ComplexOperations.java:310)

at pl.airly.database.ComplexOperations.getMeasurementsInAreaWithinTimeFrame(ComplexOperations.java:201)

at pl.airly.ResponseGenerator.getResponseAdequateToRequest(ResponseGenerator.java:139)

at sun.reflect.GeneratedMethodAccessor24.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)

at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)

at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)

at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:87)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)

at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:108)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:522)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349)

at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:1110)

at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)

at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:785)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1425)

at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Thread.java:745)

During startup I’m initializing HikariDataSource:

hikariDataSource = new HikariDataSource();

hikariDataSource.setDriverClassName(“org.postgresql.Driver”);

hikariDataSource.setUsername(DATABASE_USER_NAME);

hikariDataSource.setPassword(DATABASE_PASSWORD);

hikariDataSource.setJdbcUrl(DATABASE_URL);

hikariDataSource.setLeakDetectionThreshold(5000);

And then I’m using it like this:

try(java.sql.Connection conn = Connection.hikariDataSource.getConnection()) {

Record1 record1 = DSL.using(conn).select(SENSOR.ID) //jOOQ

.from(SENSOR)

.where(SENSOR.ID.equal(1))

.limit(1)

.fetchOne();

Debug.println(record1.getValue(0).toString());

} catch (SQLException e) {

e.printStackTrace();

}

What am I doing wrong? How to troubleshoot this problem?

Edit:

Adding this solves the problem:

hikariDataSource.setIdleTimeout(60000);

hikariDataSource.setConnectionTimeout(60000);

hikariDataSource.setValidationTimeout(3000);

hikariDataSource.setLoginTimeout(5);

hikariDataSource.setMaxLifetime(60000);

It may be something connected with the server closing connections before HikariCP does but I’m not sure. Any comment on this would be appreciated.

解决方案

I start by saying than i am not a Java programmer, you can try to close jOOQ DSL also:

try(java.sql.Connection conn = Connection.hikariDataSource.getConnection()) {

try (Record1 record1 = DSL.using(conn).select(SENSOR.ID)) {

.from(SENSOR)

.where(SENSOR.ID.equal(1))

.limit(1)

.fetchOne();

Debug.println(record1.getValue(0).toString());

} catch (Exception e) {

e.printStackTrace();

}

} catch (SQLException e) {

e.printStackTrace();

}