Almost two weeks ago, I encouraged GlassFish users who need load-balanced JDBC connections to MySQL Cluster (or master-master replicated MySQL Server) to set the loadBalanceValidateConnectionOnSwapServer property to true in order to help ensure the connection chosen at re-balance is still usable.  That advice triggered finding a bug (14563127) which will cause the following Exception message:

No operations allowed after connection closed. Connection closed after inability to pick valid new connection during fail-over.

If you implemented the loadBalanceValidateConnectionOnSwapServer property and are seeing the above error message, updating your driver to the newly-released 5.1.22 build will likely solve this …

Having supported a number of successful load-balanced JDBC applications using MySQL Cluster and MySQL Connector/J over the years, I’ve found a few problems that are unique to specific Java app servers.  A recent customer inquiry reminded me of a GlassFish-specific issue, and the Connector/J connection property we introduced to help solve it.  I thought it might be useful to document this here for any GlassFish users looking to deploy a load-balanced JDBC application with MySQL (Cluster or multi-master replication).

If you’re entirely new to the load-balancing functionality in MySQL Connector/J, you may want to review some earlier posts.  In particular, it’s important to understand how a …

Many people are aware that MySQL 5.5 added support for external authentication plugins, and that Oracle provides several commercial-licensed plugins that can help users leverage this functionality out-of-the-box (you can try these and other features of MySQL commercial offerings for free).  Until the recent release of Connector/J 5.1.19, though, JDBC users could not leverage the plugin capabilities of MySQL 5.5.  Now, Java users can write their own client-side plugins in support of the standard MySQL 5.5 external authentication plugins, or even server-side external authentication plugins they write themselves.

…

A fourth and final Connector/J extension point I covered in my JavaOne and Silicon Valley Code Camp presentations is load-balancing strategies.  This exists in order to allow you to define behavior for balancing load across multiple back-end MySQL server instances.  MySQL Connector/J’s load-balancing implementation is a simple internal connection pool.  What appears to your application as a single Connection object can actually have multiple physical connections to MySQL servers underneath (one per configured host/port pair).  At specific points, Connector/J will re-balance and choose another host to interface with.  This extension point allows you to define how Connector/J determines …

A third built-in extension point for MySQL Connector/J is the ExceptionInterceptor interface.  This is the third extension point covered in my recent JavaOne and Silicon Valley Code Camp presentations, and is very useful for diagnosing specific Exceptions encountered without modifying application-side code. This corresponds to slide #60 in my slide deck, and there are two Java files we’ll reference from my demo code:

• demo.connectorj.ExceptionInterceptorExample
• demo.connectork.plugins.ExampleExceptionInterceptor

To …

Have you seen error messages similar to the following:

Communications link failure – Last packet sent to the server was X ms ago.

Judging from the forums, many people have had problems with this.  Here’s a brief overview of the causes, and potential solutions.

Generally speaking, this error suggests that the network connection has been closed. There can be several root causes:

• Firewalls or routers may clamp down on idle connections (the MySQL client/server protocol doesn’t ping).
• The MySQL Server may be closing idle …

MySQL Connector/J has a useful feature which executes a lightweight ping against a server (or, in the case of load-balanced connections, all active pooled internal connections that are retained) to validate the connection. As you might guess, this feature is useful for Java apps which use connection pools, so that the pool can validate the connection. Depending on your connection pool and configuration, this can be done at different times:

• before the pool returns a connection to the application
• when the application returns a connection to the pool
• during periodic checks of idle connections

So, if you want to use this magic light-weight ping process, here’s how you do it:

Specifying a “validation query” in your connection pool that starts with “/* ping */” _exactly_ will cause the driver to instead send a ping to the server and return a fake result set (much lighter weight), …

Connector/J has long provided an effective means to distribute read/write load across multiple MySQL server instances for Cluster or master-master replication deployments, but until version 5.1.13, managing such deployments frequently required a service outage to redeploy a new configuration.  Given that ease of scaling out by adding additional MySQL Cluster (server) instances is a key element in that product offering, which is also naturally targeted at deployments with very strict availability requirements, we had to add support for online changes of this nature.  It’s also critical for online upgrades – the other option is to take a MySQL Cluster server instance down hard, which loses any in-process transactions and generates application exceptions, if any application is trying to use that particular server instance.

Mark Matthews and I first …

In a previous blog post, I wrote about Connector/J’s failover policies, I described three triggers which cause Connector/J to re-balance (potentially selecting a new physical connection to another host):

1. At transaction boundaries (transactions are explicitly committed or rolled back)
2. A communication exception (SQL State starting with “08″) is encountered
3. When a SQLException matches conditions defined by user, using the extension points defined by the loadBalanceSQLStateFailover, loadBalanceSQLExceptionSubclassFailover or loadBalanceExceptionChecker properties.

Those conditions fit most needs very well, but there are situations where people are running with auto-commit enabled (no explicit transaction commit or rollback), and the end result is that Connector/J never …

Shortly after I wrote my last post regarding some advanced Connector/J load-balancing properties, Anirudh published a post describing configuration of RHEL LVS for load-balancing and failover of MySQL Cluster SQL nodes.  It’s an interesting post, and I admit I know very little about RHEL LVS, but it reminded me of problems I experienced when trying to set up load-balanced ColdFusion(!) servers at my last job, years back.  We ended up with a nice hardware load-balancer sitting in front of multiple ColdFusion web servers.  The problems we found were that our application depended upon session state, which was stored (of course) on a single web server.  The load-balancer allowed us to define sticky sessions, which is what we did, but it cost us.

We couldn’t really balance load – we could balance session counts, sort …