6.6. Database Connection Validation

Procedure 6.12. Configure Database Connection Validation Settings

1. Choose a Validation Method

   Select one of the following validation methods.

   • <validate-on-match>true</validate-on-match>

     When the <validate-on-match> option is set to true, the database connection is validated every time it is checked out from the connection pool using the validation mechanism specified in the next step.
     If a connection is not valid, a warning is written to the log and it retrieves the next connection in the pool. This process continues until a valid connection is found. If you prefer not to cycle through every connection in the pool, you can use the <use-fast-fail> option. If a valid connection is not found in the pool, a new connection is created. If the connection creation fails, an exception is returned to the requesting application.
     This setting results in the quickest recovery but creates the highest load on the database. However, this is the safest selection if the minimal performance hit is not a concern.

   • <background-validation>true</background-validation>

     When the <background-validation> option is set to true, it is used in combination with the <background-validation-millis> value to determine how often background validation runs. The default value for the <background-validation-millis> parameter is 0 milliseconds, meaning it is disabled by default. This value should not be set to the same value as your <idle-timeout-minutes> setting.
     It is a balancing act to determine the optimum <background-validation-millis> value for a particular system. The lower the value, the more frequently the pool is validated and the sooner invalid connections are removed from the pool. However, lower values take more database resources. Higher values result in less frequent connection validation checks and use less database resources, but dead connections are undetected for longer periods of time.

   Note

   If the <validate-on-match> option is set to true, the <background-validation> option should be set to false. The reverse is also true. If the <background-validation> option is set to true, the <validate-on-match> option should be set to false.

2. Choose a Validation Mechanism

   Select one of the following validation mechanisms.

   • Specify a <valid-connection-checker> Class Name

     This is the preferred mechanism as it optimized for the particular RDBMS in use. JBoss EAP 6 provides the following connection checkers:

     • org.jboss.jca.adapters.jdbc.extensions.db2.DB2ValidConnectionChecker
     • org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker
     • org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLReplicationValidConnectionChecker
     • org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker
     • org.jboss.jca.adapters.jdbc.extensions.novendor.JDBC4ValidConnectionChecker
     • org.jboss.jca.adapters.jdbc.extensions.novendor.NullValidConnectionChecker
     • org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker
     • org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker
     • org.jboss.jca.adapters.jdbc.extensions.sybase.SybaseValidConnectionChecker

   • Specify SQL for <check-valid-connection-sql>

     You provide the SQL statement used to validate the connection.
     The following is an example of how you might specify a SQL statement to validate a connection for Oracle:

     <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>

     For MySQL or PostgreSQL, you might specify the following SQL statement:

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

3. Set the <exception-sorter> Class Name

   When an exception is marked as fatal, the connection is closed immediately, even if the connection is participating in a transaction. Use the exception sorter class option to properly detect and clean up after fatal connection exceptions. JBoss EAP 6 provides the following exception sorters:

   • org.jboss.jca.adapters.jdbc.extensions.db2.DB2ExceptionSorter
   • org.jboss.jca.adapters.jdbc.extensions.informix.InformixExceptionSorter
   • org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter
   • org.jboss.jca.adapters.jdbc.extensions.novendor.NullExceptionSorter
   • org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter
   • org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter
   • org.jboss.jca.adapters.jdbc.extensions.sybase.SybaseExceptionSorter
   • org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter