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.

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.

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.

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.

• presentation slides
  

• demo code

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

• Extension points – lifecycle interceptors
• Extension points – statement interceptors
• Extension points – exception interceptors