6.6. Database Connection Validation

6.6.1. Configure Database Connection Validation Settings

Overview

Database maintenance, network problems, or other outage events may cause JBoss EAP 6 to lose the connection to the database. You enable database connection validation using the <validation> element within the <datasource> section of the server configuration file. Follow the steps below to configure the datasource settings to enable database connection validation in JBoss EAP 6.

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