Category Archives: Java

Java stuff.

Connection attributes in MySQL 5.6

Introduced in 5.6.6, connection attributes haven’t been mentioned in any of the many overviews of new 5.6 features that I’ve seen.  You might not have even known they exist – there’s not really a “feature page” in the documentation that tells you what this is all about.  The best documentation is found in the description of the PERFORMANCE_SCHEMA tables which expose these connection attributes:

As of MySQL 5.6.6, application programs can provide key/value connection attributes to be passed to the server at connect time, using the mysql_options() and mysql_options4() C API functions. The Performance Schema provides tables that expose this information through SQL statements[.]

Here’s what that means in practice:  With connectors which support this feature, certain client information can be exposed via PERFORMANCE_SCHEMA tables, making it easier to get useful information about clients from the MySQL Server directly.
Continue reading Connection attributes in MySQL 5.6

More GlassFish loadbalancing tips for Connector/J

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 problem.  Here’s a quick look at internals of Connector/J re-balance operations, some additional configuration suggestions, and details on the bug fixed in 5.1.22: Continue reading More GlassFish loadbalancing tips for Connector/J

Learn From MySQL Support Staff at MySQL Connect!

Members of the MySQL Support Team wear a number of different hats here at Oracle.  Obviously, our top priority is to provide amazing technical support that makes customers rave.  We also have a team dedicated to processing bug reports from the MySQL Community.  Some of us are active bloggers or assist on mailing lists or forums, while others find other ways to contribute to the MySQL Community.  We help out with QA and product planning, write books, and the consultative support aspects of MySQL subscriptions allow us the opportunity to help train and advise customers on best practices and successful MySQL-backed application architecture.  In short, we’re deeply invested in making sure that MySQL deployments are successful.

This year, we’re privileged to send some of these great MySQL experts to the inaugural MySQL Connect Conference this September 29-30, and it will be a great opportunity for you to experience first-hand the value that MySQL Support can provide:

Be sure to stop by the MySQL Support booth in the demo grounds at MySQL Connect and meet these MySQL experts – they’ll be happy to help you solve any of your tricky MySQL problems.

If you’re an Oracle DBA just getting started on MySQL, it’s worth the $100 fee to add MySQL Connect to your OOW registration, but Ben Krug will also be leading a session at OOW with the goal of Demystifying MySQL for Oracle DBAs and Developers.

And if California is just too far to travel, perhaps you can catch Uma Bhat‘s presentation at PyCon India, Introduction to MySQL Connector/Python.

We hope to see you at one of these upcoming conferences!

Load-balanced JDBC Tip for GlassFish Deployments

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:

  1. 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.
  2. The application issues commit(), which triggers re-balance, and Server B is made active.  The physical connection to Server A is retained.
  3. The application returns the Connection to the connection pool.
  4. 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).
  5. 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.
  6. The application issues commit(), triggering re-balance again, and Server A is selected and made active.
  7. 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.

Connector/J now supports authentication plugins

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.

This release (and this feature, specifically) is also significant in at least one other way:  It’s the first release (and feature) of Connector/J authored by Alexander Soklakov – the newest member of the Connectors team and crack Java developer.  Like most things at MySQL, there was a good team backing Alex up (with Mark Matthews helping define the architecture, Rafal Somla coordinating the protocol-level changes, and Tonci Grgin overseeing it all).  Way to go, Alex and team!

Because the key MySQL 5.5 external authentication plugins are platform dependent (PAM, Windows), they may not be the best fit for Java users, where platform independence (and lack of reliance on native code) is a big priority.  We’re always interested to receive feature requests, though, so if you have an idea for how you would want to use MySQL external authentication in the context of your Java application, please let us know (comment here, or register the request at

The original purpose for writing this blog entry, however, was to supplement my previous entries on other Connector/J extension points.  Much to my chagrin, but your benefit, Alexander was way ahead of me.  The public docs haven’t quite caught up, but there’s great content in the CHANGES file.  He even includes example and test implementations.  I’ll just quote the CHANGES file directly:



  - Added support for pluggable authentication via the com.mysql.jdbc.AuthenticationPlugin
    interface (which extends standard "extension" interface). Examples are in
    com/mysql/jdbc/authentication and in testsuite.regression.ConnectionRegressionTest.
    This introduces three new properties:

       authenticationPlugins defines comma-delimited list of classes that implement
       com.mysql.jdbc.AuthenticationPlugin and which will be used for authentication
       unless disabled by "disabledAuthenticationPlugins" property.

       disabledAuthenticationPlugins defines comma-delimited list of classes implementing
       com.mysql.jdbc.AuthenticationPlugin or mechanisms, i.e. "mysql_native_password".
       The authentication plugins or mechanisms listed will not be used for authentication
       which will fail if it requires one of them. It is an error to disable the default
       authentication plugin (either the one named by "defaultAuthenticationPlugin" property
       or the hard-coded one if "defaultAuthenticationPlugin" propery is not set).

       defaultAuthenticationPlugin defines name of a class implementing
       com.mysql.jdbc.AuthenticationPlugin which will be used as the default authentication
       plugin. It is an error to use a class which is not listed in "authenticationPlugins"
       nor it is one of the built-in plugins. It is an error to set as default a plugin
       which was disabled with "disabledAuthenticationPlugins" property. It is an error
       to set this value to null or the empty string (i.e. there must be at least a valid
       default authentication plugin specified for the connection, meeting all constraints
       listed above).

It’s great work from Alexander, and I’m looking forward to his future work.  It also demonstrates the value of having a support organization with direct involvement in the development of all MySQL products, as this functionality was implemented in direct response to customer need.

Let us know if you have any problems implementing external authentication plugins, or have ideas on plugins you would find valuable for managing your Java deployments.


Connector/J extension points – Load Balancing Strategies

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 which host it should pick next.

Unlike the previous extension points, my demo code does not contain examples of this.  In this case, though, there are some standard implementations provided with Connector/J that we can look at, instead.  The two implementations shipped with Connector/J today implement a “best response time” strategy and a “random” strategy.  The default behavior when using load-balanced deployments is “random”, and the Connector/J configuration properties documentation describes their use cases.  Below are the actual classes which define the behavior:

  • com.mysql.jdbc.RandomBalanceStrategy
  • com.mysql.jdbc.BestResponseTimeBalanceStrategy

These classes – and any user-implemented load-balancing strategy – implements the com.mysql.jdbc.BalanceStrategy interface.  As the purpose of this extension point is to define how load is balanced across MySQL instances, there’s really only one key method you need to focus on:  pickConnection().  The purpose of this method is to return a Connection (more specifically, a com.mysql.jdbc.ConnectionImpl object).  Looking at the RandomLoadBalacneStrategy code, you will see the logic that’s involved.

The first parameter to pickConnection() is a LoadBalancingConnectionProxy object.  This is the object that does much of the load-balancing work.  It also contains a few callback methods you will want to consider:

  1. getGlobalBlacklist() – this method returns a Map<String, Long> of hosts that have been identified as unavailable.  The String key is the host/port, while the Long is the time that the blacklist entry should expire.  Inside the proxy, this global blacklist is defined as a static Map, meaning that Host X will be found in the blacklist by one Connection if another Connection object put it there after experiencing problems.  Access to the static variable is synchronized, and the Map returned from this method is a local copy.
  2. shouldExceptionTriggerFailover() – this method takes a SQLException and determines whether such an Exception should trigger a failover.  This, too, is user-configurable, although the defaults are usually sufficient for most deployments.  A previous post contains detailed information on how to customize this behavior.
  3. addToGlobalBlacklist() – this is the method you want to call if you want to add a host to the global blacklist.
  4. createConnectionForHost() – this is a utility method that handles creation of a new ConnectionImpl object based on the host/port String, so that you don’t have to wire up ConnectionImpl objects directly.  If you look at the method implementation, you will see the work that goes into setting up a properly-configured ConnectionImpl.

The remaining parameters are, in order:

  • List<String> – a list of configured hosts involved in load-balancing
  • Map<String, ConnectionImpl> – a Map of “live” connections already established, accessed through the host/port key.  Thinking of this as a connection pool, these are the cached connections which can be reused if the host/port pair is chosen, instead of doing the additional work of setting up a new physical connection.
  • long[] – an array of response times in the same order as the List<String> of configured hosts.  This is used in BestResponseTimeBalanceStrategy.
  • int – number of retries that should be attempted before giving up on finding a new connection.

So, what can you do with this?  People frequently ask for a true round-robin load-balancer.  Our experience has been that RandomBalanceStrategy is far better, but if you really need a true round-robin load-balancing algorithm, you could implement it here.


Connector/J extension points – exception interceptors

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 implement an exception interceptor, you need to do the following:

  1. Create a Java class which implements com.mysql.jdbc.ExceptionInterceptor
  2. Configure Connector/J to use your exception interceptor by passing the fully-qualified class name as the value for the “exceptionInterceptors” property.

Like statement interceptors, this extension point is stackable – you can create multiple exception interceptors, passing them in as a comma-delimited list of fully-qualified class names.  The exception interceptors are executed in order in which they are defined in the connection property.

The demo here simply demonstrates how this work, but doesn’t give much of an idea of the power behind this interface.  The demo code executes a command that’s not valid SQL syntax – which will trigger a server-side error – catches the normal exception, wraps it with some additional text in the message before returning it to the application (where it is hopefully logged).  So what can you do with this?

There are certain errors where diagnosis requires additional information about either connection or server state – at the time the exception is raised.  This gives you a hook to enable collection of this data.  For example, certain NDB (Cluster) errors map to the same MySQL Server error code and message, and you can get more information from the NDB problems by issuing SHOW WARNINGS immediately after the error.  Unless you want to recode your application, there’s a lot of value in the ability to add an ExceptionInterceptor which looks for such errors, executes the SHOW WARNINGS, takes the details returned and shoves them into the Exception message text.

Another example might be when you get a server error that indicates the connection character set is set to something other what it should be set to, based on the configuration settings, and you want to know what session character set or collation is in use.  Unless you change your application code, you can only assume what it should be – unless you implement an ExceptionInterceptor that collects that data when the problem is encountered and logs that information for you.

Generally speaking, this is an extension point that you probably won’t leverage for normal operations, but can be very useful for diagnosing Exceptions that are difficult to reproduce outside the context of your Java application deployment.  Because ExceptionInterceptors are only called when handling a SQLException thrown from Connector/J code, you don’t have to worry too much about performance penalties in production deployments.


Connector/J extension points – statement interceptors

Continuing the review of MySQL Connector/J’s built-in extension points from my recent JavaOne and Silicon Valley Code Camp presentations, this blog posting will focus on the StatementInterceptor extension point.  As the name suggests, this allows you to hook into statement execution and alter behavior – without changing application-side code.  This corresponds to slide #59 in my slide deck, and there are two Java files we’ll reference:

  • demo.connectorj.StatementInterceptorExample
  • demo.connectork.plugins.ExampleStatementInterceptor

To implement a statement interceptor, you need to do the following:

  1. Create a Java class which implements com.mysql.jdbc.StatementInterceptorV2
  2. Configure Connector/J to use your statement interceptor by passing the fully-qualified class name as the value for the “statementInterceptors” property.

This extension point is stackable – you can create multiple statement interceptors, passing them in as a comma-delimited list of fully-qualified class names.

The example provided in the demo code is pretty bland, but illustrates what can be done.  In the demo code, we’ve implemented the preProcess() method to check for a certain trigger (“/* test */” in this case), which triggers entirely different behavior than what would normally transpire.  This code simply returns the result of  “SELECT NOW()” when triggered, instead of whatever would normally be executed on the server.  You’ll probably never need this particularly functionality, but there’s other interesting stuff you could do:

  • Add memcached without changing a line of application code, by checking memcached before executing a query, and caching the results after retrieving non-cached data from MySQL.
  • Work around MySQL server problem areas without changing application code.  Maybe you have slow-performing subqueries in an application you cannot change?  Use statement interceptors to rewrite them to more better-performing JOIN syntax equivalents.
  • Perform some standard result set transformation by wrapping ResultSetInternalMethods returned object.
  • Add fine-grained conditional audit logging or access control without changing application code.
  • Track down problematic statements that you suspect may be issued by the application, but not logged anywhere else.
  • Shard your data, and use statement interceptors to route statements to appropriate MySQL instances (or combine results fetched from multiple MySQL servers)  – again without changing application code.
  • Implement low-overhead “ping” operation for connection pools that don’t allow you to define your own validation query.

There’s a lot that can be done with statement interceptors, and they are very easy to wire up – there’s really only five methods you need to implement:

  • init()  – You can set up state variables here, if needed.  Returns void, so leaving this empty is fine.
  • preProcess() – This is where you return a ResultSetInternalMethods object if you want to bypass the normal operation of the statement.  This is called before the statement is sent to the server, so you can change what is sent – or even bypass the sending – here.  If you return null, the driver executes the statement as it normally would.  If you return a non-null object, further execution is bypassed.  Note that the SQL String argument will be null for PreparedStatement objects; you’ll need to handle Statement and PreparedStatement executions differently in order to examine the SQL being sent.
  • postProcess() – Like preProcess(), but invoked by the driver after the server has returned a result set.  This allows you to change the results returned or wrap them with some sort of custom decorator.
  • executeTopLevelOnly() – Return true if you are issuing queries inside preProcess() or postProcess() that could cause infinite recursion.
  • destroy() – Clean up any local references you created in init() here.

The demo code provided will give you a simple example of how to implement statement interceptors, but what you do with them is really limited only by your imagination.


Connector/J extension points – lifecycle interceptors

This is the first of a handful of posts to augment the presentations I gave at Java One and Silicon Valley Code Camp earlier this month.  It seems I significantly overestimated how much content I could effectively deliver in the time allotted, and left a few of my major points untouched.  These blog posts will try to rectify that.

The first major area I failed to cover in depth was really “Extension Points”, starting from slide #56.  There are four major extension points in Connector/J:

  • Lifecycle Interceptors
  • Statement Interceptors
  • Exception Interceptors
  • Loadbalancing Strategies

We’ll look at the first in this post.

Connection lifecycle events can be useful for instrumenting or debugging application database behavior, without changing application code.  In Connector/J, you can intercept the following lifecycle events by implementing com.mysql.jdbc.ConnectionLifecycleInterceptor:

  • Connection creation (via the init() method)
  • Connection.commit()
  • Connection.rollback()
  • Connection.setAutoCommit()
  • Connection.close()
  • Connection.setCatalog()
  • Transaction start/end (via transactionBegun() and transactionCompleted() methods)
  • Connection.close()
  • Connection object destruction (via destroy() method)

So, how might this be useful?  In the demo code provided, I implemented code that prints the stack trace when Connection.rollback() is called.  Perhaps you are trying to understand where rollbacks are coming from in your application – the demo code lets you do just that.  The steps are fully illustrated in the demo code:

  1. Create a lifecycle interceptor that implements com.mysql.jdbc.ConnectionLifecycleInterceptor.
  2. Add logging in the rollback() method to track whatever you require.
  3. Start the application and list the fully-qualified class name of the lifecycle interceptor you created in step #1 as the value for property, “connectionLifecycleInterceptors”.  In the demo code, this is found in demo.connectorj.LifecycleInterceptorExample at line 23:

props.setProperty(“connectionLifecycleInterceptors”, “demo.connectorj.plugins.ExampleLifecycleInterceptor”);

That’s it!

What other lifecycle events might you be interested in tracking?  Well, you might be interested in tracking the time between transaction start and end times.  You might be interested to know how many times setAutoCommit() is called.  It’s convenient that you can do all of this without modifying application code.

Connector/J Presentation at JavaOne and SVCC

I’ve uploaded both the presentation materials and demo code used in my JavaOne and Silicon Valley Code Camp presentations. Since I ran out of time at JavaOne, I’ll be writing blog posts later this coming week to cover the material I didn’t get a chance to complete there.

UPDATE:  I’ve started adding posts fleshing out the presentation materials, which I will index below: