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 load-balanced Connector/J (logical) Connection object maintains multiple physical connections (small “c”) – up to one for each load-balanced server. When the driver determines it’s appropriate to re-balance the connection, a new host is chosen, and if a physical connection to that server already exists within the Connection object, it will be reused, rather than creating a new physical connection. Because we’re maintaining multiple physical connections, it’s important that the connection validation process used by the application server trigger validation of all underlying physical connections – not just the currently-active physical connection. In Connector/J, load-balanced Connection objects will validate all underlying physical connections when a query starting with “/* ping */” (exactly) is encountered.
And this is where GlassFish users sometimes have problems – it can be difficult (or impossible, in some versions) to define a custom validation query that starts with “/* ping */” to trigger the necessary validation of all physical connections. Here’s the scenario that proves most problematic:
- The application gets a Connection object from the connection pool, and the Server A is the currently-active server to which traffic is routed by the Connection object.
- The application issues commit(), which triggers re-balance, and Server B is made active. The physical connection to Server A is retained.
- The application returns the Connection to the connection pool.
- The connection pool periodically validates the Connection using “SELECT 1” – a query that is executed against the currently-active physical connection to Server B. The physical connection to Server A remains idle, and eventually dies (unnoticed).
- The application is given this same Connection from the connection pool later, perhaps even validates it on check-out (again with “SELECT 1”), and uses it successfully.
- The application issues commit(), triggering re-balance again, and Server A is selected and made active.
- The application tries to use the Connection again, and it fails due to CommunicationException, as the physical connection to Server A has been left idle too long.
Explicitly in support of users of GlassFish or other JDBC connection pools where the connection validation query cannot be set to start with “/* ping */”, we added the loadBalanceValidateConnectionOnSwapServer connection property starting in MySQL Connector/J version 5.1.11. In the example above, this affects step #6. When the Connection re-balances and chooses a new physical connection, that physical connection is explicitly validated before returning control to the application. Thus, when the application tries to use the Connection object again in step #7, it should find a valid Connection.
Having confidence that the Connection won’t choose an idle physical connection at re-balance sounds good, right? So why not make this the default behavior?
Well, this adds some overhead to re-balance operations – in particular, commit(). If you have a JDBC connection pool that can maintain load-balanced connections adequately using the /*ping */ connection validation query, checking the physical connection at each commit() can be unwelcome overhead. It also doesn’t guarantee the Connection won’t throw a CommunicationException – with applications connecting over a network, a disconnect is possible at any time.
Hopefully this tip will help users of GlassFish (and other JDBC connection pools where validation queries cannot be specified) be more successful with load-balanced MySQL deployments.