MySQL Bugs: #6966: Fallback not working

Bug #6966	Fallback not working
Submitted:	2 Dec 2004 21:29	Modified:	17 Jan 2005 15:08
Reporter:	Mark Matthews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
I'm using Connector/J 3.0.15 with JBoss 3.2 to connect to MySQL 4.1.7.
I'm trying to use the failover feature. When the primary DB goes down, the first query fails, and JBoss creates a new connection, which is correctly connecting to the secondary DB.
The problem is that when the primary DB goes back up, the connection never falls back to it - and continues to be connected to the secondary DB forever.

I'm pretty sure this used to work when I was using an older version of Connector/J (maybe 3.0.10).

Looking at the Connection.java code, I see that pingAndReconnect() [the only function which calls shouldFallBack()] is only called if needsPing is true. However, for a new connection created to the secondary DB (when the primary one is down), needsPing is never set.

Is this by design? Is there any way I can get the functionality I expect (check periodically if the primary DB is back up and fall back to it)?

TIA,

Uri.

How to repeat:
See instructions.

Just adding a comment so I will get notifications, as per Mark's suggestion (thanks, Mark!).

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

This will be in Connector/J 3.0.17, (merging fix into 3.1.6 and 3.2.0 right now), which should be released before the end of this month.

I tried this on 3.1.6 (where, I understand, it is considered to be fixed).
It still doesn't seem to work for me, apparently because I have autocommit set to flase. shouldFallBack() is called only from execSQL(), and only when autoCommit is true.
While I agree that trying to fallback at the middle of a transaction is a bad idea, I think that in case autommit is false, fallback should be attempted in commit() and rollback().

Also, when I tried using autocommit=true, I ran into an infinite recursion which I submitted as bug 7952.

Use the configuration property 'reconnectAtTxEnd' to get the behavior you're asking for.