32

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>
<!-- ... -->
Robert
  • 762
  • 2
  • 10
  • 23

3 Answers3

27

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>
Domenico Briganti
  • 551
  • 5
  • 7
  • 1
    We are using Oracle database and not working this solution, could you please suggest what needs to be done for oracle. – 1097733 Jul 02 '15 at 09:33
  • 5
    For oracle the statement should probably be "select 1 from dual" – Ryan Feb 05 '18 at 18:06
17

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>

Robert
  • 762
  • 2
  • 10
  • 23
  • 3
    this enable an old behavior. From the doc: (https://docs.jboss.org/jbossas/docs/Server_Configuration_Guide/beta500/html/ch13s13.html) __ - Prior to JBoss 4.0.5, connection validation occurred when the JCA layer attempted to match a managed connection. With the addition of this is no longer required. Specifying forces the old behavior. NOTE: this is typically NOT used in conjunction with __ – Domenico Briganti Apr 01 '15 at 09:10
  • 2
    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. – Rob Mascaro Mar 06 '16 at 09:29
  • 3
    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. – Nicola Ambrosetti Oct 04 '16 at 08:41
6

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

Alireza Alallah
  • 2,486
  • 29
  • 35