Fix: MySQL ERROR 1064 (42000): You have an error in your SQL syntax

The Error

You run a MySQL query and get:

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 VALUES (1, "test")' at line 1

Or variations:

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 'LIMIT 10' at line 3

#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 '' at line 1

MySQL’s parser could not understand your SQL statement. The near '...' part tells you approximately where the syntax error is — look at the text shown and the few characters before it.

Why This Happens

MySQL’s SQL parser follows strict grammar rules. When it encounters something unexpected — a missing keyword, a reserved word used as an identifier, a wrong quote character, or syntax from a different database — it stops and reports the error.

The error message points to where the parser gave up, not necessarily where the mistake is. The actual error is often a few words before the quoted text.

Common causes:

• Reserved word used as a column or table name. order, group, select, table, key, index, status, name, user.
• Wrong quote characters. Using double quotes (") instead of backticks (`) for identifiers.
• Missing comma between column definitions or values.
• Typo in a SQL keyword. INSER INTO instead of INSERT INTO.
• MySQL version mismatch. Using syntax that is not available in your MySQL version (e.g., OVER() window functions before MySQL 8.0).
• Copy-paste from other databases. PostgreSQL, SQL Server, or SQLite syntax that is not valid in MySQL.

Fix 1: Escape Reserved Words with Backticks

The most common cause of error 1064. MySQL has many reserved words that cannot be used as identifiers without escaping:

Broken:

CREATE TABLE order (
    id INT PRIMARY KEY,
    status VARCHAR(50)
);
-- ERROR 1064: near 'order' — 'order' is a reserved word

Fixed — use backticks:

CREATE TABLE `order` (
    id INT PRIMARY KEY,
    `status` VARCHAR(50)
);

Common reserved words that trip people up:

Pro Tip: Avoid using reserved words as table or column names entirely. Use plural nouns for tables (orders, users) and descriptive names for columns (order_status, user_name). This prevents the error across all SQL dialects and makes queries more readable.

Fix 2: Use Correct Quote Characters

MySQL uses three types of quotes, each for a different purpose:

Broken — double quotes for identifiers (not standard MySQL):

SELECT "name" FROM "users";
-- MySQL treats "name" as a string literal, not a column name

Fixed — backticks for identifiers:

SELECT `name` FROM `users`;

Broken — backticks for values:

INSERT INTO users (name) VALUES (`Alice`);
-- ERROR: `Alice` is treated as a column name, not a string

Fixed — single quotes for values:

INSERT INTO users (name) VALUES ('Alice');

Broken — smart quotes from word processors:

SELECT * FROM users WHERE name = 'Alice';  -- Smart quotes!

Fixed — straight single quotes:

SELECT * FROM users WHERE name = 'Alice';

Copy-pasting SQL from Word, Google Docs, Slack, or Notion often introduces smart quotes. Always type quotes directly in your SQL editor.

Fix 3: Fix Missing Commas and Parentheses

Missing comma between columns:

CREATE TABLE users (
    id INT PRIMARY KEY
    name VARCHAR(100)  -- Missing comma after PRIMARY KEY!
    email VARCHAR(200)
);

Fixed:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(200)
);

Missing closing parenthesis:

INSERT INTO users (name, email VALUES ('Alice', '[email protected]');
-- Missing ) after email

Fixed:

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');

Extra comma at the end:

SELECT name, email, FROM users;
-- Extra comma before FROM

Fixed:

SELECT name, email FROM users;

Common Mistake: Adding a trailing comma after the last column in CREATE TABLE, SELECT, or INSERT. This is valid in some programming languages but not in SQL.

Fix 4: Fix Version-Specific Syntax

Some SQL features are only available in certain MySQL versions:

Window functions (MySQL 8.0+):

SELECT name, ROW_NUMBER() OVER (ORDER BY created_at) as row_num
FROM users;
-- ERROR 1064 on MySQL 5.7 — window functions not supported

Common Table Expressions / CTE (MySQL 8.0+):

WITH active_users AS (
    SELECT * FROM users WHERE active = 1
)
SELECT * FROM active_users;
-- ERROR 1064 on MySQL 5.7

CHECK constraints (MySQL 8.0.16+):

CREATE TABLE products (
    price DECIMAL(10,2) CHECK (price > 0)
);
-- Silently ignored before MySQL 8.0.16

Check your MySQL version:

SELECT VERSION();

If you are on MySQL 5.7 and need these features, either upgrade to MySQL 8.0 or rewrite the query using supported syntax.

Fix 5: Fix INSERT and UPDATE Syntax

Missing VALUES keyword:

INSERT INTO users (name, email) ('Alice', '[email protected]');
-- Missing VALUES

Fixed:

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');

SET syntax for UPDATE:

UPDATE users SET name = 'Alice', WHERE id = 1;
-- Extra comma before WHERE

Fixed:

UPDATE users SET name = 'Alice' WHERE id = 1;

Wrong multi-row INSERT:

INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie',);
-- Trailing comma after last row

Fixed:

INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');

If the INSERT succeeds but violates a constraint, see Fix: PostgreSQL duplicate key violates unique constraint (the concept is similar for MySQL unique key violations).

Fix 6: Fix Escaped Characters in Strings

Unescaped single quote in a string:

INSERT INTO posts (title) VALUES ('It's a test');
-- ERROR: the parser sees 'It' as the string, then 's a test' is unexpected

Fixed — escape with backslash:

INSERT INTO posts (title) VALUES ('It\'s a test');

Fixed — double the single quote:

INSERT INTO posts (title) VALUES ('It''s a test');

Fixed — use prepared statements (best for application code):

cursor.execute("INSERT INTO posts (title) VALUES (%s)", ("It's a test",))

Prepared statements handle escaping automatically and prevent SQL injection. For related security best practices, see Fix: MySQL access denied for user.

Fix 7: Fix Imported or Generated SQL

If the error comes from a SQL dump or migration file:

Check the file encoding. UTF-8 BOM or other encoding issues can cause invisible characters:

file dump.sql
# Should show: ASCII text or UTF-8 Unicode text

Check for database-specific syntax. If the dump came from PostgreSQL or SQL Server:

-- PostgreSQL syntax (not valid in MySQL):
CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- MySQL uses AUTO_INCREMENT
    data JSONB              -- MySQL uses JSON (no JSONB)
);

-- MySQL equivalent:
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);

Check for delimiter issues in stored procedures:

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

Without the DELIMITER change, the ; inside the procedure body terminates the statement prematurely.

Fix 8: Debug the Error Location

The near '...' text in the error message shows where the parser stopped. The actual error is usually just before that text:

ERROR 1064: ... for the right syntax to use near 'FROM users' at line 2

This means the error is just before FROM — check the column list or expression before FROM.

Tips for finding the error:

1. Read the near '...' text.
2. Look at the few words before that text in your query.
3. Check for missing commas, parentheses, or keywords at that point.
4. If near '' (empty string), the error is at the very end of the statement — usually a missing closing parenthesis or semicolon.

Break the query into parts to isolate the error:

-- Start with the simplest version:
SELECT * FROM users;

-- Add clauses one by one:
SELECT * FROM users WHERE active = 1;
SELECT * FROM users WHERE active = 1 ORDER BY name;
SELECT * FROM users WHERE active = 1 ORDER BY name LIMIT 10;

The first version that fails tells you which clause has the error.

Still Not Working?

Check for invisible characters. Copy-pasting SQL from web pages can introduce zero-width spaces or non-breaking spaces:

cat -A query.sql | head -5

Look for unexpected M-BM- or ^M characters.

Check the SQL mode. MySQL’s sql_mode setting affects what syntax is valid:

SELECT @@sql_mode;

ANSI_QUOTES mode makes double quotes work as identifier quotes (like PostgreSQL). NO_BACKSLASH_ESCAPES disables backslash escaping.

Check for client-specific issues. Some MySQL clients (phpMyAdmin, MySQL Workbench, DBeaver) add their own delimiters or modify queries. Try running the query directly in the mysql command-line client.

Use an SQL formatter. Paste your query into an SQL formatter to highlight structural issues. Mismatched parentheses and missing keywords become obvious with proper formatting.

If the table itself does not exist rather than having a syntax error, see Fix: MySQL table doesn’t exist.