No more data to read from socket error

67
votes

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

12
oraclehibernatespringjdbc
It looks like your Oracle server rudely disconnected your application connection while it was reading some sort of resultset. - O. Jones
This error most likely occurs in applications that use a database connections pool. When the application checked out a connection that has been timed out or has been staled, and used it to connect to the database, this error occurs. - Ritesh Mengji
@User67546 I have set the connection pooling configuration to validate the connection before being used. Shouldnt that disregard the stale connections - Kathir
Do you get exactly the same error and stack? - steve
keywords for google: in german: "Keine weiteren Daten aus Socket zu lesen" - Andreas Covidiot

12 Answers

32
votes

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

9
votes

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.

8
votes

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);
7
votes

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

It may happen for several reasons:

  1. A temporary problem with networking.

  2. Wrong JDBC driver version.

  3. Some issues with a special data structure (on database side).

  4. Database bug.

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

5
votes

Try two things:

  1. 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.
  2. 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
4
votes

I had the same problem. I was able to solve the problem from application side, under the following scenario:

JDK8, spring framework 4.2.4.RELEASE, apache tomcat 7.0.63, Oracle Database 11g Enterprise Edition 11.2.0.4.0

I used the database connection pooling apache tomcat-jdbc:

You can 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.

For more details about the setup apache tomcat-jdbc: https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

3
votes

Downgrading the JRE from 7 to 6 fixed this issue for me.

1
votes

In our case we had a query which loads multiple items with select * from x where something in (...) The in part was so long for benchmark test.(17mb as text query). Query is valid but text so long. Shortening the query solved the problem.

0
votes

Yes, as @ggkmath said, sometimes a good old restart is exactly what you need. Like when "contact the author and have him rewrite the app, meanwhile wait" is not an option.

This happens when an application is not written (yet) in a way that it can handle restarts of the underlying database.

0
votes

I seemed to fix my instance by removing the parameter placeholder for a parameterized query.

For some reason, using these placeholders were working fine, and then they stopped working and I got the error/bug.

As a workaround, I substituted literals for my placeholders and it started working.

Remove this

where 
    SOME_VAR = :1

Use this

where 
    SOME_VAR = 'Value'
0
votes

Seemed to be an issue with a view. JDBC query was using a view. I took a guess, recompiled the view and error is gone.

-1
votes

I got this error then restarted my GlassFish server that held connection pools between my client app and the database, and the error went away. So, try restarting your application server if applicable.