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

Question

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.

Solution

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.

Comments

[Jeff Cook]

Since I am using the Spring Jdbc I dont see anything wrong is happening since I was able to save 50 records, but unable to save 500 records in one go