11g JDBC Driver Changes Behavior While Using A Firewall Between The Client And The Database Server (ORA-17410) (Doc ID 1325081.1)

Last updated on MARCH 08, 2017

Applies to:

JDBC - Version 11.1.0.6 and later
Information in this document applies to any platform.
***Checked for relevance on 29-Oct-2012***

Symptoms

On : 11.2.0.1.0 version, JDBC for Java

Find the next behavior:

11g JDBC driver fails to throw ORA-1013 timeout exception when query timeout is set on the statement object. Instead it throws a ORA-17410 at an indeterminate time.

It should throws the same ora-1013 timeout exception as it was in the previous releases.
This is only happening using the 11g jdbc release.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1. Run the next program using the UCP and the 11g driver:

import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.locks.ReentrantLock;

import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;

public class UCPTimeoutTest
{
  final static PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
  static
  {
    try
    {
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setConnectionPoolName("test");
      pds.setURL("jdbc:oracle:thin:@db03.dev.fastrieve.com:1521:fstrv_dev2");
      pds.setUser("qatest2");
      pds.setPassword("qatest2");
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
  }

  public static void main(String[] args)
  {
    try
    {
      ExecutorService executorService = Executors.newFixedThreadPool(2);

      ReentrantLock lock = new ReentrantLock();
      executorService.execute(new DatabaseUpdate(60, 20000, lock));

      //wait until first sql is running
      while (!lock.isLocked())
      {
        Thread.sleep(100);
      }

      executorService.execute(new DatabaseUpdate(5, 0, lock));

       try
      {
        executorService.shutdown();
        while (!executorService.awaitTermination(5, TimeUnit.MINUTES))
        {
          System.out.println("5 minutes elapsed, continuing to wait...");
        }
      }
      catch (InterruptedException e)
      {
        e.printStackTrace();
      }
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
  }
}

class DatabaseUpdate implements Runnable
{
  private int queryTimeout;
  private long sleepTime;
  private ReentrantLock lock;

  public DatabaseUpdate(int queryTimeout, long sleepTime, ReentrantLock lock)
  {
    this.queryTimeout = queryTimeout;
    this.sleepTime = sleepTime;
    this.lock = lock;
  }

  @Override
  public void run()
  {
    lock.lock();
    try
    {
      Connection connection = UCPTimeoutTest.pds.getConnection();
      connection.setAutoCommit(false);
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(queryTimeout);
      statement.executeUpdate("UPDATE userprofile SET email = 'someone@somewhere.com' WHERE username = 'Importer'");
      lock.unlock();
      Thread.sleep(sleepTime);
      statement.close();
      connection.rollback();
      connection.close();
      log("Completed successfully");
    }
    catch (SQLException e)
    {
      log(e.getErrorCode() + ": " + e.getMessage(), e);
    }
    catch (Exception e)
    {
      log(e.getMessage(), e);
    }
  }

   public static void log(String message)
  {
    log(message, null);
  }

  public static void log(String message, Throwable t)
  {
    StringWriter stringWriter = new StringWriter();
    PrintWriter writer = new PrintWriter(stringWriter);
    writer.append(Thread.currentThread().getName());
    writer.append(":");
    writer.append(message);
    if (t != null)
    {
      writer.println();
      t.printStackTrace(writer);
    }
    System.out.println(stringWriter.toString());
  }
}


2. The communication between the Database and the client needs to go through a Firewall with the setup to clear the Urgent Bit in the communication.

3. It will not generate the ORA-1013 error, but the ORA-17410: No more data to read from socket error instead. If you use the 10g driver you will get the expected ORA-1013.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms