Database Fail Over in Jboss Data sources

Question

In JBoss data source how can I give multiple connection strings for database fail over in which I want .

There will be two Mysql db with same tables say DB1 and DB2. I want to insert data to DB1, if DB1 is down, then I need to insert it into DB2. During inserting into DB2 if the DB1 comes up I need to insert rest of the data into DB1. How can I configure this in my JBoss?

Answer 1

<?xml version="2.0" encoding="UTF-8"?>
<!-- $Id$ -->

 <!--  Datasource config for MySQL using 3.0.9 available from:
    http://www.mysql.com/downloads/api-jdbc-stable.html
      -->
  <datasources>
    <local-tx-datasource>

      <jndi-name>MySqlDSTest</jndi-name>
      <use-java-context>true</use-java-context>
      <connection-url>jdbc:mysql:loadbalance://ip1,ip2:3306/dbname?</connection-url>
      <url-delimiter>,</url-delimiter>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <connection-property name="readOnly">false</connection-property>
      <autoReconnect>true</autoReconnect>
      <failOverReadOnly>false</failOverReadOnly>
      <user-name>userName</user-name>
      <password>password</password>
      <check-valid-connection-sql>selcect count(*) from TEST_TAB</check-valid-connection-sql>
      <maxReconnects>0</maxReconnects>
      <initialTimeout>15</initialTimeout>
      <idle-timeout-minutes>0</idle-timeout-minutes>
      <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
      <!-- Advanced options for the MySQL Driver can be set with
              <connection-property name="property">value</connection-property>
        -->
      <min-pool-size>5</min-pool-size>
      <!-- Don't set this any higher than max_connections on your
           MySQL server, usually this should be a 10 or a few 10's
           of connections, not hundreds or thousands -->
      <max-pool-size>20</max-pool-size>
      <!-- Don't allow connections to hang out idle too long,
           never longer than what wait_timeout is set to on the
           server...A few minutes is usually okay here,
           it depends on your application
           and how much spikey load it will see -->

      <!-- If you're using Connector/J 3.1.8 or newer, you can use
           our implementation of these to increase the robustness
           "mysql-ds.xml" 64L, 3683C      of the connection pool. -->
      <exception-sorter-class-name>
         com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter
      </exception-sorter-class-name>
      <valid-connection-checker-class-name>
         com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker
      </valid-connection-checker-class-name>
      <!-- sql to call when connection is created -->
      <new-connection-sql>select 1</new-connection-sql>
      <!-- sql to call on an existing pooled connection when it is obtained from pool -    MySQLValidConnectionChecker is preferred for newer drivers -->
      <check-valid-connection-sql>
        select 1
      </check-valid-connection-sql>
      <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml -->
      <metadata>
        <type-mapping>mySQL</type-mapping>
      </metadata>
    </local-tx-datasource>
  </datasources>

This will work for jboss mapping

Answer 2

Follow this documentation. Jboss has configuration setting. https://community.jboss.org/wiki/JBossJCADatabaseFailover

Additionally, if you are NOT using JNDI and plain JDBC call I hav another solution - Say you are making a jdbc call then you will need to get a DB connection, if the DB is down then you will get Database connection exception, in the try catch block, if you encounter connection exception, create a connection in catch block itself for the second database :-)

Updated: 2/16/2022.

Never too late to update. You can also use Circuit breaker like Resilience4J. If the connection result comes back with exception, you can use .onError event to try connecting to second database. 9 Years later, I would use CB to solve this issue. Jboss provides inbuilt mechanism to do that but some other servers don't without restarting or additional manual steps.