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