Search code examples
mysqlcoldfusioncoldfusion-10

CF10 , MYSQL SQL_SELECT_LIMIT=DEFAULT

I just set up CF10, win2008, IIS 7.5 with mysql 5.6. But we are getting this error:

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

This error is removed when I explicitly set "LIMIT" to the queries.

(Update from comments)

Here is an example: 

<cfquery name="dds" datasource ="#Request.Datasource#"> 
    SELECT * 
    FROM   tblaccounts 
    LIMIT 100 
</cfquery>

As far as my research shows, it's an incompatibility with the new version of mysql 5.6.

Solution

  • Borrowing some from the answers above with more detail:

    We were having the same problem and it was fixed by updating the JDBC file. Since ColdFusion 10 only supports MySQL 5.0 and 5.1, you need to update the Connector as mentioned above.

    To update the JDBC Connector on your Windows machine,

    1. Obtain the .zip connector from the link provided above
    2. Open the zip archive and locate the mysql-connector-java-5.1.23-bin.jar
    3. Copy this .jar file to C:\ColdFusion10\cfusion\lib\
    4. Open Services (Start > Control Panel [Optional] > Administrative Tools > Services)
    5. Stop/Start the "ColdFusion 10 Application Server" service

    To verify the new .jar is has been recognized by ColdFusion, login to your ColdFusion Administrator, click on "Settings Summary" under "Server Settings". Search for "mysql". You should see the .jar file listed under the "CF Server Java Class Path" section. There is no need to re-setup datasources. These changes should automatically apply.