MySQL error 1064: how to read it and fix the SQL syntax

Short answer: MySQL error 1064 (ER_PARSE_ERROR, SQLSTATE 42000) means the server could not parse your statement. Parsing stopped at the text after near, so the mistake sits just before it. Usual causes: a reserved word as a name, a missing or extra comma, wrong quotes, an empty value from code, or syntax your server version lacks.

mysql> CREATE TABLE order (id INT, total DECIMAL(10,2));
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, total DECIMAL(10,2))' at line 1

MariaDB prints the same error number and nearly the same text, with “MariaDB server version” in the middle. phpMyAdmin shows it as #1064 - You have an error in your SQL syntax, PHP’s PDO as SQLSTATE[42000]: Syntax error or access violation: 1064, and Node’s mysql2 driver as code: 'ER_PARSE_ERROR'. All of them are the same failure: the statement never ran, nothing was changed, and the fix is in the SQL text itself. The examples below follow the MySQL 8.0 and 8.4 Reference Manuals and the MariaDB documentation.

How to read “near … at line N”

The quoted part starts at the first token the parser could not fit into any valid statement and runs to the end of the statement, cut short on long queries. The mistake is almost never inside that quote: it is the word right before it, or the end of the line above it. In the example above the parser stopped at order, because ORDER is a reserved word and cannot start a table name.

at line N at the end of the message counts lines inside the one statement, not inside your file. When you import a file with the command-line client, it adds its own position in the file after the error code, and that is the number to look up:

$ mysql shop < dump.sql
ERROR 1064 (42000) at line 1843: 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
'TYPE=MyISAM' at line 9

$ sed -n '1843,1852p' dump.sql | cut -c1-200     # the statement, long lines shortened

Is it really a syntax error?

SQLSTATE 42000 is a whole class (“syntax error or access rule violation”), so access errors such as 1044 share it. The number is what identifies the problem. A statement that parses but names something wrong gets a different error:

Cause 1: a reserved word used as a name

ORDER, GROUP, KEY, DESC, INDEX, RANGE, READ and INTERVAL are reserved in both MySQL and MariaDB. They sound like perfect column and table names, which is exactly why they cause so many 1064s:

-- fails: ORDER is reserved
CREATE TABLE order (id INT, total DECIMAL(10,2));
SELECT id, desc FROM products;

-- works: quote the identifier with backticks
CREATE TABLE `order` (id INT, total DECIMAL(10,2));
SELECT id, `desc` FROM products;

-- also works: a qualified name, the word after the dot is always an identifier
SELECT p.desc FROM products AS p;

Backticks around an identifier always work in MySQL and MariaDB. Renaming the column (orders, description) is the cleaner long-term fix, because every query anyone writes against the table will otherwise need the backticks too.

Words that became reserved in MySQL 8.0

MySQL 8.0 added window functions and common table expressions, and with them a set of new reserved words, per the 8.0 manual’s keyword list: RANK, GROUPS, ROW_NUMBER, WINDOW (all from 8.0.2), RECURSIVE, OF, CUBE, GROUPING, FUNCTION (8.0.1), SYSTEM (8.0.3), LATERAL (8.0.14), plus LEAD, LAG, DENSE_RANK, NTILE, EMPTY, EXCEPT and INTERSECT. MySQL 8.4 adds QUALIFY and TABLESAMPLE. MariaDB does not reserve RANK or GROUPS.

That produces a classic migration trap. A dump moves over without complaint, because mysqldump and mariadb-dump put backticks around every name. Then the application’s own queries and migration scripts, which do not quote, start to fail:

mysql> CREATE TABLE scores (player VARCHAR(50), rank INT);
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
'rank INT)' at line 1

Quote the column in the application’s queries or rename it. On MySQL 8.0.13 and later the server can tell you whether a word is reserved:

SELECT WORD, RESERVED FROM information_schema.KEYWORDS WHERE WORD IN ('RANK', 'STATUS', 'GROUPS');

Cause 2: typos and missing or extra commas

-- a misspelt keyword: near 'SELCT * FROM users' at line 1
SELCT * FROM users;

-- missing comma: near 'name VARCHAR(50))' at line 1
CREATE TABLE users (id INT name VARCHAR(50));

-- trailing comma: near ')' at line 1
CREATE TABLE users (id INT, name VARCHAR(50),);

-- FORM for FROM: MySQL reads FORM as an alias for id, then stops at 'users'
SELECT id FORM users;

The last one shows why you look before the near text: the error quotes users, which is spelt correctly, because the parser had already accepted FORM as a column alias. A missing comma in a SELECT list can even pass without any error: SELECT id email FROM users is valid SQL that returns one column, id, labelled email. If a query “works” but a column is missing, look for that.

Cause 3: the wrong kind of quotes

MySQL and MariaDB have two kinds of quoting, and swapping them is a reliable source of 1064:

• Backticks `name` are for identifiers: tables, columns, databases.
• Single quotes 'text' are for string and date values.
• Double quotes are string quotes by default, and identifier quotes only when sql_mode contains ANSI_QUOTES. SQL copied from PostgreSQL, which uses "table", therefore fails here.

-- single quotes around a table name: near ''users' (id INT)' at line 1
CREATE TABLE 'users' (id INT);
CREATE TABLE `users` (id INT);            -- fixed

-- an apostrophe ends the string early: near 'Brien')' at line 1
INSERT INTO customers (name) VALUES ('O'Brien');
INSERT INTO customers (name) VALUES ('O''Brien');   -- fixed: double it
INSERT INTO customers (name) VALUES ('O\'Brien');   -- or escape it

Curly quotes pasted from a word processor, a chat app or a blog are not quotes to MySQL at all. Depending on where they land you get 1064 or a 1054 “Unknown column”, so retype every quote by hand when SQL came from a formatted document.

Cause 4: a value missing from code-built SQL

Most 1064s in a running application come from SQL glued together from strings, where one variable turned out empty or a space went missing. The statement the server receives is not the one you think you wrote:

-- $email was empty: near 'WHERE id = 7' at line 1
UPDATE users SET email =  WHERE id = 7;

-- $id was empty: near '' at line 1
SELECT * FROM users WHERE id = ;

-- "SELECT * FROM users" . "WHERE id = 7", no space: near '= 7' at line 1
SELECT * FROM usersWHERE id = 7;

-- PDO with emulated prepares bound LIMIT as a string: near ''10'' at line 1
SELECT * FROM posts ORDER BY id DESC LIMIT '10';

The lasting fix is prepared statements with placeholders instead of string concatenation, with numbers bound as numbers. In PHP that means $stmt->bindValue(':limit', $limit, PDO::PARAM_INT) for LIMIT, or turning off PDO::ATTR_EMULATE_PREPARES. Placeholders also close the SQL injection hole that string-built queries open. To debug, log the final SQL string just before it is executed and run that exact text in the mysql or mariadb client.

Cause 5: MySQL and MariaDB syntax differences

MariaDB forked from MySQL in 2009, and both have added syntax the other does not accept. Code written against one breaks on the other with 1064, as does SQL written for a newer release of the same server:

The MySQL 8 user statements are the ones that catch most people, because older tutorials still show the one-line GRANT ... IDENTIFIED BY. On MySQL 8.0 and 8.4 it is two statements:

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

To see which server and version you are really talking to (the client can be a different version from the server it connects to):

SELECT VERSION();
-- e.g. 10.11.x-MariaDB-0+deb12u1 on Debian 12, 8.0.x-0ubuntu0.24.04.x for MySQL on Ubuntu 24.04

Cause 6: a dump from another server version

Importing a dump made on a different server is where 1064 and its relatives come in bunches. What to check, in the order it usually bites:

• ERROR at line 1: Unknown command '\-'. Not a 1064, but it stops the same import. Since May 2024, mariadb-dump writes /*M!999999\- enable the sandbox mode */ as the first line of every dump, and the MySQL client and older MariaDB clients do not understand it. Drop the line: tail -n +2 dump.sql | mysql shop.
• TYPE=MyISAM or TYPE=InnoDB in a very old dump: TYPE was replaced by ENGINE long ago and current servers reject it with 1064. sed -i 's/) TYPE=/) ENGINE=/' dump.sql fixes it.
• MariaDB-only features in the schema, such as CREATE SEQUENCE or WITH SYSTEM VERSIONING, fail on MySQL. They have no direct MySQL equivalent, so the table definition has to be rewritten by hand.
• ERROR 1273: Unknown collation: 'utf8mb4_0900_ai_ci' is the MySQL 8 default collation arriving on a MariaDB server that does not know it. Not a syntax error; replace it with a collation the target has, such as utf8mb4_unicode_ci.

Version-specific comments such as /*!80016 ... */ in a MySQL dump are not the problem: a server older than the number inside simply skips them. For a large dump, let the client continue past errors once to collect them all, on a scratch database, never on the live one:

mysql --force scratch_db < dump.sql 2> import-errors.txt
grep -c ERROR import-errors.txt

Cause 7: DELIMITER in stored procedures and triggers

A procedure, function or trigger body contains semicolons. The mysql and mariadb clients send a statement to the server as soon as they read a ;, so without help they cut the definition off at the first one:

mysql> CREATE PROCEDURE count_users()
    -> BEGIN
    ->   SELECT COUNT(*) FROM users;
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 '' at line 3

near '' means the server got a BEGIN with no END. Change the client’s delimiter for the duration of the definition, then set it back:

DELIMITER $$
CREATE PROCEDURE count_users()
BEGIN
  SELECT COUNT(*) FROM users;
END$$
DELIMITER ;

DELIMITER is a command of the client, not SQL, and the server never sees it. That has two consequences. phpMyAdmin’s SQL tab has a Delimiter box under the query field: set $$ there and leave the DELIMITER lines out. And in application code (PDO, mysqli, a migration tool), never send DELIMITER at all: one API call sends one statement, so pass the whole CREATE PROCEDURE ... END as a single string. Sending DELIMITER $$ to the server produces a 1064 of its own.

A method that finds any 1064

• Get the exact statement the server received, not the source code that builds it. Log it right before execution.
• Look at the token before the near text, and at the end of the line before it for a missing comma or parenthesis.
• Check every name against the reserved word list of your server version, and quote with backticks when in doubt.
• Run SELECT VERSION() and read the syntax for that version in the manual; the error text itself says so for a reason.
• Cut the statement down clause by clause until it parses. The last clause you removed holds the error.

On your own server

When you run the database yourself on a VPS, you also choose which server and version the application meets, and that decides half of the cases above. Debian ships MariaDB and Ubuntu 24.04 offers MySQL 8.0 as well; installing MariaDB or MySQL on Ubuntu and Debian covers which to pick for an application that names one of them. Use the same server family and major version in development and production, and test a dump on a scratch database before a migration. Listing users and databases shows how to check what the import created, and phpMyAdmin gives you the same SQL tab with its Delimiter box on your own server. On a VMHeaven Linux VPS you have root and pick the distribution at order time, so the database server is your choice from the first boot.

Frequently asked