Start a new topic

Error in your SQL syntax ... 'OPTION SQL_SELECT_LIMIT=1000'

[This topic is migrated from our old forums. The original author name has been removed]

Hello folks When i try to look at data on a table i receive: An error occurred while executing the database request for: MySQL 5.6.10 MySQL-AB JDBC Driver mysql-connector-java-5.1.16 ( Revision: ${bzr.revision-id} ) Short message: An error occurred while performing the operation: 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=1000' at line 1 The command that caused the problem: select * from `ba_arcid` Long Message: 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=1000' at line 1 Details: Type: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException Error Code: 1064 SQL State: 42000 System Information: Product: DbVisualizer Personal 8.0.5 Build: #1714 (2011/11/03 18:14) Java VM: Java HotSpot(TM) Client VM Java Version: 1.6.0_26 Java Vendor: Sun Microsystems Inc. OS Name: Windows NT (unknown) OS Arch: x86 OS Version: 6.2 I have downloaded the latest mysql jdbc connector and copied it to *C:\Program Files (x86)\DbVisualizer-8.0.5\jdbc\mysql\mysql.jar*. This made it work. However what is causing this and what is the cleanest way to fix this? Thanks in adavance

[This reply is migrated from our old forums. The original author name has been removed]

Re: Error in your SQL syntax ... 'OPTION SQL_SELECT_LIMIT=1000'
FYI, If someone else runs into the same problem. I figured out the reason, there was a 5.1.15 driver at: /Library/Java/Extensions/mysql-connector-java-5.1.15.jar that was always being used, regardless of what you picked in the DbVisualizer setup. Removing it from there solved the problem.
[This reply is migrated from our old forums.]

Re: Error in your SQL syntax ... 'OPTION SQL_SELECT_LIMIT=1000'
Martijn, The reason an older version of the MySQL driver is used may be that it is included in the Java CLASSPATH which is read first by DbVisualizer. To find out open Tools->Debug Window and enable Debug DbVisualizer. Now connect to the MySQL DB server and check the log. Please post it here. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Error in your SQL syntax ... 'OPTION SQL_SELECT_LIMIT=1000'
Ok, it seems I'm running into the same problem. But in addition, i'm running the latest version of DBVis (9.1.8), and I've updated the JDBC drivers with 5.1.30. In fact, in my error log it now shows for driver information: *DefaultClass: com.mysql.jdbc.Driver* *Paths:* *Path: /Applications/DbVisualizer.app/Contents/java/app/jdbc/mysql/mysql-connector-java-5.1.30-bin.jar* *Type: dynamic* *Classes: com.mysql.jdbc.Driver* However the connection message shows: *MySQL* *5.6.17* *MySQL-AB JDBC Driver* *mysql-connector-java-5.1.15 ( Revision: ${bzr.revision-id} )* Why does it keep insisting in the connection message "mysql-connector-java-5.1.15" ??? (and more importantly, keep getting the error related to the OPTION vs SET OPTION). Thanks, Martijn
[This reply is migrated from our old forums.]

Re: Error in your SQL syntax ... 'OPTION SQL_SELECT_LIMIT=1000'
Matthias, The problem you got seem to be driver related and upgrading it should solve it. Here is a discussion thread on the topic: http://bugs.mysql.com/bug.php?id=66659 Later versions of DbVisualizer include newer MySQL drivers. I suggest you look into upgrading at least to 8.0.12 (which should be free if you are licensed for 8.0.5). Even better is that you upgrade to 9.1.x as it include many improvements since 8.0.x. Regards Roger