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

How to seed the database with sample data for an Arquillian test

Arquillian makes it easy to test your Java EE application on a real server. However, if you deploy an application, that uses a database to perform its tasks, how do you make sure a database with test entries is available on the target server?

If you set up a “real” database on the server, the tests become hard to understand, because the data that’s used in assertions is not visible directly in the test. You have to know the content of the database to make sense of the tests. In addition, you’ll have to maintain the database and keep the data in a consistent state.

Instead, I would like my tests to create the test data themselves, so that I can see everything I need to know to understand the tests in one place and I don’t have to maintain a database system.

Using EntityManager

My first attempt was to inject an Entity Manager into my Arquillian test and setup the test data like this:

@RunWith(Arquillian.class)
public class IntegrationTest
{
    @PersistenceContext
    private EntityManager em;

    @Resource
    private UserTransaction userTransaction;

    private User user;

    @Deployment
    public static WebArchive createDeployment()
    {
        return ShrinkWrap
                .create(WebArchive.class)
                .addClass(User.class)
                ...
                .addAsWebInfResource(EmptyAsset.INSTANCE, "beans.xml")
                .addAsResource("arquillian/persistence.xml", "META-INF/persistence.xml");
    }

    @Before
    public void setup() throws Exception
    {
        user = new User("myuser", "mypassword");
        userTransaction.begin();
        em.persist(user);
        userTransaction.commit();
    }

    @Test
    public void existingUserShouldBeFound()
    {
        assertThat(
                em.find(User.class, "myuser"),
                is(user));
    }
}

The file arquillian/persistence.xml looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
    xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="myPU">
        <jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>
        <properties>
            <property name="hibernate.hbm2ddl.auto" value="create-drop" />
        </properties>
    </persistence-unit>
</persistence>

It simply uses the default datasource (of JBoss EAP in my case) of the application server. So I don’t even have to setup a new datasource for my tests. Hibernate drops and recreates the tables during each deployment, so the tests start with a fresh database.

This test works great, as long as you run it inside the container. The EntityManager only gets injected into the test, if you deploy it to the application server. However, if you want to test your application from the outside, em will be null.

@Test
@RunAsClient
public void existingUserShouldBeFound()
{
    // some assertions against the API
}

If you add @RunAsClient to the test method or set the deployment to @Deployment(testable = false), the test is run outside the container (e.g. if you want to test a REST API). Dependency injection doesn’t work in this case and the test fails:

java.lang.NullPointerException
    at it.macke.arquillian.api.IntegrationTest.setup(IntegrationTest.java:78)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    ...

Using an initialization observer

If you use Java EE 7 (which I do), there’s an easy way to run code at the start of your application: observers. If you add an observer to your project, that gets called as soon as the application has initialized, you can execute all the needed setup code before the first test is run.

Here’s an example of my observer, that creates the test data in the database after the deployment to the remote server:

@ApplicationScoped
public class TestDataCreator
{
    @PersistenceContext
    private EntityManager em;

    @Transactional
    void createTestUser(
            @Observes @Initialized(ApplicationScoped.class) final Object event)
    {
        User user = new User("myuser", "mypassword");
        em.persist(user);
    }
}

Of course, you need to include the observer in your deployment:

@Deployment
public static WebArchive createDeployment()
{
    return ShrinkWrap
        .create(WebArchive.class)
        .addClass(TestDataCreator.class)
        ...
}

If you add some logging to TestDataCreator, you can see that it creates the test data after the deployment to the remote server:

19:28:27,589 INFO  [org.hibernate.tool.hbm2ddl.SchemaExport] (ServerService Thread Pool -- 140) HHH000227: Running hbm2ddl schema export
19:28:27,592 INFO  [org.hibernate.tool.hbm2ddl.SchemaExport] (ServerService Thread Pool -- 140) HHH000230: Schema export complete
...
19:28:27,916 INFO  [it.macke.arquillian.setup.TestDataCreator] (ServerService Thread Pool -- 143) Adding test user: User{username=myuser, password=mypassword}
...
19:28:28,051 INFO  [org.wildfly.extension.undertow] (ServerService Thread Pool -- 143) WFLYUT0021: Registered web context: /2e36a3c3-be16-4e00-86c3-598ce822d286
19:28:28,101 INFO  [org.jboss.as.server] (management-handler-thread - 29) WFLYSRV0010: Deployed "2e36a3c3-be16-4e00-86c3-598ce822d286.war" (runtime-name : "2e36a3c3-be16-4e00-86c3-598ce822d286.war")

Now, my client test runs successfully, because the needed test data gets created as soon as the application is deployed.