Error Executing Database Query

[Guest]

Error Executing Database Query.

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

I keep getting this error everytime I try to log into my inventory database I set up.  I cn not find it anywhere in order to view or change the syntax.  Please help!

[Carl Von Stetten]

What version of ColdFusion are you running?  Also, what version of MySQL?  This particular error may be a known issue related to version incompatibilities between CF and MySQL.  This was the first hit on Google when searching "OPTION SQL_SELECT_LIMIT=DEFAULT": http://stackoverflow.com/questions/15038312/cf10-mysql-sql-select-limit-default

If this is indeed your issue, more detailed information can be found on Charlie Aerhart's blog.

-Carl V.

[Guest]

My ColdFusion version is 9 and MySQL version is 5.6

On Tue, Apr 8, 2014 at 12:45 PM, Carl Von Stetten

[Carl Von Stetten]

CF9 and CF10 (prior to update 11) don't officially support MySQL above version 5.1.  You will need to update your MySQL JDBC connector to support MySQL 5.6 (although it won't be supported by Adobe).   See the links in my first reply for details on how to do this.

-Carl V.

[BKBK]

I am surprised that this issue has turned up. I had that error months ago!

The references Carl gives got it right. The error is indeed the result of applying an old MySQL driver to the new version of MySQL, 5.6.

Internal code in older drivers uses the syntax SET OPTION SQL_SELECT_LIMIT=DEFAULT. However, this syntax was deprecated way back in MySQL 5.0, in favour of SET SQL_SELECT_LIMIT=DEFAULT. MySQL 5.6 expects the new syntax, hence the conflict. It is therefore not the result of incompatibility between ColdFusion and MySQL.

In my opinion, MySQL is entirely to blame for the issue. It is a widespread and potentially serious show-stopper, and the MySQL team knew about it long ago. However, they failed to drum up the kind of noise that it deserved. They should have screamed from the roof-tops that MySQL 5.6 users should install mysql-connector-java-5.1.23-bin.jar or newer, given that corporations are notorious for integrating components whose versions are eons apart.