Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:

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

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.

like image 926
Francesco Marchetti-Stasi Avatar asked Jun 28 '16 11:06

Francesco Marchetti-Stasi


1 Answers

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 1.8.2.4, “'--' 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
# -----------------------------------------------------
like image 94
Magisch Avatar answered Sep 23 '22 02:09

Magisch