Bug #31790 MysqlValidConnectionChecker doesn't properly handle ReplicationConnection

• Submitted: 23 Oct 2007 17:32
• Modified: 3 Dec 2007 8:21
• Reporter: Harrison Fisk
• Status: Closed
• Category: Connector/J
• Severity: S3 (Non-critical)
• Version: 5.0.8
• OS: Any

[23 Oct 2007 17: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 18: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 20: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 12: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 12:04] Daniel Wallin

not "slave connection" but rather "slave node"  -  sry about that

[6 Nov 2007 12: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 1: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 3: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 8: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 17:06] Daniel Wallin

I've tried the 5.1.6 driver again and it still behaves the same way as describe above :(