When using OracleDataSource in Spring Framework, the application reports an SQLException with the error: Invalid or Stale Connection found

Products

• VMware Spring Runtime

Issue/Introduction

Symptoms:

• When using the SpringSource Advanced Pack for Oracle, Spring Data's JDBC Extensions for Oracle, or OracleDataSource in your Spring configuration, the application reports an SQLException
• You see the error:
  
  Invalid or Stale Connection found

Environment

Spring Framework 2.5
Spring Framework 3.0
Spring Framework 3.1

Cause

The SQL Exception with the Invalid or Stale Connection found error occurs when a connection in the connection pool is no longer actively connected to the database. This may occur due to one of these reasons:

• When a connection is manually disconnected from the database, probably by an administrator.
• When a connection exists in the connection pool without being used for an extended period of time and is disconnected due to a timeout.
• When the database is restarted, probably for maintenance purposes.
• 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.

Resolution

To resolve the issue, try one of these options:

• Set the ValidateConnection connection cache property to true. This forces the pool to validate a connection before it is given to your application. Any invalid or stale connections are dropped and replaced by new valid connections.
  
  Example: Using orcl schema of Spring AP for Oracle and Spring Data JDBC Extensions
  
  <orcl:pooling-datasource id="dataSource" properties-location="classpath:orcl.properties">
  <orcl:connection-cache-properties>
  ValidateConnection=true
  </orcl:connection-cache-properties>
  </orcl:pooling-datasource>
  
  
• If the connection is dropped due to a timeout, set the InactivityTimeout property that configures the time period (in seconds) within which a connection exists in the cache unused. When a connection exceeds this value, it is automatically closed.
  
  Example: Using orcl schema of Spring AP for Oracle and Spring Data JDBC Extensions
  
  <orcl:pooling-datasource id="dataSource" properties-location="classpath:orcl.properties">
  <orcl:connection-cache-properties>
  InactivityTimeout=60
  </orcl:connection-cache-properties>
  </orcl:pooling-datasource>

Additional Information

For more information, see Connection Cache Properties.

Note: The preceding link was correct as of June 06, 2012. If you find the link is broken, provide feedback and a VMware employee will update the link.