Francesco Marchetti-Stasi Francesco Marchetti-Stasi - 1 year ago 107
SQL Question

Mysql Error 1064 ("You have an error in your SQL syntax") triggered by SQL comments

I have a series of scripts for creating a schema, with a comment like the following before each instruction:


When I execute the script from mysql on the command line, I get a bunch of errors like the following:

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 1

(actually, I get one error for each comment, in spite of the message always referring to line 1).

To quickly solve my problem I simply removed the comments and the script ran without problems, but I was surprised to see such a behaviour and to be unable to find a relevant question here on stackoverflow. Does anyone have an explanation? Did anyone ever observe such an odd behaviour?

I am running mysql 5.6.30, the default for 5.6 on ubuntu at this time.

Answer Source

From the MySQL Manual:

From a “-- ” sequence to the end of the line. In MySQL, the “-- ” (double-dash) comment style requires the second dash to be followed by at least one whitespace or control character (such as a space, tab, newline, and so on). This syntax differs slightly from standard SQL comment syntax, as discussed in Section, “'--' as the Start of a Comment”.

(Emphasis Mine)

tl;DR Your -- indicating a comment must be followed by at least one whitespace or control character.

Fixed code of yours:

-- -----------------------------------------------------
--  Table TABLE_NAME
-- -----------------------------------------------------

In MySQL You can also use this syntax:

*    Table TABLE_NAME

Or even this:

# -----------------------------------------------------
#   Table TABLE_NAME
# -----------------------------------------------------
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download