For up-to-date product documentation, see the IBM MobileFirst Foundation Developer Center.


Handling stale connections

Configure your application server to avoid database timeout issues.

A StaleConnectionException is an exception that is generated by the Java™ application server profile database connection code when a JDBC driver returns an unrecoverable error from a connection request or operation. The StaleConnectionException is raised when the database vendor issues an exception to indicate that a connection currently in the connection pool is no longer valid. This exception can happen for many reasons. The most common cause of StaleConnectionException is due to retrieving connections from the database connection pool and finding out that the connection has timed out or dropped when it was unused for a long time.

We can configure your application server to avoid this exception.


Apache Tomcat configuration

MySQL
The MySQL database closes its connections after a period of non-activity on a connection. This timeout is defined by the system variable called wait_timeout. The default is 28000 seconds (8 hours).

When an application tries to connect to the database after MySQL closes the connection, the following exception is generated:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.

Edit the server.xml and context.xml files, and for every <Resource> element add the following properties:

  • testOnBorrow="true"
  • validationQuery="select 1"
For example:

<Resource name="jdbc/AppCenterDS" type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver" ... testOnBorrow="true" validationQuery="select 1" />


WebSphere Application Server Liberty profile configuration

Edit the server.xml file and for every <dataSource> element (runtime and Application Center databases) add a <connectionManager> element with the agedTimeout property:

<connectionManager agedTimeout="timeout_value"/>

The timeout value depends mainly on the number of opened connections in parallel but also on the minimum and maximum number of the connections in the pool. Hence, we must tune the different connectionManager attributes to identify the most adequate values. For more information about the connectionManager element, see Liberty: Configuration elements in the server.xml file .

Note: MySQL in combination with WebSphere® Application Server Liberty profile or WebSphere Application Server full profile is not classified as a supported configuration. For more information, see WebSphere Application Server Support Statement. Use IBM® DB2® or another database that is supported by WebSphere Application Server to benefit from a configuration that is fully supported by IBM Support.


WebSphere Application Server full profile configuration

DB2 or Oracle
To minimize the stale connection issues, check the connection pools configuration on each data source in WebSphere Application Server administration console.
  1. Log in to the WebSphere Application Server administration console.
  2. Select Resources > JDBC Providers > database_jdbc_provider > Data Sources > your_data_source > Connection pool properties.
  3. Set the Minimum connections value to 0.
  4. Set the Reap time value to be lesser than the Unused timeout value.
  5. Make sure that the Purge policy property is set to EntirePool (default).
For more information, see Connection pool settings.
MySQL

  1. Log in to the WebSphere Application Server administration console.
  2. Select Resources > JDBC > Data sources.
  3. For each MySQL data source:
    1. Click the data source.
    2. Select Connection pool properties under Additional Properties.
    3. Modify the value of the Aged timeout property. The value must be lower than the MySQL wait_timeout system variable so that the connections are purged before MySQL closes these connections.
    4. Click OK.

Note: MySQL in combination with WebSphere Application Server Liberty profile or WebSphere Application Server full profile is not classified as a supported configuration. For more information, see WebSphere Application Server Support Statement. Use IBM DB2 or another database that is supported by WebSphere Application Server to benefit from a configuration that is fully supported by IBM Support.

Parent topic: Configuring data sources