CK's Developer Blog

MySQL connections in a pool times out after a long period of inactivity [I believe 8 hours is the time period by default.] Hence the connections in the pool become stale and this results in exceptions like java.sql.SQLException: Communication link failure.(The same scenario can also be simulated by bringing JBoss up when MySQL is up and running and then restarting MySQL server.) To avoid this we have to instruct JBoss to validate a connection by issuing a test SQL.

Option 1 and the preferred way to do it is to place the following in the datasource configuration file for JBoss

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE datasources
 PUBLIC "-//JBoss//DTD JBOSS JCA Config 1.5//EN"
 "http://www.jboss.org/j2ee/dtd/jboss-ds_1_5.dtd">

<datasources>
 <local-tx-datasource>
 <jndi-name>TestDB</jndi-name>
 <connection-url>jdbc:mysql://localhost:3306/seam</connection-url>
 <driver-class>com.mysql.jdbc.Driver</driver-class>
 <user-name>root</user-name>
 <password>password</password>
 <min-pool-size>4</min-pool-size>
 <max-pool-size>20</max-pool-size>
 <idle-timeout-minutes>10</idle-timeout-minutes>
 <exception-sorter-class-name>
 com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter
 </exception-sorter-class-name>
 <valid-connection-checker-class-name>
 com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker
 </valid-connection-checker-class-name>
 <background-validation>true</background-validation>
 <background-validation-minutes>10</background-validation-minutes>
 <metadata>
 <type-mapping>mySQL</type-mapping>
 </metadata>
 </local-tx-datasource>
</datasources>

Option 2 - We can also validate the connection by using the following.

<check-valid-connection-sql>select 1</check-valid-connection-sql>
 <new-connection-sql>select 1</new-connection-sql>

JBoss documentation suggests Option 1 is the preferred way.