MySQL Bugs: #26302: MySQL server cuts off trailing "*/" from comments in SP/func

Bug #26302	MySQL server cuts off trailing "*/" from comments in SP/func
Submitted:	12 Feb 2007 23:41	Modified:	6 Jul 2007 19:16
Reporter:	Alex Rytov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0.38-BK, 5.0.37	OS:	Linux (Linux, Windows)
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	comments, stored function, stored procedure

Description:
When you define a procedure that ends with a C-style /* ... */ comment just before the terminator ending the proc, the comment gets stored with the procedure body (which is correct), but the closing "*/" gets cut off (which is wrong).  This does not happen in the command-line client as it apparently cuts off the entire comment (which also doesn't seem quite right to me, but that's another story). 

I haven't tried to submit the same proc definition programmatically, so it might be the problem is in the server itself (mySQL 5.1.15 Beta), but it feels more likely that it's in the GUI client (?). 

A proc with a comment cut-off in this way still executes correctly.

How to repeat:
Try this in Windows client (note that I didn't redefine the delimiter here, as there is no ';'s inside the proc body):

create procedure p()
select 1 /* testing */
;

Then 

  SELECT * FROM information_schema.ROUTINES

shows:

...|  create procedure p() select 1 /* testing  |...

Thank you for a bug report. Verified just as described on Windows XP SP2. It is a QB bug, as mysql command line clinet just strip all comments...

This is not a GUI tools bug. The command string sent to the server includes the full comment (via libmysql). It must either be the client lib or the server which cut the closing comment token off. The CLI avoids this problem by simply removing comments, so you cannot use it to verify the bug. You need a separate app that sends SQL to the server.

This is a server bug. Verified just as described with latest 5.0.37 and 5.0.38-BK on Linux:

openxs@suse:~/dbs/5.0> tail var/suse.log
                      1 Init DB     test
070311 12:58:01       2 Connect     root@192.168.0.184 on test
                      2 Query       SELECT @@sql_mode
                      2 Query       SET SESSION sql_mode=''
                      2 Query       SET NAMES utf8
                      2 Init DB     test
                      2 Query       SELECT @@SQL_MODE
                      2 Query       create procedure p2()
select 1 /* testing */
                      2 Quit

So, in the general query log we see proper comment, while in routines table:

openxs@suse:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.38-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from information_schema.routines where routine_name='p2'\G
*************************** 1. row ***************************
     SPECIFIC_NAME: p2
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: p2
      ROUTINE_TYPE: PROCEDURE
    DTD_IDENTIFIER: NULL
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: select 1 /* testing
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE: SQL
  IS_DETERMINISTIC: NO
   SQL_DATA_ACCESS: CONTAINS SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2007-03-11 12:58:01
      LAST_ALTERED: 2007-03-11 12:58:01
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: root@%
1 row in set (0.01 sec)

there is no one.

The root cause is in the parser, see related Bug#25411

Pushed into 5.1.20-beta with Bug#24511

Previous comment should say:

Pushed into 5.1.20-beta with Bug#25411

Noted in 5.1.20 changelog.

Embedded /* ... */ comments were handled incorrectly within the
definitions of stored programs and views, resulting in malformed
definitions. This also affected binary log contents.