java.sql.sqlrecoverableexception no more data to read from socket

Question

Problem :

I am facing bellow error

java.sql.sqlrecoverableexception no more data to read from socket

Answer 1

I was able to solve this problem from application side, under the bellow scenario:

I used the database connection pooling apache tomcat-jdbc:

You can also take the following configuration parameters as a reference:

<Resource name="jdbc/exampleDB"
      auth="Container"
      type="javax.sql.DataSource"
      factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
      testWhileIdle="true"
      testOnBorrow="true"
      testOnReturn="false"
      validationQuery="SELECT 1 FROM DUAL"
      validationInterval="30000"
      timeBetweenEvictionRunsMillis="30000"
      maxActive="100"
      minIdle="10"
      maxWait="10000"
      initialSize="10"
      removeAbandonedTimeout="60"
      removeAbandoned="true"
      logAbandoned="true"
      minEvictableIdleTimeMillis="30000"
      jmxEnabled="true"
      jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
        org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
      username="your-username"
      password="your-password"
      driverClassName="oracle.jdbc.driver.OracleDriver"
      url="jdbc:oracle:thin:@localhost:1521:xe"/>

This configuration was sufficient to fix the error. This works fine for me in the scenario mentioned above.

Further Readings:

https://github.com/dnvriend/akka-persistence-jdbc/issues/9

Answer 2

Reason:

This error notify an abort of a process, and the first step in resolving this error is to view in the alert log and seek for a trace file.  The "no more data to read from socket" is not a JDBC or an Oracle error. More, it is a generic communications error, mostly as the result of a TNS connectivity issue.

The "no more data to read from socket" error is most often since of an Oracle bug (in 11g) and we recommend moving to the latest release of Oracle.

For errors like this you must employ oracle support. Unfortunately you do not reference what oracle release you are exercising. The error can be concerned to optimizer bind peeking. Relying on the oracle version different workarounds apply.

You have two methods to address this:

1. upgrade to 11.2
2. set oracle parameter 

   _optim_peek_user_binds = false

   Naturally underscore parameters must just be set in case advised by oracle support

Another example: In case you are sending date parameters to a parameterized sql, ensure that you sent java.sql.Timestamp and not java.util.Date. Otherwise you will obtain

java.sql.SQLRecoverableException: No more data to read from socket

Example statement: In our java code, we are employing org.apache.commons.dbutils and we have the following:

final String sqlStatement = "select x from person where date_of_birth between ? and ?";
java.util.Date dtFrom = new Date(); //<-- this will fail
java.util.Date dtTo = new Date();   //<-- this will fail
Object[] params = new Object[]{ dtFrom , dtTo };
final List mapList = (List) query.query(conn, sqlStatement, new MapListHandler(),params); 

The above was impotence until we altered the date parameters to be java.sql.Timestamp

java.sql.Timestamp tFrom = new java.sql.Timestamp (dtFrom.getTime()); //<-- this is OK
java.sql.Timestamp tTo = new java.sql.Timestamp(dtTo.getTime());   //<-- this is OK
Object[] params = new Object[]{ tFrom , tTo };
final List mapList = (List) query.query(conn, sqlStatement, new MapListHandler(),params); 

Attempt two things:

1. Place in $ORACLE_HOME/network/admin/tnsnames.ora on the oracle server server=dedicated to server=shared to approve more than one connection simultaneous. Restart oracle.

2. In case you are employing Java this might help you: In java/jdk1.6.0_31/jre/lib/security/Java.security change securerandom.source=file:/dev/urandom to securerandom.source=file:///dev/urandom