CODEWITHSUNDEEP
mysqlnetbeansjdbc
Hazem Taha
Hazem Taha

Reputation: 1184

Error code 1064, SQL state 42000: You have an error in your SQL syntax;

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

Upvotes: 27

Views: 98964

Answers (6)

rachid
rachid

Reputation: 2486

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.

Upvotes: 0

D8Sasi
D8Sasi

Reputation: 79

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.

Upvotes: 0

Brice
Brice

Reputation: 366

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.

Upvotes: 1

Fresh
Fresh

Reputation: 111

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.

Upvotes: 11

farmbytes
farmbytes

Reputation: 81

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.

Upvotes: 5

hrunting
hrunting

Reputation: 3947

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.

Upvotes: 30

Related Questions