| 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: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.1.23 | OS: | Any |
| Assigned to: | Jon Olav Hauglid | CPU Architecture: | Any |
| Tags: | comments, stored procedure | ||
[31 Jan 2008 17:40]
MySQL Verification Team
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)
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