How to use JDBC (Connector/J) with MySQL Cluster
By Henrik Ingo on Nov 28, 2008
Last week I helped a customer setup a JBoss application against MySQL Cluster. It turns out it is not immediately obvious how you should setup our JDBC connector to do loadbalancing and failover. For instance, setting the connector up for an Master-Slave setup (with MySQL Enterprise) is well documented, but not doing the same with MySQL Cluster.
It's not really properly documented in the manual part, but I found in the changelogs, and confirmed on IRC that to do load-balancing across the SQL nodes in MySQL Cluster, you would use a different JDBC connection string with the "loadbalance" keyword added...
That does indeed loadbalance, however it didn't properly address failover. When an SQL node is killed, it still tries to round-robin queries to all specified hosts, resulting in exceptions 50% of the time (with 2 nodes, that is).
After further digging (in fact, my collague got this from Mark Matthews himself) I finally found out that the correct string to use is:
The "loadBalanceBlacklistTimeout" adds the needed feature that failed connections in a connection pool are put aside for the specified time, and only working connections are utilized.
That's all that is needed. It is simple and beautiful once you get it to work!
Update: I should add that transactions that are running while a node crashes will still rollback and return an exception. This is by design and it is then up to the application to decide whether to give up or retry. If you retry the transaction, the JDBC driver will pick a working SQL node for the second try.