MySQL Bugs: #65602: in-line comment before DELIMITER yields syntax error

Bug #65602	in-line comment before DELIMITER yields syntax error
Submitted:	13 Jun 2012 16:20	Modified:	18 Jun 2012 15:41
Reporter:	Charles Lewis	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.5.25	OS:	Linux (CentOS 6.2)
Assigned to:		CPU Architecture:	Any

Description:
When a c-style /*comment*/ is before DELIMITER, a syntax error is generated.

How to repeat:
This simple SQL:

/* hi */
DELIMITER $$
DROP TRIGGER IF EXISTS `sampletrigger` $$

Causes this syntax error:

ERROR 1064 (42000) at line 1: 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 'DELIMITER $$DROP TRIGGER IF EXISTS `sampletrigger` $$' at line 2

Simply removing the in-line c-style comment eliminates the error.

If you have this problem in mysql command line client (as I assume), can you copy/paste the entire session, like this:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.5.26-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> /* hi */
mysql> DELIMITER $$
mysql> DROP TRIGGER IF EXISTS `sampletrigger` $$
Query OK, 0 rows affected, 1 warning (0.02 sec)

As you can see from above I am not able to repeat the error.

Looks like DELIMITER isn't being honored... I had to use a semi-colon to get the error.  Is there a configuration setting to cause DELIMITER to work?  Thanks for your help.

Here's the mysql command-line session:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 237
Server version: 5.5.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> /* hi */
    -> DELIMITER $$
    -> DROP TRIGGER IF EXISTS `sampletrigger` $$
    -> ;
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 'DELIMITER $$DROP TRIGGER IF EXISTS `sampletrigger` $$' at line 2
mysql>

Without the in-line comment, it works:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 238
Server version: 5.5.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DELIMITER $$
mysql> DROP TRIGGER IF EXISTS `sampletrigger` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

Looks like the problem may be related to "end of lines" used in your case. Please, paste the problematic sequence of commands into a file and upload it to this bug report.

SQL that causes syntax error due to in-line comment at top before DELIMITER (works without comment)

Attachment: junk.sql (application/octet-stream, text), 64 bytes.

Thank you for the feedback.

Which shell do you use? Do you use official MySQL package or compiled client yourself? If you use package, please, indicate exact package name (file you downloaded), if you built yourself provide configuration options used.

I'm using the latest GA from the MySQL website.  I downloaded and installed these:
MySQL-client-5.5.25-1.el6.x86_64.rpm
MySQL-server-5.5.25-1.el6.x86_64.rpm
MySQL-shared-compat-5.5.25-1.el6.x86_64.rpm
MySQL-devel-5.5.25-1.el6.x86_64.rpm
MySQL-shared-5.5.25-1.el6.x86_64.rpm

The shell is bash.

Some more information that might be useful:  After a fresh HD format and load of CentOS 6.2 (64-bit), the MySQL version is 5.1.52, and this problem is evident.  A "yum update" results in a MySQL version of 5.1.61, which still has this problem.  That's when I decided to upgrade to the latest MySQL GA, in an attempt to upgrade past this issue.  I removed MySQL 5.1.61 with the command:  "yum remove mysql mysql-devel mysql-server mysql-libs".  Then I installed the .rpm files listed above with the command:  "rpm -Uvh *" (where '*' is just those five .rpm files shown above).  When I found this problem still exists in MySQL 5.5.25, I reported this as a bug here.  The older 5.0.67 version of MySQL does not have this problem.  I have been through this process a few times, with the same results each time, so it appears to be very reproducible.

Thanks for your help.

Thank you for the feedback.

I can not repeat issue with this package as well.

Please check if you don't start mysql command line client with option --comments: http://dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html#option_mysql_comments In this case described behavior is expected.

With the --comments flag for mysql, the syntax error happens, but, without it, it does not.  I thought that flag was only going to preserve comments, not cause problems with DELIMITER.  I did not expect that behavior, but, if that's what I should expect, then so be it.  Also, I did not realize I was giving the --comments flag to mysql, or I would have mentioned that earlier.  It turns out that part of the load script I am using is adding an alias for mysql to be "mysql --comments".

If you are sure that preserving comments with the --comments flag to the mysql command is expected to yield a syntax error when an in-line comment preceeds DELIMITER, then, I guess this is not actually a bug and can be closed.

Thanks for all of your help.

Thank you for the feedback.

Option --comments tells mysql to not ignore comments, so treating them as SQL statement, which is invalid in your case, is expected. This is not a bug.