MySQL Bugs: #13823: backup truncates COMMENT of PROCEDURE and FUNCTION objects to 64 chars

Bug #13823	backup truncates COMMENT of PROCEDURE and FUNCTION objects to 64 chars
Submitted:	6 Oct 2005 22:47	Modified:	12 Oct 2005 13:24
Reporter:	Roland Bouman	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	Admin 1.1.3 / MySQL 5.0.13	OS:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any

Description:
Creating a backup using the MySQL Administrator generates an SQL script that has the COMMENTs for stored procedures and functions truncated to exactly 63 characters. 

Table and column COMMENTs appear untruncated in the generated backup script.

It was expected that stored procedure and function COMMENTs would be dumped in full, just like it is the case for COMMENTs of tables and columns

How to repeat:
1) create a procedure:

delimiter go

create procedure test_comment()
COMMENT '123456789_123456789_123456789_123456789_123456789_123456789_12345'
select 1;
go

2) backup using administrator:

DROP PROCEDURE IF EXISTS `test_comment`;
DELIMITER %%;
CREATE PROCEDURE `test_comment`()
    COMMENT '123456789_123456789_123456789_123456789_123456789_123456789_1234'
select 1%%
DELIMITER ;%%

not the last (65th) character is missing from the comment.

Suggested fix:
Backup the entire comment for stored procedures and functions, not just the first 64 characters.

Sorry, I said '63' in the first paragraph, but i mean 64 of course

I was unable to repeat testing current Windows source server 5.0.15-rc
and MA. Below the script created and pasted the original COMMENT
line reported:

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.15-rc-nt

/*!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 */;

/*!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' */;

--
-- Create schema yu
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ yu;
USE yu;

--
-- Procedure `yu`.`test_comment`
--

DROP PROCEDURE IF EXISTS `test_comment`;
DELIMITER %%;
CREATE PROCEDURE `test_comment`()
    COMMENT '123456789_123456789_123456789_123456789_123456789_123456789_1234'
    COMMENT '123456789_123456789_123456789_123456789_123456789_123456789_1234'

select 1%%
DELIMITER ;%%
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

Miguel, I'm not surprised! 

You comment is exactly 64 characters long, wich is just the boundary. Look again at my repeat recipe. The first is what i put in, the second is what came out:

123456789_123456789_123456789_123456789_123456789_123456789_12345
123456789_123456789_123456789_123456789_123456789_123456789_1234
              10             20             30             40            50             60      65
So, the last '5', the 65th character is missing. your test just puts the 64 characters in, and gets them all out again. Please try again with > 64 characters

Thank you for the feedback, but isn't an Administrator bug, if you test
with mysql client you will see it is truncated at 65th character,
I was unable for to find this information at:

http://dev.mysql.com/doc/mysql/en/create-procedure.html

"The COMMENT clause is a MySQL extension, and may be used to describe the stored procedure. This information is displayed by the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements."

So I am changing to Documentation verified status where should be
added the COMMENT with maximum 64 character.

c:\mysql\bin>mysql -uroot yu
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.15-rc-nt

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

mysql> DROP PROCEDURE IF EXISTS `test_comment`;
Query OK, 0 rows affected (0.13 sec)

mysql> DELIMITER %%;
mysql> CREATE PROCEDURE `test_comment`()
    ->     COMMENT '123456789_123456789_123456789_123456789_123456789_123456789_12345'
    -> select 1%%
Query OK, 0 rows affected (0.00 sec)

mysql> show create procedure test_comment\G
*************************** 1. row ***************************
       Procedure: test_comment
        sql_mode:
Create Procedure: CREATE PROCEDURE `test_comment`()
    COMMENT '123456789_123456789_123456789_123456789_123456789_123456789_1234'
select 1
1 row in set (0.00 sec)

Thanks for quick reaction, and for clearing that up Miguel.

Im sorry to've bothered you with this, I guess I should've looked around more carefully.

I'm marking this as a duplicate of bug#13934
(which was created later, but has more info)

Ok, fair enough. Thanks!