java.sql.SQLException: Invalid or Stale Connection found in the Connection Cache

Question

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

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

Answer 1

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

1. Connection is manually aborted from the database by a dba. For example, if the connection was killed using "ALTER SYSTEM KILL SESSION"
2. 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)
3. A database restart
4. 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.

If you are setting the InactivityTimeout, then you have to make sure that it's less that the IDLE_TIME enforced by the database. You can get the IDLE_TIME with the below query

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 PropertyCheckInterval property 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.

I would always make sure that I use 0 as the 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.