EAP 6/7 JDBC datasource not fault tolerant in JBoss and needs connection validation enabled

Resolution

Overview

Fault tolerance/validation should be configured for every production datasource. Without this configuration, JBoss will be unable to detect externally terminated connections (i.e. terminated due to database server initiated timeout, network timeout, network interruption, etc.). Without the ability to detect stale/invalid connections, they cannot be evicted from the pool and will be provided (repeatedly) to application components which request connections. This will result in exceptions in the application layer each time these invalid/failed connections are used.

An appropriate fault tolerance/validation configuration will usually consist of the following:

    <validation>
        <!-- 1. The below is the recommended alternative to check-valid-connection-sql for JDBC 4+ compliant drivers.
                DO NOT USE BOTH valid-connection-checker AND check-valid-connection-sql. -->
        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.novendor.JDBC4ValidConnectionChecker"/>

        <!-- 2. Match time validation is recommended in **most** cases, instead of background validation.
                DO NOT USE BOTH validate-on-match AND background-validation. -->
        <validate-on-match>true</validate-on-match>
        <!-- If enabling background validation, **disable** validate-on-match and add background-validation-millis. -->
        <background-validation>false</background-validation>

        <!-- 3. The below is required to facilitate pool flush strategy handling -->
        <exception-sorter class-name="database_specific_ExceptionSorter_here"/>
    </validation>

See below for in-depth discussion and references with examples for specific datasource types.

Discussion

For datasource specific examples demonstrating recommended validation configuration, consult the database connection validation documentation specific to your EAP version:

• EAP 7 Examples
  • EAP 7 Validation Documentation
• EAP 6 Examples
  • EAP 6 Validation Documentation

Detailed information regarding the configuration options can be found below if the recommended configuration is not suitable for a specific system/use case.

• NOTE : that fault tolerance/validation configuration only impacts connections which are not held/reserved by application components.

• To be able to recover from database maintenance or an outage situation, the use of connection validation (both a timing: validate-on-match or background-validation, and a mechanism: valid-connection-checker or check-valid-connection-sql) and the setting of exception-sorter are recommended when defining a datasource.

• You must complete EACH of the following 3 steps to correctly configure the datasource validation

1) Select ONE of the following validation mechanisms:

• <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.novendor.JDBC4ValidConnectionChecker" />

  • This is the preferred mechanism (instead of check-valid-connection-sql) as the validation classes may include driver/RDBMS-specific optimizations ¹.
  • org.jboss.jca.adapters.jdbc.extensions.novendor.JDBC4ValidConnectionChecker
    • This is the recommended class for JDBC 4+ compliant drivers (even where vendor specific validation classes may also exist).
      • Implementation of the Connection.isValid(int) method (by driver vendors) is mandatory for JDBC 4 compliance.
      • Only if problems in the implementation of the method are verified for the specific driver to be used should the vendor specific classes below be considered.
    • WARNING Other validation mechanisms (below) may be subject to long delays due to JDBC driver blocking while accessing stale connections. Consequently, a JVM may not recover for several minutes (or longer) following any loss of connectivity.
    • Note There are indications the isValid(int) test may not timeout on invalid connections in some scenarios when using SQL Server Drivers.
    • A validation timeout may be set explicitly as discussed in the article regarding how to configure custom validation properties. The default timeout is 5 seconds if not otherwise set. Note that enforcing the timeout is the responsibility of the underlying driver implementation.
  • The following may be used as a workaround if problems are encountered with the driver implementation of the Connection.isValid(int) method used by the JDBC4ValidConnectionChecker class:
    • org.jboss.jca.adapters.jdbc.extensions.db2.DB2ValidConnectionChecker
    • org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker
    • org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker
    • org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLReplicationValidConnectionChecker
    • org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker
    • org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker
    • org.jboss.jca.adapters.jdbc.extensions.sybase.SybaseValidConnectionChecker

• <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>

  • DO NOT USE check-valid-connection-sql if a suitable validation class (see above) exists.
  • This may be used to specify an SQL statement to validate the connection for drivers which are not JDBC4 compliant or for which no suitable validation class exists.

NOTE : that it is not valid to use both a valid-connection-checker class and check-valid-connection-sql for the same pool.

2) Select ONE of the following validation methods (timing of the validation):

• <validate-on-match>true</validate-on-match>
  This will validate the connection each time it is checked out from the pool. This is the preferred approach as it can greatly reduce (compared to background-validation) the window of time during which connections may fail between validation and the time the connections are provided to application code. Please refer to this solution for the default values. 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. (Note: If you do not wish to cycle through every connection in the pool you can use the <use-fast-fail> option.) 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.

• <background-validation>true</background-validation>
  When this is set to "true" set validate-on-match to "false." Use <background-validation-millis> to tune how often validation runs.

  Explicit configuration of <background-validation-millis> to a value greater than 0 is MANDATORY when using background validation. If no value is specified, the default value is 0 which will disable validation. There is no generic/universal recommendation for the value. The risk of encountering invalid connections in the application (which may result in transaction failure, rollback and costly retry) must be weighed against the potential cost of background validation for a large set of idle connections (which may generate significant network traffic, etc.). The value must also be configured with consideration for external timeouts configuration (in the network, on the database server, etc.). For instance, if something in the network or database server is configured to time out "idle" connections after 10 minutes, a background validation frequency of 15 or 20 minutes may increase the likelihood of the application encountering connections that have been terminated. Instead, it might be better to configure validation to run every 5 to 7 minutes. This would reduce the chance of missing an externally timed out connection. Because background validation will generate activity on the connections, this will tend to prevent externally initiated timeout. In some cases, configuration of the JBoss EAP idle-timeout-minutes configuration (set lower than any possible external timeout) may work well alongside background validation. Note that the JBoss idle timeout mechanism is not impacted by how often background scans are performed. The "clock" for idle timeout begins the moment a connection is returned to the pool after use and is not reset when background validation occurs for that connection. The <background-validation-millis> should not be the same as your <idle-timeout-minutes> value.

• NOTE : that it is not valid to use both validate-on-match and background-validation for the same pool. Select one. If both are enabled, only validate-on-match works, but background-validation is ignored and not performed in EAP7.

• NOTE ::

  • If the minimal performance hit on each connection request is not a concern then using validate-on-match is preferred rather than background-validation. See also the discussion of the implications of using background-validation vs validate-on-match.
  • The lower the background-validation-millis value the more often the pool will be validated which will take up more database resources. Also the lower the value the sooner invalid connections will be evicted from the pool. On the one hand, too low of a value (meaning validation is occurring frequently) can stress the connection pool resources. On the other hand, if the value is set too high (meaning validation is not occurring frequently enough) then the dead connections will go undetected for longer periods. Therefore it is a balancing act to determine the correct value for a particular system.

3) Set exception sorter <exception-sorter class-name="x.y.z" />

• Use this so that fatal exceptions can be detected properly and the connections that throw them can be cleaned up. When an exception is recognized as fatal by the exception sorter, the connection from which it came is closed immediately, even if the connection is enlisted in a transaction. Notification of a fatal exception is then forwarded to the managing pool in order to enforce the pool flush strategy.

• EAP 6 and later releases provide the following exception sorters:

  • org.jboss.jca.adapters.jdbc.extensions.db2.DB2ExceptionSorter
  • org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter (EAP 6.4 and later only)
  • org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter
  • 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.novendor.ListExceptionSorter (EAP 6.4.8 and later only)

• Some datasource types (in some EAP releases) may lack an exception sorter. Connection validation will work without an exception sorter because it is based on the success or failure of the connection test method (see the validation methods discussed above). Datasources without an exception sorter will use the NullExceptionSorter (which returns false for the boolean isExceptionFatal(SQLException) test) and will be unable to notify the pool of fatal connection errors so flush strategies other than FailingConnectionOnly will be ineffective. For such datasources, the ListExceptionSorter may be used to specify vendor specific error codes used to identify fatal connection errors.

NOTE :

• The above referenced configuration will be placed within the <validation>..</validation> section in the relevant <datasource> section under the datasource subsystem. Please refer to the datasource examples for your EAP version (EAP 7 or EAP 6) for detailed example settings. See below for a simple JBoss CLI examples.
• Is is not necessary to specify a stale-connection-checker class unless the application is specifically coded to check for stale connection exceptions.

Create a New Fault Tolerant Datasource using JBoss CLI

# Standalone configuration example for *new* datasource (omitting connection-url, user-name, password, driver-name, etc.)
[standalone@localhost:9999 /] data-source add --name=Oracle --jndi-name=java:jboss/datasources/oracle ... --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.novendor.JDBC4ValidConnectionChecker --validate-on-match=true --background-validation=false --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter --enabled=true

Add Fault Tolerance to an Existing Datasource using JBoss CLI

# Standalone configuration example for existing datasource pool named 'Oracle'
[standalone@localhost:9999 /] /subsystem=datasources/data-source=Oracle:write-attribute(name=valid-connection-checker-class-name, value=org.jboss.jca.adapters.jdbc.extensions.novendor.JDBC4ValidConnectionChecker)
{
    "outcome" => "success",
    "response-headers" => {
        "operation-requires-reload" => true,
        "process-state" => "reload-required"
    }
}
[standalone@localhost:9999 /] /subsystem=datasources/data-source=Oracle:write-attribute(name=validate-on-match, value=true)
{
    "outcome" => "success",
    "response-headers" => {
        "operation-requires-reload" => true,
        "process-state" => "reload-required"
    }
}
[standalone@localhost:9999 /] /subsystem=datasources/data-source=Oracle:write-attribute(name=background-validation, value=false)
{
    "outcome" => "success",
    "response-headers" => {
        "operation-requires-reload" => true,
        "process-state" => "reload-required"
    }
}
[standalone@localhost:9999 /] /subsystem=datasources/data-source=Oracle:write-attribute(name=exception-sorter-class-name, value=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter)
{
    "outcome" => "success",
    "response-headers" => {
        "operation-requires-reload" => true,
        "process-state" => "reload-required"
    }
}
[standalone@localhost:9999 /] reload

Configure Fault Tolerance for a JBoss EAP for OpenShift Image

With current JBoss EAP for OpenShift images published in the Red Hat respository, the following environment parameters may be used to configure fault tolerance².

• <DATASOURCE_PREFIX>_CONNECTION_CHECKER=<DATASOURCE_TYPE_SPECIFIC_CONNECTION_CHECKER_CLASS>
• <DATASOURCE_PREFIX>_EXCEPTION_SORTER=<DATASOURCE_TYPE_SPECIFIC_EXCEPTION_SORTER_CLASS>

For instance with an external Oracle datasource for which the prefix is ORADS the following would be used:

• ORADS_CONNECTION_CHECKER=org.jboss.jca.adapters.jdbc.extensions.novendor.JDBC4ValidConnectionChecker
• ORADS_EXCEPTION_SORTER=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter

Note that this will configure the default mechanism which uses validate-on-match. If background-validation is desired (background validation is not usually recommended), additional properties should be used to disable the recommended configuration and use background validation instead:

• <DATASOURCE_PREFIX>_BACKGROUND_VALIDATION=true
• <DATASOURCE_PREFIX>_BACKGROUND_VALIDATION_MILLIS=120000 (this will set the validation frequency to 2 minutes, the default if not specified explicitly is 10000 milliseconds/10 seconds which is likely too frequent for most systems)

Depending on how the container is created/launched it might be necessary to provide the configuration:

• In an environment property file using --env-file <file>
• Using individual --env <PROPERTY>=<VALUE> parameters

• Using a custom standalone-openshift.xml file (see discussion/examples of XML configuration above)

• NOTE : Has a datasource been affected and does the boss have caching behavior for the datasource connection? does this mean that one must restart the server after changing the password? Yes, because the changes are not yet at runtime. Also, the connection pool reservation had already been made with the old password.