Bug #34197 CREATE PROCEDURE fails when COMMENT truncated in non strict SQL mode
Submitted: 31 Jan 2008 13:49 Modified: 12 Nov 2009 20:53
Reporter: Mark Leith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1.23 OS:Any
Assigned to: Jon Olav Hauglid
Tags: comments, stored procedure
Triage: Triaged: D4 (Minor)

[31 Jan 2008 13:49] Mark Leith
Description:
Having a COMMENT within SPs of greater than 64 characters long now causes the CREATE PROCEDURE statement to fail, even when in a non-strict SQL mode:

mysql> CREATE PROCEDURE p1 ()
    -> COMMENT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
    -> BEGIN
    -> END;
ERROR 1607 (HY000): Cannot create stored routine `p1`. Check warnings
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1265 | Data truncated for column 'comment' at row 1      | 
| Error   | 1607 | Cannot create stored routine `p1`. Check warnings | 
+---------+------+---------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            | 
+------------+
1 row in set (0.00 sec)

This is a regression, and was introduced within ChangeSet 1.2476.480.11 2007/10/17 12:13:56 anozdrin@:

 Fix for BUG#24923: Functions with ENUM issues.
  
  The problem was that the RETURNS column in the mysql.proc was of
  CHAR(64). That was not enough for storing long-named datatypes.
  
  The fix is to change CHAR(64) to LONGBLOB, and to throw warnings
  at the time a stored routine is created if some data is truncated
  during writing into mysql.proc.

How to repeat:
SET SESSION sql_mode = '';
DROP PROCEDURE IF EXISTS p1;
CREATE PROCEDURE p1 ()
COMMENT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
BEGIN
END;
SHOW WARNINGS;

Suggested fix:
Only disallow truncated comments when within a strict SQL mode
[31 Jan 2008 17:40] Miguel Solorzano
c:\dbs>5.1\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.24-rc-nt Source distribution

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

mysql> SET SESSION sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> DROP PROCEDURE IF EXISTS p1;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> CREATE PROCEDURE p1 ()
    -> COMMENT
    -> 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
    -> BEGIN
    -> END;
ERROR 1607 (HY000): Cannot create stored routine `p1`. Check warnings
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1265 | Data truncated for column 'comment' at row 1      |
| Error   | 1607 | Cannot create stored routine `p1`. Check warnings |
+---------+------+---------------------------------------------------+
2 rows in set (0.05 sec)

mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql>
[7 Feb 2008 14:28] Konstantin Osipov
I don't think I agree with the suggested solution.
We should increase the width of comment column instead, but strict mode has
nothing to do with truncation that happens in a data dictionary operation.
[7 Feb 2008 14:47] Mark Leith
I actually suggested that because that is what was done in other similar instances:

http://bugs.mysql.com/bug.php?id=13934
http://bugs.mysql.com/bug.php?id=13823

Now - I certainly agree that we could increase the comment column - having it so small seems just a little crazy to me - I see no reason to stop people being verbose within the SP comment if they want to. 

If you choose to fix it like this, that would be great! However, I still wonder whether we should follow the conventions as with tables above?
[2 Jun 2009 11:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/75458

2791 Jon Olav Hauglid	2009-06-02
      Bug #34197 CREATE PROCEDURE fails when COMMENT truncated in non 
                 strict SQL mode
      
      COMMENT field in mysql.proc changed from char(64) to text.
      mysql_system_tables.sql and mysql_system_tables_fix.sql updated.
      Test case added to sp.test.
[2 Jun 2009 13:29] Alexander Nozdrin
I'm fine with the patch.
[12 Jun 2009 9:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76168

2793 Jon Olav Hauglid	2009-06-12
      Followup to Bug#34197 and Bug#21099
      
      This is an update of 6 result files in the funcs_1 test suite, 
      that by mistake were not updated when NO_ENGINE_SUBSTITUTION 
      was added to strict mode (21099) and COMMENT field in mysql.proc 
      was changed to text data type (34197).
[16 Jun 2009 7:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76351

2795 Jon Olav Hauglid	2009-06-16
      Followup to Bug#34197
      
      ROUTINE_COMMENT field in INFORMATION_SCHEMA.ROUTINES 
      changed from varchar(64) to longtext.
      Updated test case in sp.test and affected result files.
[16 Jun 2009 8:41] Alexander Nozdrin
Ok to push.
[18 Jun 2009 6:48] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090617073019-azsawauatv99124t) (version source revid:jon.hauglid@sun.com-20090616092759-pelq3sqh8ql0qxoa) (merge vers: 5.4.4-alpha) (pib:11)
[29 Jun 2009 0:40] Paul Dubois
Noted in 5.4.4 changelog.

A COMMENT longer than 64 characters caused CREATE PROCEDURE to fail.
[12 Aug 2009 22:58] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 2:15] Paul Dubois
Ignore previous comment about 5.4.2.
[9 Oct 2009 15:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86413

2901 Jon Olav Hauglid	2009-10-09
      Bug #34197 CREATE PROCEDURE fails when COMMENT truncated in non 
                 strict SQL mode
      
      The problem was that a COMMENT longer than 64 characters
      caused CREATE PROCEDURE to fail.
      
      This patch fixed the problem by changing the COMMENT field in 
      mysql.proc from char(64) to text. The corresponding ROUTINE_COMMENT
      field in INFORMATION_SCHEMA.ROUTINES is also changed from
      varchar(64) to longtext.
      
      mysql_system_tables.sql and mysql_system_tables_fix.sql updated.
      Test case added to sp.test and affected result-files updated.
[9 Oct 2009 15:36] Jon Olav Hauglid
Pushed to mysql-next-mr-runtime (version 5.5.0)
[10 Oct 2009 0:11] Paul Dubois
Noted in 5.5.0 changelog.
[3 Nov 2009 7:16] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@ibmvm-20091009194256-qr0tniatakj4dw4p) (merge vers: 6.0.14-alpha) (pib:13)
[3 Nov 2009 15:50] Paul Dubois
Noted in 6.0.14 changelog.
[12 Nov 2009 8:19] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)