Tag Archives: Java

Secure Java Connections by Default

MySQL Connector/Java 5.1.38 was released earlier this week, and it includes a notable improvement related to secure connections.  Here’s how the change log describes it:

When connecting to a MySQL server 5.7 instance that supports TLS, Connector/J now prefers a TLS over a plain TCP connection.

This mirrors changes made in 5.7 to the behavior of MySQL command-line clients and libmysql client library.  Coupled with the streamlined/automatic generation of TLS key material to ensure TLS availability in MySQL Server 5.7 deployments, this is an important step towards providing secure communication in default deployments. Continue reading Secure Java Connections by Default

Practical P_S: Find Client JRE Version Using SQL

MySQL Connector/Java supports connection attributes since version 5.1.25.  This projects useful metadata about the client environment into the database, where MySQL administrators can query PERFORMANCE_SCHEMA tables to remotely survey application deployment environments.  One useful piece of information exposed is the version and vendor of the JVM in use by the client.  This very short blog demonstrates how to get this information from PERFORMANCE_SCHEMA.

Continue reading Practical P_S: Find Client JRE Version Using SQL

SSL/TLS Connections to Recent MySQL Servers in Java

Recent changes to support better security by increasing strength of Diffie-Hellman cipher suites from 512-bit to 2048-bit were introduced to MySQL Server 5.7. While this change enhances security, it is an aggressive change in that 2048-bit DH ciphers are not universally supported. This has become a problem specifically for Java users, as only Java 8 JRE (currently) supports DH ciphers greater than 1024 bits. Making the problem more acute, this change was back-ported from MySQL Server 5.7 to the recent 5.6.26 and 5.5.45 releases in response to a community bug report. This blog post will identify affected applications, existing workarounds, and our plans to provide a more permanent solution in upcoming maintenance releases.
Continue reading SSL/TLS Connections to Recent MySQL Servers in Java

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.

Continue reading Multi-master support in MySQL Connector/Java

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)

Continue reading Practical P_S: Fixing gaps in GLOBAL STATUS

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.

Continue reading Finding the source of problematic queries

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: Continue reading More GlassFish loadbalancing tips for Connector/J

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.