Handle MySQL stale connections

Instructions for how to configure the application server to avoid MySQL timeout issues.

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.

The following sections provide the configuration elements specific to each application server we can use to avoid this exception if we use the MySQL database.

Apache Tomcat configuration

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"
/>

WAS Liberty profile configuration

MySQL in combination with WAS Liberty profile or WAS full profile is not classified as a supported configuration. See WebSphere Application Server Support Statement. We suggest that we use IBM DB2 or another database supported by WebSphere Application Server to benefit from a configuration that is fully supported by IBM Support.

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"/>

For example:

<dataSource jndiName="jdbc/AppCenterDS" transactional="false"> 
  <connectionManager agedTimeout="7h30m"/>
  <jdbcDriver libraryRef="MySQLLib"/>
  ...
</dataSource>

WAS full profile configuration

MySQL in combination with WAS Liberty profile or WAS full profile is not classified as a supported configuration. See WebSphere Application Server Support Statement. We suggest that we use IBM DB2 or another database supported by WebSphere Application Server to benefit from a configuration that is fully supported by IBM Support.

1. Log in to the WebSphere Application Server 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 that the MySQL wait_timeout system variable to have the connections purged prior to the time that MySQL closes these connections.

   4. Click OK.

Parent topic: Configure MobileFirst Server