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.
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
<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
check-valid-connection-sql might be different depending on your database (e.g.
select 1 from dual for Oracle). Take a look at the available parameters here: Database Connection Validation JBoss EAP 7 – Configuration Guide
4 thoughts on “Automatically reconnect to a database after a failure in JBoss EAP 7”
i have got the similar issue but with wildfly connecting to sqlserver 2016, what validation parameters could i menction to connect with sqlserver
this is with wildfly 10.1 and jdk 1.8.
Sorry, I have no experience with SQL Server :-/
You can find valid connection checkers here or just use correct sql expression in check-valid-connection-sql