How to troubleshoot MySQL Syntax ERROR 1064?

Question

Context

• Ubuntu 22.04
• Attempt to create data base object per https://github.com/curzon01/mqtt2sql

mysql --host localhost -u root -p < mysql.sql

Symptom

user@mysql:~/mqtt2sql$ mysql --host localhost -u root -p < mysql.sql
Enter password: 
ERROR 1064 (42000) at line 94: 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 'IF NOT EXISTS `mqtt_history_view` AS
SELECT
    h.id,
    h.ts AS 'ts',
    m.ts' at line 1

mysql.sql excerpt

A complete listing of the sql script is found here: https://github.com/curzon01/mqtt2sql/blob/master/mysql.sql

The error message indicates line 94 of the sql script:

A forked version of the .sql script returned the same result.

Question

What is the next step to diagnose / troubleshoot the error? Hopefully, the syntax error it is obvious to the experienced eye. Thank you

Answer 1

I'll try to answer your exact question:

What is the next step to diagnose / troubleshoot the error?

Look at the context given in the error:

ERROR 1064 (42000) at line 94: 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 'IF NOT EXISTS mqtt_history_view AS

The error tells you exactly where the parser got confused. It found some words starting with IF NOT EXISTS... and was not expecting those words. That's where your SQL was wrong.

CREATE VIEW IF NOT EXISTS ...
ok ok...    ^ error starts here

Your next step is to look up the reference documentation for the CREATE VIEW statement: https://dev.mysql.com/doc/refman/8.0/en/create-view.html

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]

There are some parts of the syntax that are optional, indicated by square brackets. But notice that IF NOT EXISTS does not appear in any part of this syntax. It is not recognized by MySQL's SQL parser.

You can use CREATE OR REPLACE... which probably accomplishes what you want.

Pro tip: Keep the reference documentation handy. Use it as often as you need. I've been using MySQL since 2001, and I look still things up in the documentation several times per day, whenever I am in doubt of some specific syntax or feature that I don't use often enough to have it memorized.