No more data to read from socket error

Question

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

It looks like your Oracle server rudely disconnected your application connection while it was reading some sort of resultset. — O. Jones, Oct 20 '11 at 20:11
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, Oct 20 '11 at 20:34
@User67546 I have set the connection pooling configuration to validate the connection before being used. Shouldnt that disregard the stale connections — Kathir, Oct 20 '11 at 21:15
keywords for google: in german: *"Keine weiteren Daten aus Socket zu lesen"* — Andreas Covidiot, Nov 30 '16 at 09:12
sometimes it seems to even have a general ORA number assigned: *"ORA-17410 No more data from socket"*: more on the problem also from Burleson: http://www.dba-oracle.com/t_sqlexception_17410_no_more_data.htm — Andreas Covidiot, Nov 30 '16 at 09:17
This answer http://stackoverflow.com/a/16964640/119741 fixed it — Nick, Feb 08 '17 at 01:24

score 35 · Accepted Answer · edited Jan 04 '17 at 23:20

35

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

edited Jan 04 '17 at 23:20

Igor Kustov

3,228
2
34
31

answered Oct 21 '11 at 04:28

steve

5,870
1
21
22

Have contacted oracle and it is a _optim_peek_user_binds issue and we had to either upgrade or patch it. – Kathir Nov 17 '11 at 18:15
6

I still get this error on 11.2.0.2.0 - 64bit, after restart oracle db - pool not reconnerct.. – Akvel Feb 01 '13 at 04:34
1

This error message is indicates a crash on the server-side. There are lots of these, so without checking the server-side logs any fixes are just a guess. Upgrading to the latest supported JDBC client is probably a good idea most of the time. – WW. Jan 11 '16 at 05:10
34

Replacing my installation of Oracle with a modern database management system worked for me. – K. Alan Bates Jan 25 '17 at 17:32
1

logged into stackoverflow only to upvote @K.AlanBates comment – Repoker Oct 31 '18 at 10:12

score 10 · Answer 2 · answered Mar 19 '14 at 09:20

10

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.

answered Mar 19 '14 at 09:20

fyelci

1,399
2
16
27

devwebcl · Answer 3 · 2020-06-28T22:27:35.617

10

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.

edited Jun 28 '20 at 22:27

answered Apr 03 '18 at 13:45

devwebcl

2,866
3
27
46

chrisl08 · Answer 4 · 2017-07-17T11:30:35.717

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

score 5 · Answer 5 · edited Nov 23 '12 at 23:46

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

edited Nov 23 '12 at 23:46

Peter O.

32,158
14
82
96

answered Nov 23 '12 at 23:27

Richard

59
1
1

score 4 · Answer 6 · edited Jul 15 '17 at 21:44

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

I am using JDK1.8,SpringJDBCTemplate,OracleDB,SpringRestService & org.commons.dbcp.BasicDataSource. Getting this error intermittently. Will I get any issue if use the above given properties? — Jack, Jan 29 '20 at 04:39

score 3 · Answer 7 · answered Jul 02 '14 at 16:21

3

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

answered Jul 02 '14 at 16:21

johndemic

53
5

2

Just a thought, maybe that worked because of the JDBC driver version: 11.2.0.4 support JDK 6, 7 and 8, but 11.2.0.3 only JDK 6 http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html – maxivis Jan 07 '16 at 19:34

score 1 · Answer 8 · answered Sep 20 '17 at 12:01

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.

score 1 · Answer 9 · answered Oct 19 '18 at 02:18

1

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.

answered Oct 19 '18 at 02:18

mcelikkaya

315
4
13

score 0 · Answer 10 · answered Oct 18 '13 at 21:30

0

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.

answered Oct 18 '13 at 21:30

ggkmath

4,188
23
72
129

9

Yes, problem goes away when you restart your app, but this is not an anwer to a question, and you cannot keep doing restarts all the time that in production. – javagirl Jun 03 '14 at 09:59
1

This is actually a perfectly legit answer. When the db gets restarted sometimes the depending backend/application does not handle it properly. So yes, the app should be fixed, but that does not help you at the moment when you need the app to respond. Restarting the app does. – Jaroslav Záruba Sep 20 '17 at 11:44
3

@javagirl And what was the question you are referring to? The OP actually did not phrase *any* question. He did not ask "how should we rewrite the app?" And this answer also does not suggest "this is a long-term solution." When tens of people are waiting for the app to start responding you don't tell them "rewrite it." – Jaroslav Záruba Sep 20 '17 at 11:48

score 0 · Answer 11 · answered Jul 20 '19 at 00:35

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'

score 0 · Answer 12 · answered Sep 17 '21 at 16:02

0

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.

answered Sep 17 '21 at 16:02

Dan Poulos

41
2

score -1 · Answer 13 · answered Apr 11 '22 at 17:38

In my case the error occurs running a simple query like this in SQLdeveloper:

select count(1) from tabel1 inner join tabel2 on table1.id = table2.id_table1 ;

I solved the error so...

select 
/*+OPT_PARAM('_index_join_enabled' 'false') */  
count(1) from tabel1 inner join tabel2 on table1.id = table2.id_table1 ;

No more data to read from socket error

13 Answers13

Linked

Related