java.sql.SQLException: 在连接缓存中发现无效或过时的连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25589694/
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
java.sql.SQLException: Invalid or Stale Connection found in the Connection Cache
提问by Ahmed Wahbi
I am using spring framework 3.2 with hibernate 4 , I get the above exception when sending a request after a long idle time on the local server ( apache-tomcat v7.0 ) and the database is located on remote server. After hours of search I came to that the problem comes from the connection pool. I tried number of connection pools but didn't find the satisfying solution. bellow is the current datasource on my spring-data file
我正在使用带有 hibernate 4 的 spring 框架 3.2，在本地服务器 (apache-tomcat v7.0) 上长时间空闲后发送请求并且数据库位于远程服务器上时，我收到上述异常。经过数小时的搜索，我发现问题来自连接池。我尝试了多个连接池，但没有找到令人满意的解决方案。下面是我的 spring-data 文件中的当前数据源
<bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource"
destroy-method="close">
<property name="connectionCachingEnabled" value="true" />
<property name="URL" value="${app.jdbc.url}" />
<property name="user" value="${app.jdbc.username}" />
<property name="password" value="${app.jdbc.password}" />
<property name="connectionCacheProperties">
<value>
MinLimit:70
MaxLimit:200
InitialLimit:20
ConnectionWaitTimeout:120
InactivityTimeout:180
ValidateConnection:true
</value>
</property>
</bean>
please advise.
请指教。
回答by coderplus
You will get the "Invalid or Stale Connection" error when you have a connection in the connection pool which is no longer connected to the Database actively. Below are few scenarios which can lead to this
当连接池中有一个不再主动连接到数据库的连接时，您将收到“无效或陈旧的连接”错误。以下是可能导致这种情况的几种情况
- Connection is manually aborted from the database by a dba. For example, if the connection was killed using "ALTER SYSTEM KILL SESSION"
- When a connection exists in the connection pool without being used for a long time and is disconnected due to the timeouts enforced by the database (idle_time)
- A database restart
- A network event has caused the connection to drop, probably because the network has become unavailable or a firewall has dropped a connection which has been open for too long.
- 连接由 dba 手动从数据库中止。例如，如果使用“ALTER SYSTEM KILL SESSION”终止连接
- 当连接池中存在一个连接长时间未使用，由于数据库强制超时而断开连接时（idle_time）
- 数据库重启
- 网络事件导致连接断开，可能是因为网络变得不可用或防火墙断开了打开时间过长的连接。
If you are setting the InactivityTimeout, then you have to make sure that it's less that the IDLE_TIMEenforced by the database. You can get the IDLE_TIMEwith the below query
如果您正在设置InactivityTimeout，那么您必须确保它小于IDLE_TIME数据库强制执行的。您可以通过IDLE_TIME以下查询获得
select * from dba_profiles dp, dba_users du
where dp.profile = du.profile and du.username ='YOUR_JDBC_USER_NAME';
When you use connectionCacheProperties, always make sure that you set the PropertyCheckIntervalproperty to something less than the timeouts. The default value is 900 seconds which means that the cache daemon thread will only run every 15 minutes and enforce the timeouts. So you would always want to set this to a value lower than your timeout properties.
使用 connectionCacheProperties 时，请始终确保将PropertyCheckInterval属性设置为小于超时的值。默认值为 900 秒，这意味着缓存守护程序线程将仅每 15 分钟运行一次并强制执行超时。因此，您总是希望将其设置为低于超时属性的值。
I would always make sure that I use 0 as the MinLimit.
我将始终确保使用 0 作为 MinLimit。
Rewriting you config file a little bit would make it :
稍微重写您的配置文件将使它：
<bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close">
<property name="connectionCachingEnabled" value="true" />
<property name="URL" value="${app.jdbc.url}" />
<property name="user" value="${app.jdbc.username}" />
<property name="password" value="${app.jdbc.password}" />
<property name="connectionCacheProperties">
<props merge="default">
<prop key="MinLimit">0</prop>
<prop key="MaxLimit">200</prop>
<prop key="InitialLimit">1</prop>
<prop key="ConnectionWaitTimeout">120</prop>
<prop key="InactivityTimeout">180</prop>
<prop key="ValidateConnection">true</prop>
<prop key="PropertyCheckInterval">150</prop>
</props>
</property>
</bean>
You might also get a "Invalid or Stale Connection Error" when your network is actually broken at the time when you try to validate an old connection obtained from the pool.
当您尝试验证从池中获得的旧连接时，您的网络实际上已损坏，您也可能会收到“无效或陈旧的连接错误”。
回答by Shailendra
connectionCachingEnabledis the key here. By setting it to true you are using implicit connection cache (kind of proprietary Oracle connection pooling) which caches the connection.
But with ValidateConnectionthe connection should have been validated. You mentioned you tried different connection pools. Almost all connection pools like (commons dbcp, c3p0, tomcat dbcp) have this facility of validating connections before handing over to the application. For e.g., Tomcat DBCPhas property testOnBorrowalong with validationIntervaland validationQuery. Other pools too have similar property. With other pools too are you getting the same issue ?
connectionCachingEnabled是这里的关键。通过将其设置为 true，您将使用缓存连接的隐式连接缓存（一种专有的 Oracle 连接池）。但与ValidateConnection连接应该已经过验证。您提到您尝试了不同的连接池。几乎所有连接池（如commons dbcp、c3p0、tomcat dbcp）都具有在移交给应用程序之前验证连接的功能。例如，Tomcat DBCP具有testOnBorrow与validationInterval和一起的属性validationQuery。其他池也有类似的属性。对于其他游泳池，您是否也遇到同样的问题？
回答by stones333
Yes, I have seen the error
是的，我看到了错误
java.sql.SQLException: Invalid or Stale Connection found in the Connection Cache.
After switch to Oracle's ucp, I haven't seen any Stale connections since. I have setting like following.
切换到 Oracle 的 ucp 后，我再也没有看到任何过时的连接。我有如下设置。
<New id="DS" class="org.eclipse.jetty.plus.jndi.Resource">
<Arg></Arg>
<Arg>OracleDS</Arg>
<Arg>
<New class="oracle.ucp.jdbc.PoolDataSourceImpl">
<Set name="URL">jdbc:oracle:thin:@abc.corp.com:1234:xyz</Set>
<Set name="user">owner</Set>
<Set name="password”>pwd</Set>
<Set name="connectionFactoryClassName">oracle.jdbc.pool.OracleDataSource</Set>
<Set name="minPoolSize">0</Set>
<Set name="maxPoolSize">10</Set>
<Set name="inactiveConnectionTimeout">300</Set>
<Set name="maxStatements">200</Set>
<Set name="maxConnectionReuseCount">150</Set>
<Set name="connectionWaitTimeout">9</Set>
<Set name="abandonedConnectionTimeout">30</Set>
<Set name="validateConnectionOnBorrow">true</Set>
<Set name="SQLForValidateConnection">SELECT SYSDATE FROM DUAL</Set>
</New>
</Arg>
</New>
