Connector/J ping (there will be a test on this)

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), and when using a ReplicationConnection or a LoadBalancedConnection, will send the ping across all active connections.

So says the relevant note in the changelogs.

Now for the test.  Which of the following Strings match the condition above?

  • sql = “/* PING */ SELECT 1”;
  • sql = “SELECT 1 /* ping*/”;
  • sql = “/*ping*/ SELECT 1”;
  • sql = ” /* ping */ SELECT 1″;
  • sql = “/*to ping or not to ping*/ SELECT 1”;

If you said “none of the above”, you pass the test.  The test is sensitive to whitespace, capitalization, and placement.  It’s this way for efficiency, as this test is done for every Statement that is executed:


protected static final String PING_MARKER = "/* ping */";
...
if (sql.charAt(0) == '/') {
if (sql.startsWith(PING_MARKER)) {
doPingInstead();
...

All of these statements will issue normal SELECT statements and not get transformed into the lightweight ping.  More problematic, this means that loadbalanced connections only execute one statement against one physical connection in the internal pool, rather than validating the connection for each underlying physical connection.  So the non-active physical connections sit stale, and stale connections die, and then Connector/J re-balances and selects that stale dead connection, and suddenly you have an Exception pushed up to the application (loadBalanceValidateConnectionOnSwapServer can save you here).

If your Connector/J deployment uses a connection pool which allows you to specify a validation query, check it, and make sure that it starts with “/* ping */” – exactly.  This is especially true if you are using load-balancing or replication-aware features of Connector/J – it should help keep alive connections which otherwise will go stale and die, causing problems later.

2 thoughts on “Connector/J ping (there will be a test on this)

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.