| Bug #11602 | stored procedure > 64K causes bad behavior | ||
|---|---|---|---|
| Submitted: | 28 Jun 2005 5:42 | Modified: | 7 Aug 2005 1:11 |
| Reporter: | Timothy Smith | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.8 | OS: | Any (any) |
| Assigned to: | Jim Winstead | CPU Architecture: | Any |
[28 Jun 2005 21:18]
Timothy Smith
run: mysql test < longprocedure.sql > out
Attachment: longprocedure.sql.gz (application/gzip, text), 834 bytes.
[27 Jul 2005 1:08]
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/internals/27619
[3 Aug 2005 2:54]
Jim Winstead
Fixed in 5.0.14.
[7 Aug 2005 1:11]
Mike Hillyer
Documented in 5.0.11 changelog: <listitem><para> Maximum size of stored procedures increased from 64k to 4Gb. (Bug #11602) </para></listitem>
Description: I tested this on FreeBSD 5.4, with today's BK code. I am attaching a file to this bug report that contains the exact longprocedure.sql that I used. I run it like: $ mysql test < longprocedure.sql > longprocedure.txt ERROR 1064 (42000) at line 4109: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select co' at line 1460 Line 4109 is: call test.longprocedure(@value); select @value; A tail of the longprocedure.txt shows why: $ tail -15 longprocedure.txt select count(*) into param1 from mysql.user; select count(*) into param1 from mysql.user; select co EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: YES SQL_DATA_ACCESS: CONTAINS SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2005-06-28 17:10:13 LAST_ALTERED: 2005-06-28 17:10:13 SQL_MODE: ROUTINE_COMMENT: DEFINER: root@localhost So, clearly the 'body' field is truncated. Hmmm, it's defined as a BLOB in mysql.proc. mysql> select length(body) from mysql.proc where specific_name = 'longprocedure'; +--------------+ | length(body) | +--------------+ | 65535 | +--------------+ In earlier versions, the server would crash when trying to do some operations, like SHOW CREATE PROCEDURE longprocedure. But now I just get a syntax error. It also gives a syntax error when I try to drop it: mysql> drop procedure longprocedure; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select co' at line 1460 I can delete it from mysql.proc, though. How to repeat: See Description and the attached file, longprocedure.sql. Suggested fix: I ran "alter table proc modify body longblob not null", and was then able to perform all the operations. This is a simple fix; I don't see any reason it won't work, but there may be some hidden problems with it.