How to Fix MySQL Error 1064: SQL Syntax Error

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 N is MySQL's generic parse error. It means the SQL parser could not understand the statement — the syntax is invalid for the MySQL version being used.

Impact

A syntax error aborts the entire statement. In migration scripts, a single syntax error in one statement can halt the entire migration, leaving the schema in a partially-upgraded state. In application code, it surfaces as an unhandled database error if the query is dynamically constructed.

Why the Error Location Is Often Misleading

MySQL's parser reports the position of the first token it could not understand — not necessarily where the mistake is. The problem often appears one or more tokens before the reported position:

ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual ... for the right syntax to use near '' at line 3

near '' (empty) means the parser hit the end of the statement unexpectedly — a missing closing parenthesis or COMMIT from earlier in the statement.

Common Causes

1. Reserved word used as identifier without backtick quoting: column or table names like order, key, status, group, value, interval, rank, row are reserved in MySQL
2. MySQL version difference: syntax valid in MySQL 8.0 (e.g., INVISIBLE indexes, NOWAIT, SKIP LOCKED, CTEs with WITH) fails on MySQL 5.7
3. Missing comma in column list, VALUES list, or SELECT list
4. Unclosed parenthesis, string quote, or backtick
5. Wrong quote character: using 'single quotes' for identifiers instead of \backticks``
6. Using double-dash -- comment style without a trailing space: MySQL requires -- (with a space) for inline comments, or #
7. Dialect mismatch: PostgreSQL syntax (RETURNING, :: cast, $1 placeholders, ILIKE) used against MySQL
8. ORM generating invalid SQL for the connected MySQL version (CTE support, window functions pre-8.0)
9. sql_mode difference between environments — ANSI_QUOTES mode makes "double quotes" into identifiers, not strings
10. Byte-order mark (BOM) at the start of a SQL file from a Windows editor

Troubleshooting and Resolution Steps

1. Read the near '...' portion carefully. The token shown immediately after near ' is where the parser stopped:

   near 'order FROM users WHERE id = 1' at line 1
   

   The word order is a reserved keyword — the parser expected a statement keyword, not a column name.

2. Quote identifiers that conflict with reserved words using backticks:

   -- Bad: "order" is a reserved word
   SELECT id, order FROM shipments;
   
   -- Good: backtick-quoted identifier
   SELECT id, `order` FROM shipments;
   
   -- Common reserved words that need quoting:
   -- order, group, key, value, rank, row, status, interval, type,
   -- condition, limit, offset, primary, index, from, to, end
   

3. Check the MySQL reserved word list for your version:

   SELECT WORD FROM information_schema.KEYWORDS
   WHERE RESERVED = 1
   ORDER BY WORD;
   -- Available in MySQL 5.7.15+
   

4. Find unclosed parentheses or quotes by counting in the statement:

   -- Broken: missing closing paren on the subquery
   SELECT * FROM orders WHERE customer_id IN (
       SELECT id FROM customers WHERE country = 'US'
   ;
   -- Should be:
   SELECT * FROM orders WHERE customer_id IN (
       SELECT id FROM customers WHERE country = 'US'
   );
   

5. Check comment syntax — MySQL needs a space after --:

   -- Bad: no space after dashes
   SELECT 1;--this is a comment
   SELECT 2;-- this is fine
   
   -- Or use # for MySQL-specific line comments:
   SELECT 1; # this is a comment
   

6. Check ANSI_QUOTES mode if double quotes are used:

   SHOW VARIABLES LIKE 'sql_mode';
   

   With ANSI_QUOTES in sql_mode, "string" becomes an identifier, not a string literal. Use single quotes for strings and backticks for identifiers.

7. Test MySQL 8.0-only syntax against your server version:

   -- CTEs (WITH) require MySQL 8.0
   WITH ranked AS (
       SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
       FROM events
   )
   SELECT * FROM ranked WHERE rn = 1;
   -- Fails on MySQL 5.7 with error 1064
   
   -- Window functions require MySQL 8.0
   SELECT id, SUM(amount) OVER (PARTITION BY user_id) FROM payments;
   -- Fails on MySQL 5.7
   

8. Check for dialect-specific syntax from PostgreSQL, SQL Server, or SQLite:

   -- PostgreSQL type cast — invalid in MySQL
   SELECT '2024-01-01'::DATE;   -- use CAST('2024-01-01' AS DATE) instead
   
   -- PostgreSQL RETURNING — invalid in MySQL
   INSERT INTO t (col) VALUES (1) RETURNING id;
   
   -- PostgreSQL $1 placeholders — use ? or :name in MySQL drivers
   
   -- PostgreSQL ILIKE — use LIKE with a case-insensitive collation in MySQL
   

9. Validate dynamically-built SQL by printing it before execution:

   query = f"SELECT * FROM {table_name} WHERE id = {user_id}"
   print("DEBUG SQL:", query)   # print before executing to spot concat issues
   cursor.execute(query)
   

   Never interpolate user input directly into SQL strings — use parameterized queries:

   cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
   

10. Strip the BOM from SQL files that fail on the first line:

    # Check for BOM
    hexdump -C migration.sql | head -1
    # BOM = ef bb bf at offset 0
    
    # Remove BOM
    sed -i '1s/^\xEF\xBB\xBF//' migration.sql
    

11. Test the statement in isolation — paste just the failing statement into a MySQL client and run it directly to confirm whether it is a SQL problem or an application/driver problem:

    mysql -u root -p mydb -e "SELECT * FROM \`order\` LIMIT 1"
    

Additional Information

• SQLSTATE 42000 covers both syntax errors (1064) and access violations (1142). They are distinct error codes with the same state class.
• MySQL 8.0 introduced many new reserved words: BUCKETS, CLONE, COMPONENT, EXCEPT, EXCLUDE, FOLLOWING, GROUPS, JSON_TABLE, LAG, LEAD, NTH_VALUE, NTILE, NULLS, OF, OTHERS, OVER, PERCENT, PERSIST, PERSIST_ONLY, PRECEDING, RANK, RECURSIVE, ROW, ROWS, ROW_NUMBER, TIES, WINDOW. Code that worked on MySQL 5.7 can break on 8.0 if it uses these as unquoted identifiers.
• sql_mode = STRICT_TRANS_TABLES does not affect syntax parsing — it controls data validation. Syntax errors occur before sql_mode is consulted.
• In Hibernate/JPA: ORM frameworks generate SQL based on the database dialect. Using MySQLDialect against MySQL 8.0 when MySQL8Dialect is available can produce 5.7-compatible SQL that lacks 8.0 features — or conversely, generate 8.0 syntax that fails on 5.7.

Frequently Asked Questions

Q: The error says "near '' at line 1" — what does an empty near mean? A: The parser reached the end of input unexpectedly. The most common cause is a missing closing parenthesis or an unterminated string literal earlier in the statement.

Q: My SQL works in MySQL Workbench but fails from the application. Why? A: The application driver may be in a different sql_mode or using a different MySQL server version. Check SELECT @@sql_mode from both the client and Workbench. Also check whether the driver is sending the statement as-is or adding encoding/escaping.

Q: I'm getting error 1064 on a CREATE TABLE statement that looks correct. A: Check: (1) any column name that is a reserved word, (2) the character set or collation name is spelled correctly (utf8mb4_unicode_ci, not utf8mb4-unicode-ci), (3) the engine name is correct (InnoDB, MyISAM), (4) the MySQL version supports the feature (e.g., INVISIBLE columns are MySQL 8.0 only).

Q: My migration framework runs SQL that worked in staging but fails in production. Same MySQL version. Why? A: Check sql_mode — it often differs between environments. Production may have ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, or ANSI_QUOTES while staging does not. Run SELECT @@GLOBAL.sql_mode and SELECT @@SESSION.sql_mode in both environments.

Q: How do I find which line of a long migration script is failing? A: Run the script with the mysql CLI using --verbose mode, which prints each statement before executing. Or use pt-query-digest or a migration framework (Flyway, Liquibase) that reports the failing statement number. Binary splitting (run half the script, then the failing half, then bisect) also works.