Java cancel task running Oracle query through JDBC - connection broken because of SQLSTATE(08006), ErrorCode(17002) IO Error: Socket read interrupted

Question

Starting from Java custom ThreadPool - pause task submission and cancel current queued tasks I'm implementing a mechanism to close ongoing queries on a specific connection for a period of time. Underlying JDBC connection pool is assured by HikariCP. Ongoing queries are cancelled by calling preparedstatement close as described here Database query interruption. Even doing so I'm getting the following error:

Connection oracle.jdbc.driver.T4CConnection@4bc28c33 marked as broken because of SQLSTATE(08006), ErrorCode(17002)

marked as broken because of SQLSTATE(08006), ErrorCode(17002)

java.sql.SQLRecoverableException: IO Error: Socket read interrupted
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:821)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
    at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
    at java.base/java.lang.Thread.run(Thread.java:840)
Caused by: java.io.InterruptedIOException: Socket read interrupted
    at oracle.net.nt.TimeoutSocketChannel.handleInterrupt(TimeoutSocketChannel.java:258)
    at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:180)
    at oracle.net.ns.NSProtocolNIO.doSocketRead(NSProtocolNIO.java:555)
    at oracle.net.ns.NIOPacket.readHeader(NIOPacket.java:258)
    at oracle.net.ns.NIOPacket.readPacketFromSocketChannel(NIOPacket.java:190)
    at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:132)
    at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:105)
    at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:91)
    at oracle.jdbc.driver.T4CMAREngineNIO.prepareForUnmarshall(T4CMAREngineNIO.java:764)
    at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:429)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:407)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
    ... 16 common frames omitted

Answer 1

As I lost a day on this issue I will post the solution for others: running the application using

-Doracle.jdbc.javaNetNio=false

fix the issue.

From here:

The 12.2.0.1 and above JDBC driver uses Java NIO calls in blocking mode, which may be impacted by any interrupt() calls being made by the application. This differs from previous versions of the JDBC driver, which used stream-based I/O API calls that were not affected by calls to interrupt(). This is a deliberate / intentional change beginning in the 12.2.0.1 JDBC driver. Inspect the application code for any interrupt() method calls.

Alternatively, set the following connection property: oracle.jdbc.javaNetNio=false in application tra file Example: -Doracle.jdbc.javaNetNio=false

This will direct the JDBC driver to revert to the pre-12.2 behavior of using stream-based I/O API calls no affected by interrupt() method calls.