The MySql 1064 error

Error #1064 - 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 '%s'

indicates a syntax error in a query or in a command submitted to MySQL.

The error message shows a fragment of your query. The fragment starts with the first character of your query that MySQL did not understand. So, it's often helpful to look at the part of your query immediately before the fragment.

Troubleshooting hints

• Is the first thing in the message a reserved word in MySQL? It can cause an error when you name something with a word like WHERE or ORDER. SELECT WHERE, ORDER FROM TABLE WHERE ORDER=WHERE, for example.
• Is the character of your query right before the message some kind of punctuation? If so, it might be the wrong punctuation. SELECT a, b, FROM c WHERE a=b has an extra comma after b, so MySQL shows FROM c... as the location of the error.
• Should some text string be wrapped correctly in quotes earlier in your query? Did you forget to close a quote? SELECT a FROM c WHERE a='title ORDER BY a complains starting at the beginning of the unclosed string title.

Example of interpreting a 1064 message.

For example, take this bit of incorrect DDL:

CREATE TABLE test_table (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    txt VARCHAR(255) NULL DEFAULT NULL COLLATOR 'utf8mb4_general_ci',
    PRIMARY KEY (id) USING BTREE
);

When you try to run it you get this error message.

SQL Error (1064): 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 ''utf8mb4_general_ci', PRIMARY KEY (id) USING BTREE )' at line 3

So, the fragment MySQL couldn't get was this ...

                                                'utf8mb4_general_ci',
    PRIMARY KEY (id) USING BTREE
);

and the stuff immediately before the fragment in the message was this

CREATE TABLE test_table (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    txt VARCHAR(255) NULL DEFAULT NULL COLLATOR 

COLLATOR? No, that's not right. It should be COLLATION. Fix the problem.

See how this goes? Get the statement fragment from the 1064 message, then back up a little bit from where the fragment started. You'll probably find your error.

Other SOers ... don't hesitate to add your hints!

Related tag

• mysql