Improving MySQL JNDI Connection Reliability

I blogged quite some time ago about using JNDI to configure database connections in Dodeca. As I mentioned then, JNDI can bring some useful improvements to your configuration, management, security, and administration of your environment versus how you might be configuring normal JDBC connections. To be clear, this isn’t because JNDI connections are inherently better from a performance standpoint, it’s just that it might be a cleaner solution in various ways.

My original blog post looked at configuring a pretty typical MySQL connection in JNDI. As I have worked with this in the last few months, I have run into a few issues with the configuration as it related to connection timeout issues. I was occasionally getting some timeout issues like this:

Dodeca error dialog reporting a timed out MySQL JNDI connection

MySQL connection timeout when configured with JNDI

Helpfully enough (or perhaps unhelpfully) the error message itself reports that perhaps the autoReconnect=true setting would be of help. I’ve actually used that setting in the past and it seemed to help things out. But as it turns out, that setting is deprecated and should not be used. There are some alternative techniques that can/should be used to ensure the program gets a valid connection back.

One common technique is to specify a “validation query”. This is often something like SELECT 1 or SELECT 1 FROM DUAL depending on the particular database technology being used. You can use SELECT 1 for MySQL. What this essentially means is that before returning a connection via JNDI to the Java servlet to do things with, the connection pool manager is going to run the validation query to ensure that it is indeed a valid connection (able to connect, doesn’t error out, and so on.

Interestingly enough, MySQL in particular has added an optimization for this use case such that you can give it a sort of fake query (code: /* ping */) and it’s slightly more optimized than the overhead involved with a SELECT 1.

Together with this optimized test query, some additional attributes on the JNDI configuration (testWhileIdle, testOnBorrow, testOnReturn, and removedAbandoned, I’ve updated the overall JNDI configuration and it seems to be much more robust. Here’s the new connection JNDI code from my Tomcat context.xml:

<Resource name="jdbc/dodeca_sample" auth="Container" type="javax.sql.DataSource" username="dodeca" password="password" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/dodeca_sample?noDatetimeStringSync=true" maxActive="100" maxIdle="30" maxWait="10000" removeAbandoned="true" removeAbandonedTimeout="20" logAbandoned="true" validationQuery="/* ping */" testWhileIdle="true" testOnBorrow="true" testOnReturn="false" />
<ResourceLink name="jdbc/dodeca_sample" global="jdbc/dodeca_sample" type="javax.sql.DataSource"/>

JDBC and JNDI connections compared (with a Dodeca example)

Have you ever wondered what the difference between a JDBC and a JNDI connection is? If you’re familiar with at least one of these, it’s likely that you’re familiar with JDBC (but probably not JNDI).

JDBC connections come up often in the Oracle world (for good reason). It’s a standard model/framework for designing drivers that interact with relational databases. As it pertains to us in the Hyperion, Dodeca (and even Drillbridge!) world is that we often define connections in terms of specifying JDBC parameters. This typically means a driver class name (like com.mysql.jdbc.Driver for a MySQL driver), a JDBC URL (a URL specifying a server and optionally a database/schema and other parameters), and credentials (username/password). So if you’ve poked around in your infrastructure much at all, there’s a good chance that you’ve come across a JDBC connection.

You may have even come across something called JNDI and even vaguely known it was sort of an alternate way to configure a connection but never really had to bother with it. I’ll spare you the acronym details, but think of JNDI as a way of organizing database connections (and other objects actually, but we don’t need to worry about that at the moment) such that instead of our app/system having to know the server name and credentials, it just asks “Hello, can I have the resource that was defined for me with name XYZ?”

Continue Reading…