2 Replies Latest reply on Nov 8, 2004 11:49 AM by Terry Ray

    Datasource failover with MySQL?

    Terry Ray Newbie

      Hi,

      I am trying to set my datasource up so that if my primary database server goes down, the datasource will use the backup server. I am using:

      jboss-3.2.5 (default config)
      j2sdk1.4.2_04
      mysql-max-3.23.55
      red hat ews 3.0
      mysql-connector-java-3.0.15

      Here is the snippet from my datasource config file (mysql-ds.xml):


      <local-tx-datasource>
      <jndi-name>MySqlDS</jndi-name>
      <connection-url>jdbc:mysql://10.1.1.3:3306,10.1.1.4:3306/mydatabase?autoReconnect=true</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <min-pool-size>5</min-pool-size>
      <max-pool-size>20</max-pool-size>
      <idle-timeout-minutes>10</idle-timeout-minutes>
      <track-statements>false</track-statements>
      <user-name>myusername</user-name>
      mypassword
      <!--security-domain>MySqlDbRealm</security-domain-->
      <prepared-statement-cache-size>100</prepared-statement-cache-size>
      <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
      </local-tx-datasource>


      I have tried using different connection-url parameter values (with and without the port numbers, etc) and some of the available options such as autoReconnect=true on the url. I have even tried the latest "development" version of the mysql connector which is supposed to have an autoReconnect for connection pools. But, still no luck.

      For my test, I start with the two database servers running. I start using my jboss app, then shutdown the localhost instance of mysql. When jboss goes for another connection, I get the following exception:

      Communication link failure: java.io.EOFException, underlying cause: null

      It looks to me that the connection pool is holding these closed connections. As a test, I pulled up the ManagedConnectionPool for my datasource using the jmx-console and invoked the flush() method. After doing this, jboss rebuilt the connection pool with connections from the other (back-up) server and everything was cool.

      My questions are:

      1. I am missing something on the failover settings at the datasource level?
      2. Assuming #1 just won't work the way I expected, is there a way to get a JNDI reference to the ManagedConnectionPool for my datasource so that I can trap the exception in my connection manager class and invoke the flush() method so the pool will be rebuilt?

      Any help is appreciated. Much thanks.

      Terry