SQLSTATE(08006), ErrorCode(17002) When Using HikariCP JDBC Connection Pool

spring hibernate spring-jdbc jdbctemplate hikaricp
Jawad Tariq picture Jawad Tariq · Jan 24, 2019 · Viewed 8k times · Source

In our Server we are using scheduler to perform a particular tasks. There are already many scheduler running on the server which are working smoothly and Perfrorming DB Operations without any hurdle. But in below case due to some weird issues, I am getting the following exception:

00:01:01,322 WARN  [com.zaxxer.hikari.proxy.ConnectionProxy] (schedulerFactoryBean_Worker-4) Connection oracle.jdbc.driver.T4CConnection@98b70f9 (springHikariCP) marked as broken because of SQLSTATE(08006), ErrorCode(17002).
00:01:01,326 ERROR [stderr] (schedulerFactoryBean_Worker-4) java.sql.SQLRecoverableException: Io exception: Socket read timed out
00:01:01,327 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:101)
00:01:01,328 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
00:01:01,329 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
00:01:01,331 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
00:01:01,332 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:521)
00:01:01,333 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:791)
00:01:01,333 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:855)
00:01:01,334 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
00:01:01,334 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
00:01:01,335 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:387)
00:01:01,335 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at com.zaxxer.hikari.proxy.StatementProxy.executeQuery(StatementProxy.java:99)
00:01:01,336 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at com.zaxxer.hikari.proxy.StatementJavassistProxy.executeQuery(StatementJavassistProxy.java)
00:01:01,336 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at com.inov8.microbank.server.dao.stakeholdermodule.hibernate.StakeholderBankInfoHibernateDAO.getStakeholderBankInfoModelList(StakeholderBankInfoHibernateDAO.java:130)
00:01:01,337 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at com.inov8.microbank.server.service.stakeholdermodule.StakeholderBankInfoManagerImpl.getStakeholderBankInfoModelList(StakeholderBankInfoManagerImpl.java:258)
00:01:01,338 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
00:01:01,339 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
00:01:01,339 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
00:01:01,340 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at java.lang.reflect.Method.invoke(Method.java:606)
00:01:01,340 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
00:01:01,341 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
00:01:01,343 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
00:01:01,344 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
00:01:01,345 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
00:01:01,346 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
00:01:01,346 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at com.sun.proxy.$Proxy78.getStakeholderBankInfoModelList(Unknown Source)
00:01:01,347 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at com.inov8.microbank.server.service.dailyjob.FundTransferScheduler.getStakeholderBankInfoModelList(FundTransferScheduler.java:587)
00:01:01,348 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at com.inov8.microbank.server.service.dailyjob.FundTransferScheduler.executeInternal(FundTransferScheduler.java:93)
00:01:01,348 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:113)
00:01:01,349 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
00:01:01,349 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
00:01:01,350 ERROR [stderr] (schedulerFactoryBean_Worker-4) Caused by: oracle.net.ns.NetException: Socket read timed out
00:01:01,351 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.net.ns.Packet.receive(Packet.java:249)
00:01:01,351 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.net.ns.DataPacket.receive(DataPacket.java:92)
00:01:01,352 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:172)
00:01:01,353 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.net.ns.NetInputStream.read(NetInputStream.java:117)
00:01:01,354 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.net.ns.NetInputStream.read(NetInputStream.java:92)
00:01:01,355 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.net.ns.NetInputStream.read(NetInputStream.java:77)
00:01:01,356 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1034)
00:01:01,357 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1010)
00:01:01,357 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:588)
00:01:01,358 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
00:01:01,359 ERROR [stderr] (schedulerFactoryBean_Worker-4)                at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:780)
00:01:01,359 ERROR [stderr] (schedulerFactoryBean_Worker-4)                ... 24 more
00:01:01,363 INFO  [stdout] (schedulerFactoryBean_Worker-4) ERROR [schedulerFactoryBean_Worker-4] | *******>>>ERROR MESSAGE<<<******* 
00:01:01,364 INFO  [stdout] (schedulerFactoryBean_Worker-4) Hibernate flushing: Cannot release connection; uncategorized SQLException for SQL [???]; SQL state [99999]; error code [17008]; Closed Connection; nested exception is java.sql.SQLException: Closed Connection
00:01:01,365 ERROR [org.quartz.core.JobRunShell] (schedulerFactoryBean_Worker-4) Job DEFAULT.Funds Transfer threw an unhandled Exception: : java.lang.RuntimeException: Unable to load Stakeholder bank info list
                at com.inov8.microbank.server.service.dailyjob.FundTransferScheduler.executeInternal(FundTransferScheduler.java:97) [classes:]
                at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:113) [spring-context-support-3.1.1.RELEASE.jar:3.1.1.RELEASE]
                at org.quartz.core.JobRunShell.run(JobRunShell.java:202) [quartz-2.2.1.jar:]
                at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573) [quartz-2.2.1.jar:]

Here is my HikariCp Configurations:

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <constructor-arg index="0">
            <bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
                <property name="poolName" value="springHikariCP" />
                <property name="minimumIdle" value="5"/>
                <property name="maximumPoolSize" value="5"/>
                <property name="transactionIsolation" value="TRANSACTION_READ_COMMITTED"/>
                <property name="connectionTestQuery" value="select 1 from dual" />
               <!-- <property name="connectionInitSql" value="begin
                                                                 dbms_output.put_line('CALL nothing; DECIMALS=0');
                                                              end;" />-->
                <property name="initializationFailFast" value="false"/>
                <property name="idleTimeout" value="30000"/>
                <property name="dataSource" ref="oracleDataSource"/>
            </bean>
        </constructor-arg>
      </bean>

    <bean id="oracleDataSource" class="oracle.jdbc.pool.OracleDataSource">
        <property name="user" value="${datasource.username}"/>
        <property name="password" value="${datasource.password}"/>
        <property name="URL" value="${datasource.url}"/>
        <property name="connectionProperties">
            <props>
                <prop key="oracle.jdbc.ReadTimeout">120000</prop><!--In Milliseconds-->
            </props>
        </property>
    </bean>

I am not able to get a precise answer after searching it for hours so that's why I am now asking at Stack Overflow.

Answer

pkair picture pkair · May 2, 2020

There was a database lock(TABLE) created by Spring Cloud Task called "TASK_LOCK". It has to be clean and should release all locks before exit.

In my case, there was an ERROR before WARN statement telling - Failed to process @BeforeTask or @AfterTask annotation because: Task with name "scheduler" is already running.

Related questions

How to manage database connection pool in spring jpa?

I am using spring-boot in my web application and use spring-jpa to read/write from/to my database. It works very well but I want to understand how to manage the database connections. Below is my properties configuration for database: …

Read More
spring Hibernate session time out

I'm using Spring Hibernate template apache tomcat and MySQL. Everything works great in development, but when I deploy the app to a test server and let it run (largely idle) overnight, I'm usually greeted with this in the morning: Following …

Read More
How to solve the “failed to lazily initialize a collection of role” Hibernate exception

I have this problem: org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: mvc3.model.Topic.comments, no session or session was closed Here is the model: @Entity @Table(name = "T_TOPIC") public class Topic { @Id @GeneratedValue(strategy=…

Read More