Clever Geek Handbook

Java.sql.SQLException: Invalid or old connection found in connection cache

I am using spring framework 3.2 with hibernate 4, I am getting the above exception when submitting a request after a long idle time on the local server (apache-tomcat v7.0) and the database is on a remote server. After hours of searching, I came to the conclusion that the problem is coming from the connection pool. I tried the number of connection pools but didn't find a satisfactory solution. Below is the current datasource in spring-data file p>

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

+3


source to share


3 answers


When connecting to a connection pool that is no longer connected to the database, you will receive a "Invalid or old connection" error. Below are a few scenarios that can lead to this.

  • The connection is manually terminated from the database using dba. For example, if the connection was killed with "ALTER SYSTEM KILL SESSION"
  • When a connection exists in a connection pool that has not been used for a long time and is disconnected due to timeouts, database (idle_time)
  • Reloading the database
  • A network event triggered a hop connection, likely because the network became unavailable or the firewall disconnected a connection that was open for too long.

If you are installing InactivityTimeout

, then you must make sure that it is less than what IDLE_TIME

is entered into the database. You can get 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 you set the property PropertyCheckInterval

to less than the timeouts. The default is 900 seconds, which means that the cache daemon thread will run every 15 minutes and use timeouts. Therefore, you would always like to set this value lower than your timeout properties.



I would always be sure that I am using 0 as MinLimit.

Rewriting the config file again:

<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 may also receive a "Connection Outdated" or "Connection Outdated" error message when your network was actually corrupted while checking the old connection obtained from the pool.

+6


source


connectionCachingEnabled

is the key. By setting it to true, you are using an implicit connection cache (like Oracle's proprietary connection pool) that caches the connection. But the ValidateConnection

connection must be verified. You mentioned that you have tried different connection pools. Almost all connection pools such as ( commons dbcp , c3p0 , tomcat dbcp ) have this way of checking connections before passing them to the application. For example, Tomcat DBCP has a property testOnBorrow

along with validationInterval

and validationQuery

. Other pools have similar properties too. Are you getting the same problem with other pools too?



+2


source


Yes i saw the error

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

After switching to Oracle ucp, I haven't seen any legacy connections since then. I have setup as follows.

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


source






More articles:


All Articles