HikariCP
HikariCP copied to clipboard
brettwooldridge
Unable to acquire JDBC Connection (Spring JPA + Hibernate + Hikari + Oracle)
Environment
HikariCP version: 3.2.0 JDK version: 1.8.0_271 Database: Oracle Driver version: 12.1.0.1 Hibernate version: 5.3.17 Tomcat version: 9.0.36
After running the application for some days. HIKARI has stopped acquiring new DB. Thread pool count dropped to 0. I have set oracle timout connection also. I had to reboot the docker pods in order to resolve the issue.
Not sure what is happening. Kindly advise where it is going wrong.
FYI I haven't defined the connection provider in Hikari properties.
[2021-26-10 01:07:13] | DEBUG | | com.zaxxer.hikari.pool.HikariPool - Connection-- Timeout failure stats (total=0, active=0, idle=0, waiting=0)
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:447) ~[spring-orm-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:378) ~[spring-tx-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:475) ~[spring-tx-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:289) ~[spring-tx-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93) ~[spring-aop-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691) ~[spring-aop-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:104) [spring-boot-actuator-2.1.15.RELEASE.jar!/:2.1.15.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) [spring-web-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) [tomcat-embed-core 9.0.36.jar!/:9.0.36] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_302] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_302] at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.36.jar!/:9.0.36] at java.lang.Thread.run(Thread.java:748) [?:1.8.0_302] Caused by: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:48) ~[hibernate-core-5.3.17.Final.jar!/:5.3.17.Final] at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.3.17.Final.jar!/:5.3.17.Final] at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.3.17.Final.jar!/:5.3.17.Final] at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.3.17.Final.jar!/:5.3.17.Final] at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:109) ~[hibernate-core-5.3.17.Final.jar!/:5.3.17.Final] at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:136) ~[hibernate-core-5.3.17.Final.jar!/:5.3.17.Final] at org.hibernate.internal.SessionImpl.connection(SessionImpl.java:542) ~[hibernate-core-5.3.17.Final.jar!/:5.3.17.Final] at sun.reflect.GeneratedMethodAccessor147.invoke(Unknown Source) ~[?:?] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_302] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_302] at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:282) ~[spring-core-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:266) ~[spring-core-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle.doGetConnection(HibernateJpaDialect.java:425) ~[spring-orm-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:174) ~[spring-orm-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:402) ~[spring-orm-5.1.16.RELEASE.jar!/:5.1.16.RELEASE] ... 96 more **Caused by: java.sql.SQLTransientConnectionException: ConnecationPool - Connection is not available, request timed out after 50000ms. **at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:676) ~[HikariCP-3.2.0.jar!/:?]** at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:190) ~[HikariCP-3.2.0.jar!/:?] at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:155) ~[HikariCP-3.2.0.jar!/:?] at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-3.2.0.jar!/:?] at** org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.3.17.Final.jar!/:5.3.17.Final] at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:35) ~[hibernate-core-5.3.17.Final.jar!/:5.3.17.Final] at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:106) ~[hibernate-core-5.3.17.Final.jar!/:5.3.17.Final] at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:136) ~[hibernate-core-5.3.17.Final.jar!/:5.3.17.Final]
Hikari Properties `- app.datasource.hikari.data-source-properties.oracle.jdbc.ReadTimeout=600000
- app.datasource.hikari.data-source-properties.oracle.net.CONNECT_TIMEOUT=80000
- app.datasource.connection-timeout=50000
- app.datasource.idle-timeout=300000
- app.datasource.max-lifetime=1800000
- app.datasource.maximum-pool-size=40 `
We do have the same situation (also JDK 1.8.0, Oracle 19c, HikariCP 3.4.5), any updates on that issue ?
These errors are almost certainly an issue with the driver configuration and/or the driver's connection to the database, not an issue with HikariCP.
In the original poster's description, I suspect the oracle CONNECT_TIMEOUT should be shorter than the HikariCP connection timeout so that instead of getting the hikaricp timeout exception you would get the oracle timeout exception instead.
For everyone else's specific cases, I can't really say. We would need to see the specific error message as well as the connection properties as logged by HikariCP itself.
These kinds of issues are better asked on stackoverflow.
The main question to ask for all of these cases is, is there an error from the driver itself when the connection attempt is made? I imagine that you would see such an error someone else in your logs.
