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.

please provide followings: HikariCP version, oracle version, driver version. — erhanasikoglu, Jan 24 '19 at 11:37
Here are the different versions I am using: HikariCP Version: 2.3.5 Oracle Version: 9 — Jawad Tariq, Jan 24 '19 at 11:54
@JawadTariq did you got the answer to problem, i have similar issue and didn't get much on searching too — Rajni Gangwar, Jan 29 '20 at 08:27

score 2 · Answer 1 · answered May 02 '20 at 07:21

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.

yash want · Answer 2 · 2020-09-24T14:28:36.780

1

Make sure if you are performing any CRUD (DEL,UPD,INS) operations in the DB through sqlDeveloper or mysqlworkbench Commit those changes in sqldeveloper and, then run the application. It will work fine. It worked for me.

Also make sure the object is mapped properly to the table.

edited Sep 24 '20 at 14:28

answered Sep 24 '20 at 11:16

yash want

21
2

score 0 · Answer 3 · answered Jun 12 '19 at 22:31

0

I had the same issue, issue was due to the wrong column name mapping in the @entity class. After fixing the column name mapping in Entity, issue got resolved.

answered Jun 12 '19 at 22:31

Jegan

1

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 – Jeff Cook Aug 24 '20 at 16:43

score 0 · Answer 4 · edited Apr 05 '22 at 12:35

0

I experienced a similar issue during application startup, my problem was mistakenly removing Oracle JDBC driver in the pom. Reapplying ojdbc dependency resolved the problem

<!-- https://mvnrepository.com/artifact/com.oracle.jdbc/ojdbc8 -->
<dependency>
    <groupId>com.oracle.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>19.3.0.0</version>
</dependency>

edited Apr 05 '22 at 12:35

Nicholas K

15,148
7
31
57

answered Dec 10 '20 at 08:17

MrM

389
1
8
23

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

4 Answers4