Skip to main content
Ben Nadel at the jQuery Conference 2009 (Cambridge, MA) with: Vlad Filippov
Ben Nadel at the jQuery Conference 2009 (Cambridge, MA) with: Vlad Filippov

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

By
Published in ,

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.

Short link: https://bennadel.com/3315

Reader Comments

Oh my chickens, this post is old!
I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
— Ben Nadel