Bug #75392 Multiline comments treated inconsistently
Submitted: 2 Jan 2015 14:45 Modified: 19 Jan 2015 16:03
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:any - case from 5.6.22 OS:Any
Assigned to: CPU Architecture:Any

[2 Jan 2015 14:45] Peter Laursen
Description:
When a comment in a Stored Routine is created with multiple lines SHOW CREATE and SELECT FROM I_S. ROUTINES format the linebreak differently. In SHOW CREATE thelinebreaks are formatted as the string "\n" (or "/" dependent on platform).  In SELECT FROM I_S. ROUTINES a true 'linefeed' (ASCII [LF]) character is returned. 

I have not checked column- and table -level comments etc.

How to repeat:
It is reproducible with GUI cliets and even command-line

Microsoft Windows [version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. Alle rettigheder forbeholdes.

C:\Users\Peter>cd \program files\mysql\mysql server 5.6\bin

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -p --port=3310
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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> use test;
Database changed
mysql> DELIMITER $$
mysql> CREATE PROCEDURE testp()
    -> COMMENT '
    '>     this
    '>     does
    '>     nothing'
    ->     BEGIN
    ->  -- this
    ->  -- does
    ->  -- nothing
    ->     END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> show create procedure testp;
+-----------+----------------------------------------------------------------+--
--------------------------------------------------------------------------------
------------------------------------------+----------------------+--------------
--------+--------------------+
| Procedure | sql_mode                                                       | C
reate Procedure
                                          | character_set_client | collation_con
nection | Database Collation |
+-----------+----------------------------------------------------------------+--
--------------------------------------------------------------------------------
------------------------------------------+----------------------+--------------
--------+--------------------+
| testp     | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | C
REATE DEFINER=`root`@`localhost` PROCEDURE `testp`()
    COMMENT '\n    this\n    does \n    nothing'
BEGIN

    END | cp850                | cp850_general_ci     | utf8_general_ci    |
+-----------+----------------------------------------------------------------+--
--------------------------------------------------------------------------------
------------------------------------------+----------------------+--------------
--------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT `SPECIFIC_NAME`, ROUTINE_COMMENT FROM `information_schema`.`ROUTINES` WHERE SPECIFIC_NAME = 'testp';
+---------------+---------------------------------+
| SPECIFIC_NAME | ROUTINE_COMMENT                 |
+---------------+---------------------------------+
| testp         |
    this
    does
    nothing |
+---------------+---------------------------------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
Do things consistently! 

I'd prefer always to return *visible linebreaks* (with LF character) as it was defined. In particular for routines you may want to have a rather long descriptive comment.

(on a ide-note: also note the alignment issue in the client. The linebreak before 'this', 'does' and 'nothing' causes display to happen in first column of the result set and not the second column where it rightly belongs)
[2 Jan 2015 14:54] Peter Laursen
BTW: isn't "\n" odd in any case?

I can understand the compliance with "/n" in C - even though I think it is completely misplaced here. The comment is *a string* simply.
[2 Jan 2015 15:21] Peter Laursen
The alignment issue is a non-issue, I realize.

The client cannot handle display of multiline strings. You will have to use "\G" and not ";" as statement separator/delimiter.
[2 Jan 2015 15:26] Peter Laursen
mysql> SELECT `SPECIFIC_NAME`, ROUTINE_COMMENT FROM `information_schema`.`ROUTIN
ES` WHERE SPECIFIC_NAME = 'testp'\G
*************************** 1. row ***************************
  SPECIFIC_NAME: testp
ROUTINE_COMMENT:
    this
    does
    nothing
1 row in set (0.01 sec)

(I think still rather bad formatted!)
[3 Jan 2015 20:51] Peter Laursen
I was wondering if the representation of linebreaks as "\n"ยด|"/n" is due to the historical linkage of MySQL with PHP somehow? 

Anyway an [LF] character is neither!
[19 Jan 2015 15:59] Sinisa Milivojevic
Tested and it truly behaves as described.

Fully verified, but it is not considered to have a serious impact.
[19 Jan 2015 16:03] Peter Laursen
Nothing seems to matter then, right?  

This was a very disappointing reply.