Todd's MySQL Blog

Tiny tidbits of trivia from Todd

Archive for the tag “Connector/J”

Live reconfiguration of replication topography in Connector/Java

As noted in a previous post, MySQL Connector/Java supports multi-master replication topographies as of version 5.1.27, allowing you to scale read load to slaves while directing write traffic to multi-master (or replication ring) servers.  The new release of version 5.1.28 builds upon this, allowing live management of replication host (single or multi-master) topographies.  This parallels functionality that has long existed for load-balanced connections, and enables users to add or remove hosts – or now promote slaves – for Java applications without requiring application restart.  This post aims to explain how to leverage this functionality (the TL;DR/fun demo is found in the examples section)

Read more…

Multi-master support in MySQL Connector/Java

MySQL Connector/Java has long had support for replication-aware deployment, allowing a single logical Connection object to effectively “pool” connections to both a master and (potentially multiple) slaves.  This allowed scale-out of read load by distribution of read traffic to slaves, while routing write load to the master.  The JDBC specification provides a nice hook to know what’s read-only traffic – Connection.setReadOnly().  When a boolean value of true is passed, a ReplicationConnection will route further commands a selected slave instance, while values of false trigger routing to the master.  This is sufficient for many simple replication topographies, but not all – most notably, it has been difficult to handle multi-master deplyment.  MySQL Connector/Java 5.1.27 aims to solve that, and a number of related problems.

Read more…

Speaking at MySQL Connect

I'm Speaking at MySQL ConnectThe MySQL Connect content catalog is published, and I’ll be leading a hands-on lab on MySQL Enterprise Features in Practice [HOL9787].  If you have wondered how to get the most out of the features of MySQL Enterprise subscriptions – whether you are an existing Enterprise customer or not – this lab is for you.  We’ll help you understand the benefits of the various components of the MySQL Enterprise subscription as you install, configure, demonstrate and use the features.  You’ll learn how best practices and helpful tips, and work through sample customization exercises illustrating how tools such as MySQL Enterprise Monitor, MySQL Enterprise Backup and Security, Audit and Scalability components of MySQL Server can be applied to your MySQL use cases.  I’ll be joined by Engineering staff responsible for several of these key products/features, so it’s a great opportunity to learn more about features that can make your life easier directly from the experts!

It’s also very likely I will be found at the Application Development with MySQL, Java, PHP, and Python [BOF4743] if you want to talk Java with me.

Practical P_S: Fixing gaps in GLOBAL STATUS

Over three years ago, I noticed that there was no STATUS counter for COM_PING commands – something that is useful for ensuring proper configuration of JDBC connection pools.  Mark Leith even provided a patch, but it’s never been incorporated.  With the advances PERFORMANCE_SCHEMA makes in MySQL 5.6, that’s OK – a STATUS counter becomes somewhat redundant:

mysql> SELECT SUM(count_star) as pings
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name = 'statement/com/Ping';
+-------+
| pings |
+-------+
|    12 |
+-------+
1 row in set (0.02 sec)

Read more…

Finding the source of problematic queries

Many MySQL users are familiar with using slow query logs and tools such as mysqldumpslow to identify poor-performing SQL commands, and MySQL 5.6 introduces new powerful tools in PERFORMANCE_SCHEMA.  Both allow you to identify the date/time and the user account from which the command was issued, which is helpful – but if you’re using MySQL Enterprise Monitor (MEM), you can immediately identify the actual line of code responsible for the SQL command in question.  This happens to be one of my favorite and powerful features of MEM, but it’s frequently overlooked by new and experienced MEM users alike, so I’m writing the post to highlight it.

MySQL Enterprise Monitor, of course, is a commercial product that’s part of the MySQL Enterprise subscription.  But it’s freely-available under 30-day trial terms for evaluation from Oracle Software Delivery Cloud – if you aren’t a commercial customer, consider downloading MEM to see what it can do for you.  And if you are a MySQL Enterprise subscriber who hasn’t deployed MEM, or haven’t yet explored some of the more advanced features, now’s the time to do so.

Read more…

Connector/J 5.1.25 Released

MySQL Connector/J 5.1.25 has been released, and is available in Community and Enterprise flavors on dev.mysql.com and My Oracle Support, respectively.  I’ve already noted the addition of support for connection attributes for MySQL 5.6 – 5.1.25 adds this functionality.  This release also includes a fix for Bug#68733, which caused the special light-weight ping operation to execute only against the master and currently-selected slave, rather than the master plus all active slave connections.  The ping operation exists to help ensure connections aren’t left idle for excessive durations in the internal load-balancing and replication “pools”, so failure to ping *all* active slave connections will make CommunicationFailureExceptions more likely after a rebalance operation selects a non-active, long-idle physical connection.  This bug was found by Dominic Tootell and reported via the community forums in a wonderfully-researched post.  It’s a great example of the type of community feedback and interaction that helps improve MySQL quality – thanks for the great contribution, Dominic!

In working through the bug Dominic reported, I found a couple other small bugs to fix.  The first is Bug#68763, which caused ReplicationConnection.isMasterConnection() to incorrectly always report false.  The second (Bug#68664) was an Ant build script bug which caused builds in Eclipse to produce a .JAR file – but it contained no .class files.  Our standard builds are done from the Ant script rather than Eclipse, and I’ve typically worked around it by running Ant from the command-line when I needed to package (compiling has worked in Eclipse for some time thanks to Mark Matthews, just not packaging).  Now you can package a .JAR file without leaving Eclipse.  In another bug report, Alex Soklakov fixed Bug#68801, making Connector/J play nicely with Maven.

I hope you enjoy Connector/J 5.1.25!

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: Read more…

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 bugs.mysql.com).

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.

 

Post Navigation