CODEWITHSUNDEEP
mysqlmysql-error-1064
Francesco Marchetti-Stasi
Francesco Marchetti-Stasi

Reputation: 533

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.

Upvotes: 6

Views: 2622

Answers (4)

Magisch
Magisch

Reputation: 7352

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
# -----------------------------------------------------

Upvotes: 10

Riples
Riples

Reputation: 1157

Personally, I only use the two dashes -- when commenting a single line. When working with block comments, I tend to use the following format:

/**
 * Table TABLE_NAME
 *
 */

Upvotes: 1

Thinkeye
Thinkeye

Reputation: 928

From: http://dev.mysql.com/doc/refman/5.7/en/comments.html The space after double dash i.e. "-- " is part of the comment identification! Rationale behind MySQL decision: is here http://dev.mysql.com/doc/refman/5.7/en/comments.html

The space is required to prevent problems with automatically generated SQL queries that use constructs such as the following, where we automatically insert the value of the payment for payment:

UPDATE account SET credit=credit-payment
UPDATE account SET credit=credit--1

Upvotes: 0

John Conde
John Conde

Reputation: 219804

You need a space after two dashes to indicate a comment. Without it it is just a string:

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

Upvotes: 3

Related Questions