Bug #14885 Out of memory when creating view in stored procedure
Submitted: 11 Nov 2005 20:01 Modified: 6 Dec 2005 21:43
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.15-nt, 5.0.16 OS:Microsoft Windows (Windows, Mac OS X 10.4.2)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[11 Nov 2005 20:01] Paul Dubois
Description:
I see this problem on Mac OS X (Tiger), but not on Linux.

Creating a view within a stored procedure causes an
out of memory error.  However, the server does not
crash, and the view appears to have been created!

How to repeat:
Input file:

drop view if exists v;
drop procedure if exists p;
delimiter //
create procedure p () deterministic
begin
create view v as select 1;
end;
//
delimiter ;
call p();
show create view v;

Result:

mysql> drop view if exists v;
Query OK, 0 rows affected (0.07 sec)

mysql> drop procedure if exists p;
Query OK, 0 rows affected (0.40 sec)

mysql> delimiter //
mysql> create procedure p () deterministic
    -> begin
    -> create view v as select 1;
    -> end;
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> call p();
ERROR 5 (HY000): Out of memory (Needed 4281230632 bytes)
mysql> show create view v;
+--------------------------------------------------------------------------------------------------------+
| View | Create View                                                                                            |+--------------------------------------------------------------------------------------------------------+
| v    | CREATE ALGORITHM=UNDEFINED DEFINER=`paul`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select 1 AS `1` |
+--------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
[13 Nov 2005 12:29] Valeriy Kravchuk
On Windows XP and 5.0.15-nt call p() leads to server crash and view is not created:

mysql> use test;
Database changed
mysql> drop view if exists v;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop procedure if exists p;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter //
mysql> create procedure p () deterministic
    -> begin
    -> create view v as select 1;
    -> end;
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call p();
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> exit
Bye

C:\Documents and Settings\openxs>net start MySQL5
...

C:\Documents and Settings\openxs>mysql -uroot -p -P3307
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-nt

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

mysql> use test;
Database changed
mysql> show create view v;
ERROR 1146 (42S02): Table 'test.v' doesn't exist

On Linux and 5.0.17-BK everything works as expected. 

Sorry, but I have no Mac OS X to verify.
[30 Nov 2005 12:49] 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/32856
[2 Dec 2005 18:19] Konstantin Osipov
Approved by email with few small notes.
[2 Dec 2005 19:18] 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/32976
[3 Dec 2005 17:52] Oleksandr Byelkin
Thank you for bugreport!
The patch for this bug id pushed to 5.0.17 and 5.1.4
[6 Dec 2005 21:43] Paul Dubois
Noted in 5.0.17, 5.1.4 changelogs.