MySQL Bugs: #30631: Nested pseudo-comments with DROP TRIGGER

Bug #30631	Nested pseudo-comments with DROP TRIGGER
Submitted:	24 Aug 2007 20:35	Modified:	26 Aug 2010 9:28
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.x >= 5.1.14	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Nested comments like 

/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `trigtest`.`tr1` */ ;

is not parsed correctly (as in 5.028a and 5.0.45).

How to repeat:
select version();
drop database if exists trigtest;
create database trigtest;
create table `trigtest`.`t1` (`blabla` int) ;

DELIMITER $$
DROP TRIGGER /*!50114 IF EXISTS */ `trigtest`.`tr1`$$
CREATE TRIGGER `trigtest`.`tr1` BEFORE DELETE on `trigtest`.`t1`
FOR EACH ROW BEGIN
-- nothing
END$$
DELIMITER ;

/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `trigtest`.`tr1` */ ;

For version 5.1.20 server returns: 
Error Code : 1064
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
(0 ms taken)

For version 5.0.45 only the expected:
Error Code : 1360
Trigger does not exist

Suggested fix:
I too don't have any better idea than a fix ... :-)

Sorry, but I don't see a bug here.

With the script given:

In 5.0.48,
the query
  DROP TRIGGER /*!50114 IF EXISTS */ `trigtest`.`tr1`$$
will be parsed as
  DROP TRIGGER `trigtest`.`tr1`$$
because 50114 means 5.0.114 which is greater than 5.0.48,
and will fail as expected, since the trigger does not exist.

In 5.1.22,
the query
  DROP TRIGGER /*!50114 IF EXISTS */ `trigtest`.`tr1`$$
will pass as expected.

Later, the query:
/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `trigtest`.`tr1` */ ;
fails as expected with a syntax error, since special comments can not be nested.

Closing this report as "not a bug"

To use special comments for both "DROP TRIGGER" and "IF EXISTS" in a query,
you may use one of the following syntaxes :

/*!50003 DROP TRIGGER */ /*!50032 IF EXISTS */ /*!50003 `trigtest`.`tr1` */ ;
/*!50003 DROP TRIGGER */ /*!51014 IF EXISTS */ /*!50003 `trigtest`.`tr1` */ ;
which does not nest special comments.

See related bug#23703: DROP TRIGGER IF EXISTS was introduced in
- 5.0.32 --> /*!50032 */
- 5.1.14 --> /*!51014 */

Actually, by looking closer,

/*!50003 DROP TRIGGER */ /*!50032 IF EXISTS */ /*!50003 `trigtest`.`tr1` */ ;
works properly,
but there is a problem with the following query in 5.1.22:
/*!50003 DROP TRIGGER */ /*!51014 IF EXISTS */ /*!50003 `trigtest`.`tr1` */ ;

Re-opening the bug report, this needs to be investigated further.

OK .. I can understand that 

/*!50003 DROP TRIGGER */ /*!51014 IF EXISTS */ /*!50003 `trigtest`.`tr1` */ ;

is more 'straight' and 'healthy' than

/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `trigtest`.`tr1` */ ;

I think you are right that the nested comment construction is not documented, and no gurantee it will work. But still this 'nested comment' construction seems to work with 5.0.x (and from here I used to it that way) and I am also pretty sure that I have seen DUMPs created by some 'mysqldump' version using that construction.

A consideration more!

You write "...since special comments can not be nested".  Does that mean that 'ordinary' comments can? In my understanding that is implicit with what you told!

according to this

/*!.... /*!.... */... */  -- is not valid
/*.... /*.... */... */    -- but this is?

now what about 

/*.... /*!.... */... */
and
/*!.... /*.... */... */

Not to mention

/*!... this
is
-- just
a comment
*/

Will 'just' be treated conditionally or not?!

... so I think this discussion also reveals a need for more detailed documentation!
 
How can and how cannot (common and conditional) comments be nested, and how will it be parsed?

I do not think it is a silly request as we all from time to time 'comment out' code blocks (when developing, testing etc.). Inside such block there might be other comments! Bascially the behaviour of such code should be predictable!

And the requst is NOT to adjust docs to how code is currently working, but to consider how it SHOULD work and adjust code accordingly if there is something to adjust!

Correction

The syntax for 5.1.14 is in fact /*!50114 */.
The syntax /*!51014 */ means 5.10.14, since 2 digits are reserved for the
minor version number, so that 3.23.02 can be represented as 32302.

When using the correct syntax then, the following queries:

/*!50003 DROP TRIGGER */ /*!50032 IF EXISTS */ /*!50003 `trigtest`.`tr1` */ ;
/*!50003 DROP TRIGGER */ /*!50114 IF EXISTS */ /*!50003 `trigtest`.`tr1` */ ;

are parsed as expected (tested with 5.0.50 and 5.1.23)

As detailed in previous comments, there is no bug in the server behavior:
the result of the scripts and queries provided is as expected.
Closing this report as "Not a Bug".

I take myself the freedom to change to 'feature request'!

the mismatch in version in my post was a typo only of course!

The culprit is that nesting (conditional only?) comments works different in 5.x and 5.x .. and this is not documented at all!

Documetation request:

How can (all kinds of) comments be nested and how will it be parsed!

no .. with hindsight I think more correct is a "S3" in category 'docs' ....

Nested comments are not supported.

See: http://dev.mysql.com/doc/refman/5.0/en/comments.html

This report should have been reopened and marked as a duplicate of http://bugs.mysql.com/bug.php?id=39559

We just had a user reading this one and getting the impression that our SQL-dumps (that are identical to mysqldump in this respect) will not work on 5.1.

.. what I just did now myself!