Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at cf.Objective() 2013 (Bloomington, MN) with: Max Pappas
Ben Nadel at cf.Objective() 2013 (Bloomington, MN) with: Max Pappas@maxian )

The MySQL 5.1 Java Driver Deprecates validationQuery Which Is Why "SELECT 1" May Not Show Up In Your ColdFusion Application

By Ben Nadel on
Tags: ColdFusion, SQL

Several months ago, our database team lowered the connection idle timeout on one of our MySQL instances to 20-minutes (instead of the default 8-hours). This caused an immediate (and ongoing) slew of "Communications link failure" errors in our ColdFusion code, even when I tried lowering the connection idle timeout to 10-minutes in our data-source configuration. I was going to enable connection validation (the validateConnection setting); but, I couldn't get it to work locally. I had all but given up on fixing this problem when Ben Darfler - the other B'engineer at work - educated me on the JDBC 4 API, which has replaced the validationQuery with a light-weight PING command.

If you've ever had problems with your connection pool, you've likely tried enabling the "Validate Connection" setting, using "SELECT 1" as the validation query in your ColdFusion Administer:


 
 
 

 
 ColdFusion MySQL driver - validate connection data-source settings. 
 
 
 

The SQL query associated with the validation command is run every time a connection is taken out of the connection pool. Which means, it gets executed a whole heck of a lot. As such, developers tend to use "SELECT 1" because it's essentially the fastest, most light-weight SQL statement you can execute.

I had enabled the MySQL 5 Connector/J settings for validateConnection and validationQuery in my local Docker container; but, when I went to observe the MySQL general-log, nothing was showing up:


 
 
 

 
 MySQL general log does not show SELECT 1 with the validationQuery setting. 
 
 
 

I didn't want to deploy this configuration change without being able to verify it locally; so, it just sat there for months. Until, Ben Darfler showed me that the MySQL Connector/J driver that we use - 5.1.17 - conforms to the JDBC 4 API, which no longer uses the validationQuery setting. It still uses the validateConnection setting; only, instead of executing the validationQuery, it sends an even-more-light-weight PING command:

With this insight, I finally deployed the ColdFusion MySQL 5 data-source configuration change, and it seemed to have a very positive effect on our ColdFusion connection pool error rate:


 
 
 

 
 ColdFusion MySQL 5 validateConnection send PING command, which reduces communications link failure errors. 
 
 
 

As you can see, when we deployed the ColdFusion MySQL 5 data-source configuration change - enabling the validateConnection setting - there was a rather dramatic drop in "Communications link failure" errors. The errors have not completely stopped - we're still getting several dozen a day. But, compared to the several thousand we had been seeing (per day), this is a much welcomed improvement (especially for our users who were very likely experiencing those connection errors as 500 Server Errors).

I wish I understood more about the Java driver for MySQL. I'm still confused as to why my idle-timeout settings on the ColdFusion side didn't prevent this problem in the first place. That said, hopefully this helps anyone else who might be confused as to why their MySQL validationQuery doesn't appear to be running in their ColdFusion application.



Looking For A New Job?

Ooops, there are no jobs. Post one now for only $29 and own this real estate!

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader Comments

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.