MySQL "ERROR 1064: You have an error in your SQL syntax": how to read it and fix it

1. Reserved words used as names

MySQL 8.0 added new reserved words, so a query that worked for years can break after an upgrade. Common ones: rank, groups, row, rows, system, function, lead, lag, window, over, recursive. Older ones are order, group, key, index, desc, from, to.

SELECT rank FROM players;       -- ERROR 1064
SELECT `rank` FROM players;     -- works

Backticks work in every statement. To avoid the problem altogether, choose names that aren’t reserved words (player_rank), and have your ORM quote identifiers.

2. Quotes

MySQL has three kinds, and they aren’t interchangeable:

Two classic mistakes, an apostrophe inside a string and “smart” quotes pasted from a document:

INSERT INTO notes (body) VALUES ('it's broken');     -- ERROR 1064, the string ends at it'
INSERT INTO notes (body) VALUES ('it''s fine');      -- works

‘text’ with curly quotes also gives ERROR 1064: retype them. In application code, never build SQL by joining strings: use parameters. It avoids this error, and SQL injection too.

3. Commas and brackets

SELECT id, name, FROM users;              -- comma before FROM
SELECT id name email FROM users;          -- missing comma: "name" is read as an alias
CREATE TABLE t (id INT, name TEXT,);      -- comma before )

The second one is a good example of the rule: id name is valid (an alias), so MySQL only complains at email, one word later than the real mistake.

The empty near '' means the statement ended while MySQL still expected something:

ERROR 1064 (42000): … near '' at line 1

Look for a missing ) or a closing quote.

4. Syntax from another database

Each database has its own dialect. These are the usual travellers:

The SQL Dialect Reference shows the same statement in MySQL, MariaDB, PostgreSQL, SQL Server, Oracle and SQLite, side by side.

5. Syntax from an older MySQL

Tutorials and scripts from years ago often use syntax that has since been removed. The most common by far is creating a user inside GRANT, which MySQL 8.0 no longer accepts:

-- ERROR 1064 on MySQL 8.0 and later
GRANT ALL PRIVILEGES ON shop.* TO 'app'@'%' IDENTIFIED BY 'secret';

Do it in two steps:

CREATE USER 'app'@'%' IDENTIFIED BY 'a-long-password';
GRANT ALL PRIVILEGES ON shop.* TO 'app'@'%';

Also from old dumps: TYPE=InnoDB instead of ENGINE=InnoDB. MariaDB still accepts some of what MySQL has removed, including GRANT … IDENTIFIED BY, so a script can work on one and fail on the other.

6. Procedures and triggers need DELIMITER

In the mysql client, each ; ends the statement, including the ones inside a procedure’s body, so MySQL sees half a procedure:

DELIMITER //
CREATE PROCEDURE add_one(IN x INT)
BEGIN
  SELECT x + 1;
END //
DELIMITER ;

DELIMITER is a command of the client, not SQL. Don’t send it from application code.