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

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

book

Article ID: 338932

calendar_today

Updated On:

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.