MySQL Connector/J: Failover and Blacklists

MySQL Connector/J provides loadbalancing and failover support.  While testing failover I wanted to know what was happening under the hood. Specifically I wanted to know when a host was blacklisted and which host.

If you set loadBalanceStrategy to random in your connection properties, which has been the recommended strategy over round-robin, you can modify the RandomBalanceStrategy.java file to log when a host is blacklisted.

Using guidance found on Todd Farmer's blog (http://mysqlblog.fivefarmers.com/) and a bit of twiddling it wasn't difficult.

In my setup, I have jboss-as-7.1.1. running on my local windows machine with my test app I developed using one of the JBoss quickstarts.

The connector (connector-java-5.1.21) is deployed and I have the source on C:\mysql-connector-java-5.1.21.

In RandomBalanceStrategy.java, right after the class declaration, I added the second line shown below.

public class RandomBalanceStrategy implements BalanceStrategy {
    private static Logger logger = Logger.getLogger("cyfd.connectorj.plugins");

Next, I added the second line shown below.

proxy.addToGlobalBlacklist( hostPortSpec );
logger.log(Level.SEVERE, "RandomBalanceStrategy-"+"Blacklisted host: " + 
   hostPortSpec + " att: " + attempts + " ret:" + numRetries  );

Save the file and compile it: (from windows cmd prompt at C:\mysql-connector-java-5.1.21\src\com\mysql\jdbc>; and, this is all one line)

javac -verbose -classpath "C:\jboss-as-7.1.1.Final\modules\com\mysql\main\
mysql-connector-java-5.1.24-bin.jar" -sourcepath . RandomBalanceStrategy.java

With jboss shutdown, update the jar file with the new class (again, this is all one line but executed from under the src folder which is C:\mysql-connector-java-5.1.21\src on my machine):

jar uf C:\jboss-as-7.1.1.Final\modules\com\mysql\main\mysql-connector-java-
5.1.24-bin.jar com/mysql/jdbc/RandomBalanceStrategy.class

Start jboss and when a host is blacklisted, you'll get a message similar to this.
08:25:11,005 SEVERE [cyfd.connectorj.plugins] (http--127.0.0.1-8080-80) RandomBalanceStrategy Blacklisted host: mysqlA:3306 att: 0 ret:100

So far the results of failover and load balance testing work perfectly.   As far as I can tell, a web client will get an error page--sometimes--but they can refresh the page and all transactions happen on the other server.

More testing to do....