No more data to read from socket error

We are using Oracle as the database for our Web application. The application runs well most of the time, but we get this "No more data to read from socket" error.

Caused by: java.sql.SQLRecoverableException: No more data to read from socket
    at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1142)
    at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1099)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:288)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1869)
    at org.hibernate.loader.Loader.doQuery(Loader.java:718)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
    at org.hibernate.loader.Loader.doList(Loader.java:2449)
    ... 63 more

We use spring, hibernate and i have the following for the datasource in my applciation context file.

<bean class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close" id="dataSource">
        <property name="driverClassName" value="${database.driverClassName}" />
        <property name="url" value="${database.url}" />
        <property name="username" value="${database.username}" />
        <property name="password" value="${database.password}" />
        <property name="defaultAutoCommit" value="false" />
        <property name="initialSize" value="10" />
        <property name="maxActive" value="30" />
        <property name="validationQuery" value="select 1 from dual" />
        <property name="testOnBorrow" value="true" />
        <property name="testOnReturn" value="true" />
        <property name="poolPreparedStatements" value="true" />
        <property name="removeAbandoned" value="true" />
        <property name="logAbandoned" value="true" />
    </bean>

I am not sure whether this is because of application errors, database errors or network errors.

We see the following on the oracle logs

Thu Oct 20 10:29:44 2011
Errors in file d:\oracle\diag\rdbms\ads\ads\trace\ads_ora_3836.trc  (incident=31653):
ORA-03137: TTC protocol internal error : [12333] [4] [195] [3] [] [] [] []
Incident details in: d:\oracle\diag\rdbms\ads\ads\incident\incdir_31653\ads_ora_3836_i31653.trc
Thu Oct 20 10:29:45 2011
Trace dumping is performing id=[cdmp_20111020102945]
Thu Oct 20 10:29:49 2011
Sweep [inc][31653]: completed
Sweep [inc2][31653]: completed
Thu Oct 20 10:34:20 2011
Errors in file d:\oracle\diag\rdbms\ads\ads\trace\ads_ora_860.trc  (incident=31645):
ORA-03137: TTC protocol internal error : [12333] [4] [195] [3] [] [] [] []
Incident details in: d:\oracle\diag\rdbms\ads\ads\incident\incdir_31645\ads_ora_860_i31645.trc
Thu Oct 20 10:34:21 2011

Oracle Version : 11.2.0.1.0

Solution 1:

For errors like this you should involve oracle support. Unfortunately you do not mention what oracle release you are using. The error can be related to optimizer bind peeking. Depending on the oracle version different workarounds apply.

You have two ways to address this:

upgrade to 11.2
set oracle parameter _optim_peek_user_binds = false

Of course underscore parameters should only be set if advised by oracle support

Solution 2:

We were facing same problem, we resolved it by increasing initialSize and maxActive size of connection pool.

You can check this link

Maybe this helps someone.

Solution 3:

Another case: If you are sending date parameters to a parameterized sql, make sure you sent java.sql.Timestamp and not java.util.Date. Otherwise you get

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

Example statement: In our java code, we are using 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 failing until we changed 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);

Solution 4:

This is a very low-level exception, which is ORA-17410.

It may happen for several reasons:

A temporary problem with networking.
Wrong JDBC driver version.
Some issues with a special data structure (on database side).
Database bug.

In my case, it was a bug we hit on the database, which needs to be patched.

Solution 5:

Try two things:

Set in $ORACLE_HOME/network/admin/tnsnames.ora on the oracle server server=dedicated to server=shared to allow more than one connection at a time. Restart oracle.
If you are using 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