[Looking for Charlie's main web site?]

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

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.

The crux of the problem: a feature used by the old MySQL driver is now obsoleted in MySQL 5.6

So the crux of the problem is something under the covers, in the MySQL driver embedded within ColdFusion (the driver version is 5.1.17, not to be confused with the MySQL version discussed here, 5.6.)

This older driver is issuing a 'SET OPTION SQL_SELECT_LIMIT=DEFAULT' command for each query sent to MySQL, and that's what's failing in MySQL 5.6.

To be clear, you won't see that statement in your own code. It's being put there by the driver on your behalf. (You also won't see it in the CF Admin, in your datasource's connection string, nor will you even find it "hidden" on the connection string as may be sometimes found in the JDBC URL shown for each DSN in the CF Admin "Setting Summary" page.) It's the driver that is putting it there, and an updated driver will fix the problem.

What's the change, and why are we only now seeing the problem?

So the problem is that MySQL 5.6 dropped support for setting options that way shown above, using "SET OPTION whatever". Instead, such a statement should be written as "SET whatever". But again this is not something we can control in CFML, nor did even Adobe have any control. It's being done for us by the MySQL driver that's embedded within CF (which Adobe licenses from MySQL).

For what it's worth, this change (obsoleting the SET OPTION statement) appears to be specific to MySQL 5.6.5 (http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html, which notes "Incompatible Change: The obsolete OPTION modifier for the SET statement has been removed."). That's worth noting in case someone says "I am running on 5.6 and do not have this problem". Maybe you have a version older than 5.6.5, specifically. The latest version, at the time of this writing, is 5.6.11. (The first general release candidate was 5.6.10.)

And the issue is that the driver embedded within CF10 (version 5.1.17) still used that old syntax in creating its connections.

The newest driver to support the correct syntax (which would work in MySQL 5.6) is the driver version 5.1.20 (http://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1-20.html). It notes, "A MySQLSyntaxErrorException could occur when calling certain methods while connected to a MySQL 5.6.5 or higher server. Affected methods included StatementImpl.execute() and PreparedStatement.execute(). The cause was the removal of the SET OPTION syntax in the MySQL Server. The methods were modified to use the newer SET syntax internally."

Who's to blame for this problem?

A natural reaction in a case like this is to wonder, who's to blame? And certainly some may be tempted to lay this at Adobe's feet. We should not, as I will explain. If anything, it's an issue in the MySQL driver not having been updated sooner, and that's created by the MySQL team! Let me explain.

One might blame the MySQL driver makers--who are MySQL themselves

The MySQL 5.1.17 driver which is provided with CF10 was released in July 2011 (dates at http://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1.html).

One can reasonably ask why this now-obsoleted feature was not removed by the MySQL team even before then, because the deprecation was documented at least back in MySQL 5.0 which is from 2005. We can find reference to the deprecation in the original 5.0 docs (http://dev.mysql.com/doc/refman/5.0/en/set-statement.html), which note that "Older versions of MySQL employed SET OPTION, but this syntax is deprecated in favor of SET without OPTION."

So it's somewhat surprising that it took them until the 5.1.20 driver (dated May 2012) to remove that deprecated feature. Especially if they (the MySQL team) knew that 5.6 (which also was under development in 2011 and 2012) was going to deprecate that SET OPTION statement. And that 5.6.5 release which did obsolete it was from April 2012, so we can see that they updated the driver only after they deprecated the feature. (FWIW, this 5.6.5 release was before the actual release candidate for 5.6, which was 5.6.10 in Feb 2013, so at least they did update the driver some months before the final release of 5.6.)

But I think it's a fair question to ask: why did it take 7 years to remove a feature in the driver that was deprecated and would be obsoleted? Of course, people in glass houses shouldn't throw stones: CF has listed the parameterexists function as deprecated since about CF 3, in favor of isdefined, and yet it still works. There can be a lot of factors to be weighed in deciding when to finally pull the plug on some deprecated language feature.

So can one blame Adobe of releasing CF10 with an "old" MySQL driver? I don't think so

CF10 was released in May 2012. So, given that it included the 5.1.17 driver (which was released in July 2011), one might argue that was an "old driver" by then (11 months difference). But again let's check facts:

The next driver after that, 5.1.18, was updated Oct 2011. Ok, one could quibble that CF10 could have included that, as there should have been enough time to test that before the release in May 2012. But if you look at its release notes, it had only a few bug fixes and no new features, so it's nothing to get excited about.

The next one after that, 5.1.19, was dated April 2012. I could totally understand Adobe not trying to fit that into CF10, as CF10 would have been frozen by April 2012. So at worst, we could say they were 1 point release "behind" (if we would argue they could have put in 5.1.18, at least, though it was a meager update it seems.)

But to the point of this blog entry, that would not have mattered at all to this problem!

Again, the first MySQL driver version that would have prevented this problem, 5.1.20, was released in May 2012, which is literally the same month that CF10 was released. And there's no way Adobe would have updated the driver version in the days before release of CF10.

More than that, even though they clearly indicated that CF10 would support only up to MySQL 5.1, one could not even argue that Adobe could have foreseen this problem with the change in the driver, even if they wanted to anticipate a potential conflict for MySQL 5.6 users, as there's not even any reference to this SET OPTION being obsoleted in the base 5.6 release notes, at http://dev.mysql.com/doc/relnotes/mysql/5.6/en/, nor from pages linked from it. It only pops up in the news about 5.6.5, specifically, as I noted above.

So let's not try to lay any blame. It's just a "series of unfortunate events". Let's talk about how to solve the problem.

The solution: update the MySQL driver CF uses to at least 5.1.22 (or revert to a version of MySQL before 5.6)

Given the discussion above, the solutions are therefore simple. You could either:

  • a) revert to a version of MySQL before 5.6, which introduced the breaking change. But that would be a pretty major "fix", especially if there may be an easier solution
  • b) or you could just update the MySQL driver included in CF10 to a version that fixes the problem.

I'd vote for option b. :-)

Which MySQL driver version do we need?

Technically, we need to upgrade to at least driver version 5.1.22, as I indicated at the opening of the blog entry. Why do I say that?

Well, I had noted above that 5.1.20 was the driver that longer uses that failing SET OPTION statement, but it is not until driver version 5.1.22 that the docs indicate it supports MySQL 5.6, which is when the SET OPTION feature became obsolete, so we do need at least 5.1.22.

As an aside: the technote for driver version 5.1.22, at http://lists.mysql.com/java/9357, is the first to list 5.6 support, though it mistakenly has text on the line refers to itself as 5.1.21: "Version 5.1.21 is suitable for use with many MySQL server versions, including 4.1, 5.0, 5.1, 5.4, 5.5 and 5.6."

To be clear though, the technote for 5.1.21, http://lists.mysql.com/java/9353, does NOT mention support for 5.6, which is why it seems 5.1.22 is what we want.

But I'll point out in a moment that the current latest driver version is 5.1.24, which you may as well go ahead and get, so that debate about .21 or .22 is rather moot.

Applying the updated driver in CF10

Updating a database driver in CF is pretty easy, though I should note that technically it's not formally "supported" by Adobe, at least until they have not tested CF with that driver. There have been times in CF's past when Adobe did sanction, support, and document upgrading a JDBC drivers

But to be clear, CF 10 (or 9) does not currently support anything more than MySQL 5.1. Still, people have been using later versions of MySQL, knowing the risk. It's only this change in 5.6 that has proven a real show-stopper, at least until you update the driver.

So as for updating the driver to 5.1.22 (or above), I will note that others have done it and reported success (including at least one customer of mine). You will also find this issue discussed, though far more briefly, at a CF bug entry (https://bugbase.adobe.com/index.cfm?event=bug&id=3506758) and a stack overflow question (http://stackoverflow.com/questions/15038312/cf10-mysql-sql-select-limit-default).

There, in one of the question's "answers" offered by Clarke Schroeder, he offers the rather simple steps to take to update the driver, which involves first downloading the updated driver, then dropping that new driver into CF, and restarting. It's as simple (conceptually) as that. See this direct link to his answer: http://stackoverflow.com/a/15119005/90802 (and vote up his answer to show your thanks for his documenting it).

Some details he left out, which you will want to know

While he does make it clear that it's fairly easy, there are a few points that Clarke does not mention, a couple of which are modest helps, but a couple of which I think are pretty critical:

  • The link offered in Clarke's answer, pointing to the MySQL site for the driver (http://dev.mysql.com/downloads/connector/j/), will (as of today) get version 5.1.24, which does indeed support 5.6 and of course includes that removal of the use of the SET OPTION, and is the one that my client used today. Granted, it's only 1 day of testing, but I leave you with all the info here to decide what's best for yourself. At least now you should better understand what this is all about.
  • When you try to download the zip or tar file (you're offered the choice), note that you don't need to login with or signup for an oracle account. There is an option below those for "No thanks, just start my download."
  • When you open/extract the zip/tar archive file, you just want the mysql-connector-java-5.1.24-bin.jar file, offered in the one subdirectory found in the root of the archive file. You don't need the other sibling files or folders.
  • Clarke notes that this new jar file should be saved in \coldfusion10\cfusion\lib. And that's right, assuming you have either CF 10 Standard, or only one instance of CF 10 Enterprise. But if you do add any new instances in CF10 Enterprise, then note that the jar needs to be updated in lib of EACH INSTANCE that will be using a MySQL 5.6 datasource, specifically by copying it into \coldfusion10\instancename\lib. You would need to restart CF for this to take affect, but note that I would recommend you do one more step first:
  • Note that you will still find the old mysql driver in that directory (mysql-connector-java-commercial-5.1.17-bin.jar, as of CF10). You might be tempted to delete it, but you may want to revert back to it. So you could move it, or you may be tempted to rename that file, which is fine, but there are two very important points to note when renaming jars of any kind:
    • First, note that you can't rename the jar file unless you stop CF first (because CF has already loaded it at startup).
    • Second, beware that you don't want to rename Java jars by simply changing just their name (the part before the .jar extension). The JVM really doesn't pay attention to the file name (long story, but it could still try to load the classes within that jar, if it renamed there and were named something like mysql-connector-java-commercial-5.1.17-bin-OLD.jar). Instead, you need to rename the file extension (.jar) to something else, like .jar.OLD, as in mysql-connector-java-commercial-5.1.17-bin.jar.OLD.
  • After these changes, restart CF, and you should find that the error goes away. If not, please carefully review the above before raising a question here in the comments. Or consider the additional support option I mention at the end of this entry.

Finally, do note that the jar in CF10 is technically the "commercial" jar (note that word is in the name), whereas the one pointed to by Clarke's answer is the open source one. I'm not aware of any differences between the commercial and open source jars. I think the difference is just that for Adobe to have been able to distribute the driver with CF, they had to license it (and that is reflected in the name of the jar). But we are all each permitted to obtain and use the open source driver, even in a commercial deployment of CF. I suspect that will raise some doubt to some readers.

As always in such cases, I should say "I am not a lawyer" and you should explore those things yourself, but I will point out this blog entry that does a good job of explaining things in simple terms: http://www.xaprb.com/blog/2009/02/17/when-are-you-required-to-have-a-commercial-mysql-license/.

Conclusion

So there you have it. As I said, there was a lot more to this topic than may have seemed at first glance. That's almost always the case with most challenges with CF, and that is indeed why I do this sort of CF troubleshooting for a living. I love helping people understand and resolve these problems, whether it's in blog entries like this, or on forums or mailing lists, or by directly assisting people via my remote short-term consulting.

Would you like more directed assistance with this or other problems

If you would like me to do this driver update, or guide you through it, just reach out to me by email, phone, or twitter using the contact information offered at that page, and I'll be happy to help. We should be able to do it very quickly, and I have no minimum time requirement so you should find it a cost-effective solution, if you're having any doubts about the above.

We would work over the web and phone, using a shared desktop solution where I can "watch over your shoulders", without need for you to open any firewalls or grant me an account on your server.

Same with any CF server troubleshooting challenge you may have, which I can often help you solve quite quickly.

See that consulting page for more on my rates, approach, and satisfaction guarantee. Don't feel you need to suffer with a problem, or sift through dozens of google results trying different solutions for a given problem. This is all I do all day, each day, and I can often help you solve a problem quickly, and as you can see from my blog, I also want to help you understand the problem, and indeed how to solve problems using various diagnostics, so that eventually you don't even need me! :-)

But for those who don't have time even to read all the info I offer here on the blogs and elsewhere, I can help you digest it and apply it to your specific situation, so let me know if I can ever help.

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
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