The MySQL 5.1 Java Driver Deprecates validationQuery Which Is Why "SELECT 1" May Not Show Up In Your ColdFusion Application
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:
| |
|
|
||
| |
 |
|
||
| |
|
|
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:
| |
|
|
||
| |
 |
|
||
| |
|
|
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:
- MySQL Connector/J - JDBC4Connection.isValid()
- MySQL Connector/J - ConnectionImpl.pingInternal()
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:
| |
|
|
||
| |
 |
|
||
| |
|
|
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.
Reader Comments