Wildfly and auto reconnect to the database

Question

I've got a client, a server and a database. The client communicates with the server via a EJB remote interfaces. As the server - I use a Wildfly 8.2.0. As the database - I use a MySQL. The server communicates with the MySQL via a JPA/Hibernate. When I turn off the MySQL server - the Wildfly throws an exception, of course. But when I turn on the MySQL again - the Wildfly still throws the same ERROR. I've to turn off the Wildfly and turn it back that the Wildfly reconnect to the database.

How to set auto reconnect in the Wildfly?

I tried to set auto reconnect in a connection URL: jdbc:mysql://localhost/db?autoReconnect=true&useUnicode=yes&characterEncoding=UTF8 and i tried to add to the standalone-full.xml file which i use, this line: <check-valid-connection-sql>select 1</check-valid-connection-sql>, but both solutions don't work.

standalone-full.xml:

<!-- ... -->
<datasource jta="true" jndi-name="java:jboss/datasources/MySQLDS" pool-name="MySQLDS" enabled="true" use-ccm="true">
    <connection-url>jdbc:mysql://localhost/db?autoReconnect=true&amp;amp;useUnicode=yes&amp;amp;characterEncoding=UTF8</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <driver>mysqlDriver</driver>
    <security>
        <user-name>user</user-name>
        <password>***</password>
    </security>
    <validation>
        <check-valid-connection-sql>select 1</check-valid-connection-sql>
        <validate-on-match>false</validate-on-match>
        <background-validation>false</background-validation>
    </validation>
    <timeout>
        <set-tx-query-timeout>false</set-tx-query-timeout>
        <blocking-timeout-millis>0</blocking-timeout-millis>
        <idle-timeout-minutes>0</idle-timeout-minutes>
        <query-timeout>0</query-timeout>
        <use-try-lock>0</use-try-lock>
        <allocation-retry>0</allocation-retry>
        <allocation-retry-wait-millis>0</allocation-retry-wait-millis>
    </timeout>
    <statement>
        <share-prepared-statements>false</share-prepared-statements>
    </statement>
</datasource>
<drivers>
    <driver name="mysqlDriver" module="com.mysql">
        <xa-datasource-class>com.mysql.jdbc.Driver</xa-datasource-class>
    </driver>
</drivers>
<!-- ... -->

Solution 1

I solved this problem by changing the validate-on-match value to true in my standalone-full.xml file:

<validate-on-match>true</validate-on-match>

Solution 2

This working on Wildfly 8.1:

         <datasource jta="true" jndi-name="java:jboss/datasources/xxxdb" pool-name="xxxxDB" enabled="true" use-ccm="false">
          <connection-url>jdbc:mysql://localhost:3306/xxxdb?autoReconnect=true&amp;amp;useUnicode=true&amp;amp;characterEncoding=UTF-8</connection-url>
          <driver-class>com.mysql.jdbc.Driver</driver-class>
          <driver>mysql-connector-java-5.1.26-bin.jar</driver>
          <security>
              <user-name>xxxuser</user-name>
              <password>xxxpassword</password>
          </security>
          <validation>
              <check-valid-connection-sql>select 1</check-valid-connection-sql>
              <validate-on-match>false</validate-on-match>
              <background-validation>true</background-validation>
              <background-validation-millis>10000</background-validation-millis>
          </validation>
          <statement>
              <share-prepared-statements>false</share-prepared-statements>
          </statement>
        </datasource>

Solution 3

You can set <background-validation> to true , because this option has better performance , and set <validate-on-match> to false

Comments

[Domenico Briganti]

this enable an old behavior. From the doc: (docs.jboss.org/jbossas/docs/Server_Configuration_Guide/beta‌​500/…) <validate-on-match> - Prior to JBoss 4.0.5, connection validation occurred when the JCA layer attempted to match a managed connection. With the addition of <background-validation> this is no longer required. Specifying <validate-on-match> forces the old behavior. NOTE: this is typically NOT used in conjunction with <background-validation>

[1097733]

We are using Oracle database and not working this solution, could you please suggest what needs to be done for oracle.

[Rob Mascaro]

You maybe right @dometec , but I can confirm that on Wildfly 10 you need validate-on-match true otherwise Wildfly will never reconnect successfully to the connection pool if you bounce MySQL. I've tried background validation and is doesn't work. I think this is a bug.

[Nicola Ambrosetti]

Setting background-validation worked for me only after I set also background-validation-millis as in @dometec's answer. Wildfly 10.1 and SqlServer here.

[Ryan]

For oracle the statement should probably be "select 1 from dual"