Wildfly and auto reconnect to the database

Robert picture Robert · Feb 24, 2015 · Viewed 27.8k times · Source

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&amp;useUnicode=yes&amp;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>
<!-- ... -->

Answer

Domenico Briganti picture Domenico Briganti · Apr 1, 2015

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>