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