This tutorial discusses how to validate database connections using in WildFly so that you can manage reconnection to the database in case of temporary failures.
Connection Validation in a nutshell
A datasource connection validation helps to ensure that the connections to the database are still valid. The strategy behind connection vaidation is to periodically test connections from the connection pool to see if they are still usable. If a connection fails validation, it is removed from the pool and a new one is created to replace it.
This feature is useful for several reasons:
- It helps detect and prevent stale connections, which can occur after a database restart. It can also happens because of a network connection outage, causing the connections in the pool to become invalid.
- It can also help to detect and prevent potential security issues, as it can detect unauthorized access to the database, lost credentials or malicious access.
For example, consider the Closed Connection error:
Caused by: java.sql.SQLRecoverableException: Closed Connection at oracle.jdbc.driver.OracleStatement.ensureOpen(OracleStatement.java:4051) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3563) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493) at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:111)
This error can happen if there’s something wrong with your firewall / routing settings, and the connections are closed/dropped somewhere in the middle of the route.
To solve this issue, you can manage database connection validation using the validation element within the datasource section of the configuration file. The validation of a connection implies the following steps:
1) Choose a validation method
Firstly, choose between validate-on-match and background-validation.
You can apply the validate-on-match option on your Datasource as follows:
Then, the ExampleDS datasource now includes the following element:
With validate-on-match, 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 invalid, you will see a warning in the log and it retrieves the next connection in the pool. This 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.
Finally, if no valid connection is found a new connection is created. If the connection creation fails, an exception is returned to the requesting application.
Please notice that this setting provides the fastest recovery but creates the highest load on the database, therefore it might not be ideal for performance reasons.
You can apply the background validation setting as follows:
Which produces the following configuration:
The background-validation parameter, however, has been deprecated. It is sufficient to specify the background-validation-millis time in your Datasource configuration to activate it:
It means that a background validation will take place every ms as specified by background-validation-millis. The default value for the background-validation-millis parameter is 0 milliseconds, meaning it is disabled by default. You should not set this attribute to the same value as your idle-timeout-minutes setting.
The lower the value of background-validation-millis, 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. However dead connections could remain in the pool for longer periods of time.
Please note that if the <validate-on-match> option is set to true, the background validation should be disabled. Also the reverse is true.
Finally note that WildFly only validates free connections during each background validation pass. Behind the hoods, each connection has a timestamp for when it was last checked. During a given background validation pass, the “last checked” timestamp is compared to the background-validation-millis value.
Here is a sample datasource which uses background-validation:
<datasource jta="true" jndi-name="java:jboss/datasources/mydb" pool-name="DemoDB" enabled="true" use-ccm="false"> <connection-url>jdbc:mysql://localhost:3306/demodb?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8</connection-url> <driver-class>com.mysql.jdbc.Driver</driver-class> <driver>mysql-connector-java-5.1.31-bin.jar</driver> <security> <user-name>user</user-name> <password>password</password> </security> <validation> <check-valid-connection-sql>select 1</check-valid-connection-sql> <validate-on-match>false</validate-on-match> <background-validation-millis>10000</background-validation-millis> </validation> <statement> <share-prepared-statements>false</share-prepared-statements> </statement> </datasource>
2) Choose a validation mechanism
You can either specify a <valid-connection-checker> Class Name or a <check-valid-connection-sql>. Let’s see both options when using Oracle as Database.
Here is a sample definition:
<validation> <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/> <validate-on-match>true</validate-on-match> </validation>
The JDBC Driver includes out of the box a Connection Checker Class to check if the Connection is Valid. Besides, you can also use a custom Connection Checker by packaging it in a module. Then, you can reference the Custom Connection Checker Class and the module in the data-source CLI as follows:
data-source add --name=MySqlDS --jndi-name=java:/MySqlDS --driver-name=mysql --connection-url=jdbc:mysql://localhost:3306/mydb --user-name=admin --password=admin --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=com.sample.MySQLValidConnectionChecker --valid-connection-checker-module=my.module --exception-sorter-class-name=com.sample.MySQLExceptionSorter --exception-sorter-module=my.module
Here is a sample definition:
<validation> <check-valid-connection-sql>SELECT 1 FROM DUAL</check-valid-connection-sql> <validate-on-match>true</validate-on-match> </validation>
The recommended option is to specify a <valid-connection-checker> Class Name for your specific Database vendor.
How to let JBoss / WildFly automatically reconnect to the DB using validation
Let’s see now a proof of concept example on using validation parameters to reconnect to the DB after a failure. Our initial configuration does not use validation of DB Connections:
<datasource jndi-name="java:/PostGreDS" pool-name="PostgrePool"> <connection-url>jdbc:postgresql://localhost:5432/postgres</connection-url> <driver>postgres</driver> <security> <user-name>postgres</user-name> <password>postgres</password> </security> </datasource>
Within our application, we are using the PostgreSQL Datasource:
@Resource(lookup="java:/PostGreDS") private DataSource ds;
Let’s stop and start the DB and see what happens. If we try accessing the Datasource here is what happens:
12:43:19,596 ERROR [stderr] (default task-1) org.postgresql.util.PSQLException: This connection has been closed. 12:43:19,597 ERROR [stderr] (default task-1) at [email protected]//org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:783) 12:43:19,602 ERROR [stderr] (default task-1) at [email protected]//org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1680)
So, you have attempted to use a Connection which is closed. You can still remedy without an application restart, by flushing connections in the Pool:
However, to discard stale connection automatically, we should add validation checks to our datasource:
<validation> <validate-on-match>true</validate-on-match> <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/> </validation>
When validation checks are in place and a database restart happens, the outage won’t impact your applications. There is however a WARN in your logs which indicates the connection replacement, upon failure in validation:
12:49:57,046 WARN [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (default task-1) IJ000621: Destroying connection that could not be validated: org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@73a8363f[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@2349e1b3 connection handles=0 lastReturned=1612698575441 lastValidated=1612698575417 lastCheckedOut=1612698575417 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@4b6aaa9f mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@4086c17e[pool=PostgrePool] xaResource=LocalXAResourceImpl@256ef25[connectionListener=73a8363f connectionManager=761b50a5 warned=false currentXid=null productName=PostgreSQL productVersion=10.5 (Debian 10.5-2.pgdg90+1) jndiName=java:/PostGreDS] txSync=null]
This article was a complete walk through the datasource validation in JBoss / WildFly. Datasource validation is essential in production environments to allow the datasource reconnection after a database failure.