Automatically reconnect to a database after a failure in JBoss EAP 7

My JBoss EAP 7 server couldn’t cope with a failing database. After a database restart or failure, e.g. due to maintenance, it simply would not connect to the database again automatically. The application simply stopped working as soon as the database was unavailable for a short period of time.

JBoss’s server.log was full of (not very helpful) error messages like this:

2017-03-01 12:05:18,175 ERROR [it.macke.repository.UserRepository] (default task-17) Error reading user: org.hibernate.exception.JDBCConnectionException: could not prepare statement: javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: could not prepare statement
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
...
Caused by: org.hibernate.exception.JDBCConnectionException: could not prepare statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:48)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
...
Caused by: java.sql.SQLNonTransientConnectionException: Connection is close
    at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:123)

To make the application work again, I had to restart JBoss and every now and then even the whole Windows server on which it runs.

As it turns out, this is a common problem with JBoss. However, the solution is quite easy. You only need to configure JBoss to validate the database connections. Here’s how this would look in standalone.xml (take a look at the content of element validation):

<datasource jndi-name="java:jboss/jdbc/MyDS" pool-name="MyDS">
    <connection-url>jdbc:mariadb://localhost:3306/login</connection-url>
    <driver-class>org.mariadb.jdbc.Driver</driver-class>
    <driver>mariadb</driver>
    <security>
        <user-name>user</user-name>
        <password>secret</password>
    </security>
    <validation>
        <check-valid-connection-sql>select 1</check-valid-connection-sql>
        <validate-on-match>true</validate-on-match>
        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
    </validation>
</datasource>

And if you want to script the setting, here’s the code for the CLI:

/subsystem=datasources/data-source=MyDS:write-attribute(name=validate-on-match,value=true)
/subsystem=datasources/data-source=MyDS:write-attribute(name=check-valid-connection-sql,value="select 1")
/subsystem=datasources/data-source=MyDS:write-attribute(name=exception-sorter-class-name,value="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter")

The above settings make JBoss validate the connection on every new request (validate-on-match). But you can also have it do the checking in the background, e.g. every few seconds. And you need to consider the correct setting for your database. I use MariaDB/MySQL for my application and the exception-sorter and check-valid-connection-sql might be different depending on your database (e.g. org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker and select 1 from dual for Oracle). Take a look at the available parameters here: Database Connection Validation JBoss EAP 7 – Configuration Guide