| Bug #47586 | In procedure, the comment not shown | ||
|---|---|---|---|
| Submitted: | 24 Sep 2009 9:18 | Modified: | 29 Sep 2009 0:29 |
| Reporter: | ws lee | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0, 5.1, next bzr | OS: | Solaris |
| Assigned to: | CPU Architecture: | Any | |
[24 Sep 2009 9:37]
Peter Laursen
Aren't you using a very old server version? It works fine here on 5.1.39 (and I recall this issue from long time back). Peter (not a MySQL person)
[24 Sep 2009 9:45]
Valeriy Kravchuk
Looks like a duplicate bug #11230. Please, check.
[25 Sep 2009 1:28]
ws lee
To. Peter Thanks, your reply. I also have test at mysql 5.1.39 version but, also in mysql 5.1.39, comments of procedure not shown. Do you use any mysql client?
[25 Sep 2009 2:11]
ws lee
To. Valeriy Kravchuk Thanks, your reply. I checked bug #11230. but, I can't resolve this problem. It doesn't work --comments option about procedure.
[25 Sep 2009 5:28]
Sveta Smirnova
Thank you for the report.
Verified as described. To repeat one should copy-paste statement. With C API everything works fine.
$mysql51 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 2474
Server version: 5.1.40-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> delimiter //
mysql> CREATE PROCEDURE usp_test (id int)
-> BEGIN
-> -- ###########################
-> -- comment test
-> -- ###########################
-> select user();
-> END;
-> //
Query OK, 0 rows affected (0.38 sec)
mysql> show create procedure usp_test;
-> //
+-----------+----------+--------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-----------+----------+--------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| usp_test | | CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_test`(id int)
BEGIN
select user();
END | latin1 | latin1_swedish_ci | utf8_general_ci |
+-----------+----------+--------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.11 sec)
mysql> \q
Bye
[25 Sep 2009 6:40]
Valeriy Kravchuk
Sorry, but for me --comment works as expected with 5.0.85 on Windows at least:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql --comments -uroot -proot -P330
8 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.0.85-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> delimiter //
mysql> CREATE PROCEDURE usp_test (id int)
-> begin
-> -- comment
-> -- #######
-> /* another comment */
-> end;
-> //
Query OK, 0 rows affected (0.14 sec)
mysql> show create procedure usp_test\G
*************************** 1. row ***************************
Procedure: usp_test
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_test`(id int)
begin
-- comment
-- #######
/* another comment */
end
1 row in set (0.00 sec)
[25 Sep 2009 6:44]
Valeriy Kravchuk
Same with 5.1.39 (server and client):
C:\Program Files\MySQL\MySQL Server 5.0\bin>cd "..\..\MySQL Server 5.1"\bin
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql --comments -uroot -proot -P331
0 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.39-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> delimiter //
mysql> CREATE PROCEDURE usp_test (id int)
-> begin
-> -- comment
-> -- #######
-> /* another comment */
-> end;
-> //
Query OK, 0 rows affected (0.19 sec)
mysql> show create procedure usp_test\G
*************************** 1. row ***************************
Procedure: usp_test
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU
TION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_test`(id
int)
begin
-- comment
-- #######
/* another comment */
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.05 sec)
mysql> exit
Bye
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql --version
mysql Ver 14.14 Distrib 5.1.39, for Win32 (ia32)
[25 Sep 2009 6:49]
Valeriy Kravchuk
Same on Linux. Check the difference:
openxs@suse:/home2/openxs/dbs/5.1> 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 1
Server version: 5.1.40 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> delimiter //
mysql> create procedure usp_test (id int)
-> begin
-> -- comment
-> -- #######
-> /* more comments */
-> end;
-> //
Query OK, 0 rows affected (0.07 sec)
mysql> show create procedure usp_test\G
*************************** 1. row ***************************
Procedure: usp_test
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_test`(id int)
begin
end
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
mysql> exit
Bye
openxs@suse:/home2/openxs/dbs/5.1> bin/mysql --comments -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 3
Server version: 5.1.40 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create procedure usp_test2 (id int)
-> begin
-> -- comment
-> -- #######
-> /* more comments */
-> end;
Query OK, 0 rows affected (0.00 sec)
mysql> show create procedure usp_test2\G
*************************** 1. row ***************************
Procedure: usp_test2
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_test2`(id int)
begin
-- comment
-- #######
/* more comments */
end
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
So, this problem is fixed in recent mysql clients and this was never a problem at server level...
[28 Sep 2009 7:18]
ws lee
Why not bug this? In Solaris 5.10, and recent GA mysql 5.1.39 client, comment of procedure is not shown .
[28 Sep 2009 7:25]
Sveta Smirnova
You have to use option --comments to see comments.
[28 Sep 2009 8:10]
ws lee
To. Sveta Smirnova In this time, I have retest with mysql-server 5.1.39 and mysql-client 5.1.39. this case, no problem. but, In mysql-server 5.1.32, when i use only mysql-client5.1.39, comment of server(5.1.32)'s procedure not shown. It seems to has a bug in 5.1.39 older verion.
[28 Sep 2009 8:13]
ws lee
How can I migrate 5.1.32 to 5.1.39 procedure with comment completly?
[28 Sep 2009 8:50]
ws lee
In mysql5.1.39 server, the comments of procedure is not dumped. mysqldump -R -t -d --comments test > test_proc.dmp test_proc.dmp not include comments of procedure.
[28 Sep 2009 9:14]
Valeriy Kravchuk
You have to create procedure while working in mysql commend line client with --comments option. Please, check the results of SHOW CREATE PROCEDURE as I did. If comments are NOT there, mysqldump can not get them.
[28 Sep 2009 9:24]
ws lee
To. Valeriy Kravchuk Thanks for reply. > If comments are NOT > there, mysqldump can not get them. If comments are, can mysqldump get comments of procedure?
[28 Sep 2009 10:44]
Valeriy Kravchuk
No problem. Look:
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql --comments -uroot -proot -P331
0 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.39-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create procedure proc1()
-> begin
-> -- comment
-> /* comment */
-> end;
Query OK, 0 rows affected (0.30 sec)
mysql> show create procedure proc1\G
*************************** 1. row ***************************
Procedure: proc1
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU
TION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`()
begin
-- comment
/* comment */
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.23 sec)
mysql> exit
Bye
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqldump --comments --routines -uro
ot -proot -P3310 test
-- MySQL dump 10.13 Distrib 5.1.39, for Win32 (ia32)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.1.39-community
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Dumping routines for database 'test'
--
/*!50003 DROP PROCEDURE IF EXISTS `proc1` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO
_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `proc
1`()
begin
-- comment
/* comment */
end */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2009-09-28 13:42:13
I've just added --comments to mysqldump command line, as Sveta suggested. See http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html also.
[29 Sep 2009 0:29]
ws lee
To. Valeriy Kravchuk Thanks your reply. I also discovered no problem in mysql 5.1.32(not new version) The problem is likely to create, not with --comments.
Description: In procedure, the comment not shown How to repeat: 1. create the procedure with comment. mysql> delimiter // mysql> CREATE PROCEDURE usp_test (id int) -> BEGIN -> -- ########################### -> -- comment test -> -- ########################### -> select user(); -> END; -> // Query OK, 0 rows affected (0.00 sec) 2. confirm procedure content. mysql> show create procedure usp_test; +-----------+---------------------+--------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +-----------+---------------------+--------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | usp_test | STRICT_TRANS_TABLES | CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_test`(id int) BEGIN select user(); END | latin1 | latin1_swedish_ci | utf8_general_ci | +-----------+---------------------+--------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) Like upper conten, the comment is not shown.