Bug #31790 MysqlValidConnectionChecker doesn't properly handle ReplicationConnection
Submitted: 23 Oct 2007 15:32 Modified: 3 Dec 2007 7:21
Reporter: Harrison Fisk Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.0.8 OS:Any
Assigned to: CPU Architecture:Any

[23 Oct 2007 15:32] Harrison Fisk 
Description:
MysqlValidConnectionChecker is used by Jboss for verifying that the connection is valid.  Normally, this will use Connection.ping() to verify the validity for both com.mysql.jdbc.Connection and com.mysql.jdbc.jdbc2.optional.ConnectionWrapper.  However, for ReplicationConnection it does not and instead falls back and uses "SELECT 1".  This will cause it to not validate the slave connections, which allows them to become invalid.

How to repeat:
In JBoss, setup your pool to use both com.mysql.jdbc.ReplicationDriver and MysqlValidConnectionChecker.  Watch your slaves timeout as they do not get pinged.

Suggested fix:
One of the two things should work:

*  Add the necessary check for com.mysql.jdbc.ReplicationDriver in MysqlValidConnectionChecker.  Then use ping() based on that.

*  Change the fallback "SELECT 1" into "/* ping */ SELECT 1" which causes ReplicationConnection to verify all of the connections.
[23 Oct 2007 16:17] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36161
[23 Oct 2007 18:01] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36184
[6 Nov 2007 11:03] Daniel Wallin 
Driver: mysql-connector-java-5.1-nightly-20071106-bin.jar

If restarting the slave connection, it still wont be able to reconnect.
Restarting the master however invalidates all connections and hence makes the slave connections accurate again.
[6 Nov 2007 11:04] Daniel Wallin 
not "slave connection" but rather "slave node"  -  sry about that
[6 Nov 2007 11:18] Daniel Wallin 
Don't know if it helps, but this is the exact flow I used when reproducing the bug

*set connection to read-only=true (read replica)
*let app select from replica -> successful
*restart replica
*let app select from replica -> failure
*restart master
*let app select from replica -> failure
*set connection to read-only=false (read master)
*let app select from master -> successful
*set connection to read-only=true (read replica)
*let app select from master -> successful

So, obviously:
1. restarting replica makes replica-connection faulty.
2. restarting the master seem to invalidate the master-connection.
3. actually using the master connection, seem to refresh both master and replica connection.
4. everything works fine again.
[19 Nov 2007 0:57] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38019
[19 Nov 2007 2:52] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38024
[3 Dec 2007 7:21] MC Brown 
A note has been added to the 5.1.6 changelog: 

MysqlValidConnectionChecker doesn't properly handle connections created using ReplicationConnection.
[27 May 2008 15:06] Daniel Wallin 
I've tried the 5.1.6 driver again and it still behaves the same way as describe above :(