Re: jdbc failover

[Magnus Svensson]

Hi,

here are a quick instruction how to setup JDBC failover.

1. Get snapshot of Connector/J 3.0

2. Specify multiple hosts in the URL with the 'new' URL format and add
two additional properties, 
'autoReconnect=true&failOverReadOnly=false':
 
jdbc:mysql://[host_1]:[port_1],[host_2]:[port_2].../databasename

 For example:

jdbc:mysql://192.168.29.20:3306,192.168.29.20:6306/test?user=root&password=foo&autoReconnect=true&failOverReadOnly=false

3. Make sure that you  understand that failover only happens when the
connection is in 'autoCommit=true' state to avoid
corrupting data... Thankfully most connection pools take care of this
for you, as the JDBC spec requires the 'default' state to be
'autoCommit=true'..., so when you return a connection to a pool, it
will at some time before handing it out call setAutoCommit(true) on it,
which will trigger the re-connection code...Otherwise, you will have
to call setAutoCommit(true) on your own...

4. Get a connection as you did before.

If the node that the JDBC driver is connected two crashes, the _current_
in-progress transaction will fail and an exception will be thrown,
however, once autoCommit has been set to 'true', the failover code
kicks
in, and a connection is established to the next node in the host list,
so that processing may continue.


Best regards
Magnus


-- 
Magnus Svensson, Software Engineer
MySQL AB, www.mysql.com
Office: [phone]


On Tue, 2004-07-27 at 17:01, Crouch, Luke H. wrote:
> I notice in the Connector/J documentation that failover hosts are connected to on
> 'read-only' basis. meaning if I have set up 4 mysqld API's, and I use JDBC to connect to
> one of these, specify another one as the failover, it will only allow read statement to go
> thru. in the cluster environment, it DOES make sense to let the failover host perform
> writing queries, so how can I configure Connector/J to allow write queries on failover
> hosts?
>  
> thanks,
> -L
> 
> Luke Crouch 
> IS Developer 
> Red Man Pipe & Supply 
> 918-461-5326 
> luke.crouch@stripped 
> **NOTE: MY EMAIL HAS CHANGED AS OF 6/17/2004** 
> 
>