Error code 1064, SQL state 42000: You have an erro

Question

I'm using latest version of MySQL ==> mysql-5.6.10-winx64.zip

Created the database and every thing is ok 'I think' when I try to execute this simple command;

"select * from family"

I got this error :

Error code 1064, SQL state 42000: 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've spent much time searching for a solution but no solution was found :(

Answer 1

I had the same problem few weeks back. Followed the following steps and it very much resolved the issue.

1. Copied the latest version (mysql-connector-java-5.1.23-bin) of the jar file to ..\NetBeans 7.3\ide\modules\ext. My earlier version of the driver was mysql-connector-java-5.1.18-bin.

2. Change the driver version within NetBeans IDE. In the IDE's Services window, expand Drivers -> right-click on MySQL (Connector/J driver) and select Customize. Remove the earlier driver and point it to the latest one (C:\Program Files (x86)\NetBeans 7.3\ide\modules\ext\mysql-connector-java-5.1.23-bin.jar). Click ok and restart IDE.

This should resolve the problem.

Answer 2

That looks like an error coming from a JDBC driver. When the JDBC driver initializes the connection, it sends several commands to the MySQL server, one of which is:

SET OPTION SQL_SELECT_LIMIT=DEFAULT

The problem is that the SET OPTION syntax has been deprecated for some time and is now no longer valid in MySQL 5.6. Here's a relevant bug conversation from MySQL's bug database:

Bug #66659: mysql 5.6.6m9 fails on OPTION SQL_SELECT_LIMIT=DEFAULT

Try upgrading your JDBC MySQL driver. The bug conversation lists some other options in case upgrading the driver is not an option.

Answer 3

I got the same Error when i was dumping mysql table structure file in to DB. Mistake was putting the Syntax for dropping all table before but not checking their existence in the Database. Issue was at " DROP TABLE usermgmt". I removed that code of lines of dropping tables and it proceeded with out any error this time.

Answer 4

If the driver suggestion does not work, check your sql for unprintable characters. I just spent an hour troubleshooting this issue only to discover a hidden u+200b character at the end of my sql statement.

Answer 5

I followed the instructions above and this worked for me!

1. Download latest jar file from here: http://dev.mysql.com/downloads/mirror.php?id=412737 Unzip it Copy jar file "mysql-connector-java-5.1.25-bin.jar" to this folder: C:\Program Files\NetBeans 7.3\ide\modules\ext

2. In Netbeans IDE: Disconnect from database. Click Services. Expand Drivers. Right-click on MySQL and press Customize. Add latest driver Remove previous driver.

3. Re-connect to dabatase within IDE.

Answer 6

I prefixed the name of the table with the database name such as

select * from database_name.table_name;

and it worked perfectly, so most likely there's a name conflict.