mysql creation of a procedure fails, if the COMMENT field in the procedure is longer than 64 chars. This bug is known for log time: http://bugs.mysql.com/bug.php?id=34197 , apparently it was solved beforem but now is present again in dev-db/mysql-5.1.46 Reproducible: Always Steps to Reproduce: 1.try the next procedure (taken from ulogd-2.0.0_beta3): DROP PROCEDURE IF EXISTS DELETE_CUSTOM_ONE; delimiter $$ -- XXX be careful with SQL injections !! CREATE PROCEDURE DELETE_CUSTOM_ONE( IN tname varchar(64), IN tjoin varchar(64), IN _id bigint ) SQL SECURITY INVOKER COMMENT 'Delete packet in a custom table (specified at runtime) using a prepared query' BEGIN SET @l_sql = CONCAT('DELETE FROM ',@tname,' WHERE ',@tname,'.',@tfield,' = ',_id); PREPARE delete_stmt FROM @l_sql; EXECUTE delete_stmt; DEALLOCATE PREPARE delete_stmt; END $$ delimiter ; It fails with the message: ERROR 1607 (HY000) at line 9: Cannot create stored routine `DELETE_CUSTOM_ONE`. Check warnings 2. then try the modified procedure with the line: COMMENT 'Delete packet in a custom table using a prepared query' instead the long COMMENT line above. Voila - it works... 3. Actual Results: If ROUTINE_COMMENT is longer than 64 chars, it fails. Expected Results: Even ROUTINE_COMMENTs >64 chars should be allowed. The procedure above should NOT fail, being correct otherwise. The ROUTINE_COMMENT field in INFORMATION_SCHEMA.ROUTINES should be rather longtext than varchar(64), as recommended in http://bugs.mysql.com/bug.php?id=34197. This type of problems is rather difficult to diagnose and it seems to me, there is no reason for 64 char/line limitation
Please retest with 5.1.49-r1.
Fails with mysql-5.1.51 and similar statement from ulogd-2.0.0_beta4
*** Bug 349811 has been marked as a duplicate of this bug. ***
As per bug #349811.
Can you please test if this still fails with mysql-5.1.59?
I just confirmed this still applies to mysql-5.1.62-r1.
This may be fixed in the 5.5 series builds. MariaDB 5.5.25-r1 accepted the command. Good chance that MySQL 5.5 should too.
(In reply to comment #7) > This may be fixed in the 5.5 series builds. > > MariaDB 5.5.25-r1 accepted the command. > Good chance that MySQL 5.5 should too. I just hit this bug on mysql-5.5.28 and confirmed it doesn't affect mariadb-5.5.28.
(In reply to comment #8) > (In reply to comment #7) > > This may be fixed in the 5.5 series builds. > > > > MariaDB 5.5.25-r1 accepted the command. > > Good chance that MySQL 5.5 should too. > > I just hit this bug on mysql-5.5.28 and confirmed it doesn't affect > mariadb-5.5.28. I confirmed again this issue doesn't affect mariadb-5.5.30. With mysql-5.5.30 I got a corrupted mysql.proc table - I'm still trying to confirm if this is a local issue.
(In reply to Jorge Manuel B. S. Vicetto from comment #9) > (In reply to comment #8) > > (In reply to comment #7) > > > This may be fixed in the 5.5 series builds. > > > > > > MariaDB 5.5.25-r1 accepted the command. > > > Good chance that MySQL 5.5 should too. > > > > I just hit this bug on mysql-5.5.28 and confirmed it doesn't affect > > mariadb-5.5.28. > > I confirmed again this issue doesn't affect mariadb-5.5.30. With > mysql-5.5.30 I got a corrupted mysql.proc table - I'm still trying to > confirm if this is a local issue. I've got the mysql.proc error again, this time with both mysql-5.5.32 and mariadb-5.5.32. After running mysql_upgrade, it stopped giving the error and worked for both versions. Thus, I'm closing this bug as fixed.
