[Looking for Charlie's main web site?]

Solving seeming ColdFusion / MySQL 5.6 incompatibility, by updating CF's MySQL driver

Note: This blog post is from 2013. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.
If you're running ColdFusion 10 (and perhaps also CF 9 or earlier), you will find that if you update your MySQL installation to version 5.6, you'll get the following error from any SQL you try to run from CFQUERY (and perhaps other CF querying tags, like CFSTOREDPROC):

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT'

Summary: There is a reasonable explanation and a rather simple solution: update the MySQL driver that CF is using to at least version 5.1.22 of the driver, the first to support MySQL 5.6, because the one built into CF 10 (driver version 5.1.17) not only does not. More important, that older driver uses something that causes the failure above in 5.6.

That explanation of the "solution" may be enough for some to take the ball and run with it (and if not, I will offer more details on how to do that), though it should be noted that updating the driver is not formally supported, nor is MySQL 5.6 technically supported at all in CF10 (or 9). But for those who will press on knowing that risk, you now know what you need to do.

But as often, there's much more to this than meets the eye, so I hope you will follow along to learn more. I have broken this into two parts:

  • the problem (with what I hope is helpful explanation of what the real root of the problem is),
  • who's to blame (not Adobe, I will argue)
  • and the solution (with some caveats that even experienced folks, or those who don't care about "the problem", should still read.

And again, while I discuss this in the context of CF10, where I've seen the problem happen, it could apply also to CF9 (and it seems reasonable that it would), so all the information still applies, it would seem.

[....Continue Reading....]

Comments
Thanks for covering this Charlie. I already ran into this on my dev server and figured it would get corrected in a future patch.
# Posted By Rick Smith | 4/25/13 2:15 AM
There was a lot of conflicting information on what the problem is and how to solve it after searching OPTION SQL_SELECT_LIMIT=DEFAULT. I was quite frustrated before I gave you a call. I'm glad you take the time to post the knowledge for others. Thanks Charlie.
# Posted By Ryan Trask | 4/26/13 3:03 AM
@Ryan, yep it was an interesting challenge. And it was in fact because of the paucity of info on the web about it, that first you and then I found on our call (and with none that seemed to completely explain things), which led me to want to document what I found in my research.

It was also another great example of how things that seem "simple" on the surface have a lot of nuance and subtlety that calls for us all to be more careful in considering various "solutions" offered on the web. It's a frequent source of confusion for many, especially those who come to me when they've exhausted their own searching. :-)

I love to help, and I'm glad we were able to resolve this, and that I was able to share this additional detail for you and others.
# Posted By Charlie Arehart | 4/26/13 10:29 AM
Thanks for help, it saved me. But if u use ColdFusion 9, you have to put your jar in - C:\ColdFusion9\lib. Because in 9 u don't have directory \coldfusion10\cfusion\lib. I hope it will help to others.
# Posted By Vadim Korolov | 12/10/13 11:06 PM
This solved my problem. Thank you!
# Posted By Brian Lang | 12/11/13 2:11 PM
@Vadim, fair enough. I said in the entry that I'd written it for CF10, and wasn't sure it was even an issue for CF9. If you're saying that it does, then thanks, and sure, that would be the path location in CF9, at least in Standard or the Enterprise Server edition. Thanks for sharing.

We should note that for the Multiserver edition, it would be deep within each instance, such as C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\lib.

@Brian, great to hear that it was helpful. Thanks for sharing the feedback.
# Posted By Charlie Arehart | 12/16/13 10:59 PM
Thank you so much for this!
# Posted By Jacob Steelsmith | 5/8/15 12:22 PM
Thank you Charlie for explaining this and providing all the extra insight about the upgrade process. A huge help. - Jon C.
# Posted By Jon Cavanaugh | 7/23/15 1:10 PM
Glad to help, Jon, especially when you're a hoster where this help could multiple many times to your customers, and their customers. :-)
# Posted By charlie arehart | 7/24/15 1:02 AM
I have a same problem.

I installed HDP2.2/ambari 2.1.1/mysql5.6.27-log

[com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1]

so, I updated MySQL connector -> mysql-connector-java-5.1.32.jar

but I still see smae error log [ You have an error in your SQL syntax; check the ... ....]

how can i solve?
# Posted By chohee | 11/27/15 12:45 AM
chohee, are you sure you put the jar in the right place? and you restarted CF?

To confirm, go into your CF admin, to the "settings summary" page, and confirm that the jar appears in the classpath.

Report what you find.

PS You start saying that you installed "HDP2.2/ambari 2.1.1/mysql5.6.27-log". Did you really mean to add -log at the end? Are you saying this is an implementation of MySQL? doesn't the hdp refer to Hadoop?
# Posted By Charlie Arehart | 12/2/15 11:44 AM
I've downloaded the MySQL connector java 5.1, and I put the jar file in the cf_root/lib and restarted the cold fusion server. However, when I try to add a new MySQL 5 datasource, I continue to get the error message that instructs me to download the file, put it in the lib folder and restart, which I've already done. What am I missing?
# Posted By Sarah | 3/10/17 3:27 PM
More info: I am on a Mac OS X. I can navigate to the jar file on my hard drive. When I look at its properties, I can verify that it's in the right place: Coldfusion2016/cfusion/lib/mysql-connector-java-5.1.41-bin.jar and I've restarted the server multiple times, yet when I log in to the cf admin and view the server settings summary, the jar file is not showing up among the list. Any ideas?
# Posted By Sarah | 3/10/17 3:55 PM
Hi, Sarah. I wonder if the challenge for you is that, since OSX is essentially Linux/Unix, you need to do a chmod on the file to make.

More here (which is about doing it for mysql on CF11 on Unix): http://www.greenvall...

Let me know if that works out.
# Posted By Charlie Arehart | 3/11/17 2:28 PM
This helped me add the MySQL driver for Coldfusion 2018. Unfortunately, now I'm getting an "unrecognized time zone" error in the CF Admin Data Sources. "You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specific time zone value if you want to utilize time zone support." Hmmm. Nothing ever simple...
# Posted By MLipenk | 7/22/19 2:34 PM
Well, that would seem just a matter of setting that serverTimezone
as a connectionstring value. In the CF DSN advanced settings, you may see such a field, to which you can add it as a value.

Or if you don't see one and have only a JDBC URL, you can append it there.

Here's a doc with some info on that and example values, like EST5EDT. It lists others. It's not a CF-oriented resource, but it doesn't matter, as it shows a JDBC URL using it:

https://knowledgebas...

And if you DO NOT see a JDBC URL field in the CF DSN, add the value instead as a connection string.

Let us know if that gets you going.
# Posted By Charlie Arehart | 7/22/19 2:41 PM
@Charlie Arehart You are AWESOME. You saved me many hours of going down rabbit holes Googling and reading StackExchange! Finding info specific to CF, even the simplest of things, can be challenging.

Many sources referenced what you were getting at, but I couldn't figure out WHERE I'd enter the connection string in the ColdFusion Admin.

In the Coldfusion 2018 Administrator Data Sources ...under "Advanced" (to which you referred)... was a "Connection String" option. Simply adding something akin to: "serverTimezone=EST5EDT" (or in my case: serverTimezone=CST6CDT) removed the error I was getting and the MySQL data source connected successfully!

Indeed you got me going down the right track.

THANK YOU!!!
# Posted By MLipenk | 7/22/19 4:01 PM
Sweet. Always glad to help. :-) Thanks for the update and the kind regards.
# Posted By Charlie Arehart | 7/22/19 4:11 PM
Copyright ©2020 Charlie Arehart
Carehart Logo
BlogCFC was created by Raymond Camden. This blog is running version 5.005.
(Want to validate the html in this page?)

Managed Hosting Services provided by
Managed Dedicated Hosting