🚨 MySQL Error Guide: 'You have an error in your SQL syntax' — Fix ERROR 1064

Overview

ERROR 1064 is the parser’s way of saying it could not understand the statement. The message always points at the token near where parsing failed:

ERROR 1064 (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 'order (id INT PRIMARY KEY)' at line 1

The single most important habit with 1064 is reading the text after near: MySQL prints the point where it gave up, which is usually just past the real mistake. The (42000) is the SQLSTATE for a syntax/access-rule violation.

Symptoms

• CREATE, INSERT, SELECT, or ALTER statements rejected immediately, before touching any data.
• The near '...' fragment names a column or keyword that “looks fine.”
• A statement that runs on one server version fails on another (older or newer).
• A query built by string concatenation in application code fails, often with the near fragment ending abruptly (a truncated or empty value).
• Copy-pasted SQL from a document fails with an invisible character near the start.

Common Root Causes

• Reserved words used as identifiers — a column or table named order, rank, groups, system, or key without backtick quoting.
• Version-specific syntax — features like CREATE TABLE ... (data JSON), window functions, or CTEs used against an older MySQL/MariaDB that does not parse them.
• Missing or mismatched quotes/parentheses — an unterminated string, a stray comma before ), or a missing comma between columns.
• Trailing comma before a closing parenthesis in a column or VALUES list.
• Application string-building bugs — an empty variable interpolated into the query, producing WHERE id = with nothing after it.
• Hidden characters — a non-breaking space, smart quote, or BOM pasted from a rich-text source.
• Wrong delimiter — running a stored routine body with ; inside it in a client that has not switched DELIMITER.

Diagnostic Workflow

Start by isolating the exact statement and reading the token after near. Reproduce it directly in the client so the application layer is out of the picture:

-- Paste the failing statement verbatim and run it in the mysql CLI.
CREATE TABLE `order` (id INT PRIMARY KEY);   -- backticks fix a reserved word

Check whether the identifier is a reserved word for your version:

SELECT word, reserved
FROM information_schema.KEYWORDS
WHERE word = 'ORDER';

Confirm the server version, since syntax support differs:

SELECT VERSION();

If the statement is generated by code, log the final SQL string sent to the server (after all interpolation) and inspect it — the fault is almost always an empty or malformed interpolated value. To catch hidden characters, dump the bytes:

printf '%s' "$STATEMENT" | hexdump -C | head
# look for c2 a0 (nbsp), e2 80 9c/9d (smart quotes), ef bb bf (BOM)

For stored routines, verify the client set a distinct delimiter before the CREATE PROCEDURE:

DELIMITER //
CREATE PROCEDURE p() BEGIN SELECT 1; END //
DELIMITER ;

Example Root Cause Analysis

A deployment migration failed in CI with:

ERROR 1064 (42000): ... near 'rank INT NOT NULL, name VARCHAR(100))' at line 2

Reading after near, parsing stopped at rank. rank became a reserved word when window functions were added, so the migration that worked on the team’s older local MySQL failed on the newer CI server. SELECT VERSION() confirmed CI ran 8.0 while laptops ran 5.7. Checking information_schema.KEYWORDS showed RANK reserved. The fix was to backtick-quote the column (CREATE TABLE leaderboard (`rank` INT NOT NULL, ...)), and the durable fix was to stop using reserved words as column names. The near fragment had pointed one token past the real culprit, as it almost always does.

Prevention Best Practices

• Quote identifiers with backticks whenever a name could be a keyword, and prefer non-reserved names in new schemas.
• Develop and test against the same MySQL/MariaDB version as production to catch version-specific syntax before deploy.
• Use parameterized/prepared statements instead of string concatenation so empty values raise clear binding errors, not 1064.
• Lint and validate generated SQL in CI, and log the final rendered statement on failure.
• Paste SQL through a plain-text editor, and keep a check for hidden characters in migration files.
• Set DELIMITER explicitly when defining triggers, functions, or procedures.

Quick Command Reference

SELECT VERSION();                                   -- confirm server version
SELECT word FROM information_schema.KEYWORDS
  WHERE reserved = 1 AND word = 'YOURNAME';         -- is it reserved?
CREATE TABLE `order` (id INT PRIMARY KEY);          -- backtick a reserved word

printf '%s' "$STATEMENT" | hexdump -C | head        -- hunt hidden characters

Conclusion

ERROR 1064 is a parser failure, not a data error, and it is almost always solved by two moves: read the token right after near (the real fault is just before it), and rule out reserved words, version-specific syntax, and hidden or empty interpolated values. Reproduce the exact statement in the CLI, confirm the version, and switch to parameterized statements so string-building bugs stop generating syntax errors in the first place.