JDBC datasource not fault tolerant in JBoss and needs connection validation enabled in EAP 4.x/5.x

Solution Verified - Updated

Environment

  • Red Hat JBoss Enterprise Application Platform (EAP)
    • 4.x
    • 5.x
  • Red Hat JBoss Enterprise Portal Platform (EPP)
    • 4.3
    • 5.x
  • Red Hat JBoss SOA Platform (SOA-P)
    • 4.3
    • 5.x

Issue

  • Does the <validation> tag continuously check for the DB to come back alive and continue without having to restart JBoss?
  • After performing database maintenance or an outage JBoss does not reconnect.
  • How to enable connection validator for datasource connection pool in JBoss.
  • We're getting "No operations allowed after connection closed" on our DB connections, how can we make sure the connections are valid before using them?

Resolution

The validation tags will enable checking of the physical connections without having to restart JBoss. This will happen based upon the configuration of the datasource. The connection validator for the datasource can be enabled as follows.

  1. Select one of the following validation settings to tune frequency/timing:

    • Set <validate-on-match>true</validate-on-match>:
      This method will result in the quickest recovery from an outage, but also creates the highest load on the database as it validates the connection each time it is checked out from the pool. This is "true" by default. (NOTE: This is false by default in EAP 6.) If this is set to "true", and a validation mechanism is specified as described in step 2 (below), then if the connection taken from the pool is not valid then another is selected from the pool and validated. If that connection is not valid then another connection from the pool is selected. This process continues until a valid connection can be found. If none of the connections in the pool are valid then a new connection will be created. If creating a new connection fails then an exception will be returned to the application requesting the connection. Note, each time a connection is deemed invalid a WARN message will be issued to the log.

    OR

    • For EAP 5, set <background-validation-millis> to a value greater than 0
      • For EAP 5, <background-validation>true</background-validation> is ignored
      • The default value for background-validation-millis is 0 which disables background validation
    • For EAP 4, set <background-validation>true</background-validation> and configure <background-validation-minutes>
      • The default value for background-validation is false which disables background validation
      • The default value for background-validation-minutes is 10 but this is only in effect if background-validation is set to true

    Background validation reduces load on the database, but will take longer to detect dead connections. When background validation is enabled, set <validate-on-match> to "false." The specified validation period (millis for EAP 5 or minutes for EAP 4) tune how often validation runs. See also:

  2. Select one of the following validation mechanisms:

    • Set <valid-connection-checker-class-name>a.b.c</valid-connection-checker-class-name>:
      This is the preferred mechanism as it is optimized for the particular RDBMS in use. Validation Checkers provided with EAP 4.x and 5.x include:

      • org.jboss.resource.adapter.jdbc.vendor.DB2ValidConnectionChecker
      • org.jboss.resource.adapter.jdbc.vendor.MSSQLValidConnectionChecker
      • org.jboss.resource.adapter.jdbc.vendor.MySQLReplicationValidConnectionChecker
      • org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker
      • org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker
      • org.jboss.resource.adapter.jdbc.vendor.PostgreSQLValidConnectionChecker

    OR

    • Set <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>:
      This is any SQL statement (e.g. select 1 from dual, etc.) you want to use to validate the connection.

    • Note that it is not valid to use both a connection-checker-class and validation sql for the same pool. Select one.

  3. Set <exception-sorter-class-name>x.y.z</exception-sorter-class-name>:
    Use this so that fatal exceptions can be detected properly and the connections that throw them can be cleaned up. When an exception is marked as fatal the connection from which it came is closed immediately, even if the connection is enlisted in a transaction. Configuration of an ExceptionSorter is optional, the connection checker is the important piece. ExceptionSorters that we provide include for EAP 4.x and 5.x:

    • org.jboss.resource.adapter.jdbc.vendor.DB2ExceptionSorter
    • org.jboss.resource.adapter.jdbc.vendor.InformixExceptionSorter
    • org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
    • org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
    • org.jboss.resource.adapter.jdbc.vendor.SybaseExceptionSorter
    • org.jboss.resource.adapter.jdbc.NullExceptionSorter: This is used when no exception sorter is specified. It never marks an exception as fatal.

    Note: The exception sorter org.jboss.resource.adapter.jdbc.GenericExceptionSorter exists in our SVN repository. However, this class is NOT shipped with any version of JBoss EAP.

You can find EAP 4 and 5 example datasource configurations in $JBOSS_HOME/docs/examples/jca/*-ds.xml.

For 6.x refer to EAP 6 JDBC datasource not fault tolerant in JBoss and needs connection validation enabled and This content is not included.Administration_and_Configuration_Guide, Datasource Extensions section.

SBR
Product(s)
Components
Category
Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.