spring 没有更多的数据可以从套接字错误中读取
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7839907/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
No more data to read from socket error
提问by Kathir
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.
我们使用 Oracle 作为 Web 应用程序的数据库。应用程序大部分时间运行良好，但我们收到“没有更多数据可从套接字读取”错误。
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.
我们使用 spring、hibernate，我的应用程序上下文文件中有以下数据源。
<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
我们在oracle日志上看到以下内容
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
甲骨文版本：11.2.0.1.0
采纳答案by steve
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.
对于这样的错误，您应该涉及 oracle 支持。不幸的是，您没有提及您使用的是哪个 oracle 版本。该错误可能与优化器绑定偷看有关。根据不同的 oracle 版本，适用不同的解决方法。
You have two ways to address this:
你有两种方法来解决这个问题：
- upgrade to 11.2
- set oracle parameter
_optim_peek_user_binds = false
- 升级到11.2
- 设置oracle参数
_optim_peek_user_binds = false
Of course underscore parameters should only be set if advised by oracle support
当然下划线参数应该只在 oracle 支持建议的情况下设置
回答by fyelci
回答by chrisl08
Another case: If you are sending date parameters to a parameterized sql, make sure you sent java.sql.Timestampand not java.util.Date. Otherwise you get
另一种情况：如果您将日期参数发送到参数化的 sql，请确保您发送java.sql.Timestamp而不是java.util.Date. 否则你得到
java.sql.SQLRecoverableException: No more data to read from socket
java.sql.SQLRecoverableException: 没有更多的数据要从套接字读取
Example statement:
In our java code, we are using org.apache.commons.dbutilsand we have the following:
示例语句：在我们的 java 代码中，我们使用的org.apache.commons.dbutils是以下内容：
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
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);
回答by Richard
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.securitychangesecurerandom.source=file:/dev/urandomtosecurerandom.source=file:///dev/urandom
- 在 oracle 服务器上的 $ORACLE_HOME/network/admin/tnsnames.ora 中设置 server=dedicated to server=shared 以允许一次多个连接。重启甲骨文。
- 如果您使用的是 Java，这可能会对您有所帮助：
java/jdk1.6.0_31/jre/lib/security/Java.security更改securerandom.source=file:/dev/urandom为securerandom.source=file:///dev/urandom
回答by devwebcl
This is a very low level exception, which is ORA-17410.
这是一个非常低级别的异常，即 ORA-17410。
It may happens by several reasons:
它可能由以下几个原因发生：
A temporary problem on networking.
Wrong JDBC driver version.
Some issues with special data structure (on database side).
Database bug.
网络上的临时问题。
错误的 JDBC 驱动程序版本。
特殊数据结构的一些问题（在数据库端）。
数据库错误。
In my case, it was a bug we hit on the database, which needs to be patched.
就我而言，这是我们在数据库上遇到的一个错误，需要修补。
回答by JUAN CALVOPINA M
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
JDK8、spring框架4.2.4.RELEASE、apache tomcat 7.0.63、Oracle Database 11g企业版11.2.0.4.0
I used the database connection pooling apache tomcat-jdbc:
我使用了数据库连接池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
有关设置 apache tomcat-jdbc 的更多详细信息：https: //tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
回答by johndemic
Downgrading the JRE from 7 to 6 fixed this issue for me.
将 JRE 从 7 降级到 6 为我解决了这个问题。
回答by Jaroslav Záruba
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.
是的，正如@ggkmath 所说，有时一个好的旧重启正是您所需要的。就像“联系作者并让他重写应用程序，同时等待”不是一种选择。
This happens when an application is not written (yet) in a way that it can handle restarts of the underlying database.
当应用程序（尚未）以可以处理底层数据库重启的方式编写时，就会发生这种情况。
回答by mcelikkaya
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.
在我们的例子中，我们有一个查询，它使用 select * from x where something in (...) 加载多个项目，其中部分对于基准测试来说太长了。（17mb 作为文本查询）。查询有效，但文本太长。缩短查询解决了问题。
回答by Kalin
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'
