Bug #32890 Crash after repeated create and drop of tables and views
Submitted: 1 Dec 2007 1:44 Modified: 14 Mar 2008 20:53
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1.23-rc-debug OS:Linux (SUSE 10 64-bit)
Assigned to: Davi Arnaut CPU Architecture:Any

[1 Dec 2007 1:44] Peter Gulutzan
Description:
I create a procedure which, repeatedly, randomly,
creates and drops tables named a or b, and
creates and drops views named a or b which
contain references to a or b.
I call the procedure.
Crash.

How to repeat:
delimiter //
drop procedure p//
create procedure p ()
begin
  declare v int;
  declare r int;
  declare continue handler for sqlexception begin end;
  drop view a;
  drop view b;
  drop table a;
  drop table b;
  set v = 0;
  while v < 500 do
    set r = (rand()*10000) mod 5;
    select v,r;
    if r =0 then create table b (s1 int); end if;
    if r =1 then drop table b; end if;
    if r =2 then create view a as select * from b; end if;
    if r =3 then drop view a; end if;
    if r =4 then create view b as select * from a; end if;
    if r =5 then drop view b; end if;
    set v = v + 1;
    end while;
  end//
call p()//
call p()//
call p()//
[2 Dec 2007 9:34] Sveta Smirnova
Thank you for the report.

Verified as described.
[5 Dec 2007 1:22] Davi Arnaut
Reduced test case that triggers the crash:

create table b (s1 int)//
create procedure p ()
begin
  declare continue handler for sqlexception begin end;
  create view a as select * from b;
  create view c as select * from a;
  end//
call p()//
call p()//

Smaller test case that shows the problem:

create procedure p ()
begin
  declare continue handler for sqlexception begin end;
  create view a as select 1;
  end//
call p()//
call p()//
[15 Feb 2008 11:07] Davi Arnaut
The crash of CREATE VIEW inside a stored procedure problem has been reported as Bug#34587
[15 Feb 2008 22:36] 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/42406

ChangeSet@1.2578, 2008-02-15 20:36:24-02:00, davi@mysql.com +5 -0
  Bug#32890 Crash after repeated create and drop of tables and views
  
  The problem is that CREATE VIEW statements inside prepared statements
  weren't being expanded during the prepare phase, which leads to objects
  not being allocated in the appropriate memory arenas.
  
  The solution is to perform the validation of CREATE VIEW statements
  during the prepare phase of a prepared statement. The validation
  during the prepare phase assures that transformations of the parsed
  tree will use the permanent arena of the prepared statement.
[21 Feb 2008 17:58] 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/42776

ChangeSet@1.2583, 2008-02-21 14:58:29-03:00, davi@mysql.com +6 -0
  Bug#32890 Crash after repeated create and drop of tables and views
  
  The problem is that CREATE VIEW statements inside prepared statements
  weren't being expanded during the prepare phase, which leads to objects
  not being allocated in the appropriate memory arenas.
  
  The solution is to perform the validation of CREATE VIEW statements
  during the prepare phase of a prepared statement. The validation
  during the prepare phase assures that transformations of the parsed
  tree will use the permanent arena of the prepared statement.
[21 Feb 2008 18:58] Konstantin Osipov
Approved the second patch.
[21 Feb 2008 20:33] Davi Arnaut
Queued in 5.0-runtime
[3 Mar 2008 18:14] Bugs System
Pushed into 5.0.58
[3 Mar 2008 18:18] Bugs System
Pushed into 5.1.24-rc
[3 Mar 2008 18:18] Bugs System
Pushed into 6.0.5-alpha
[14 Mar 2008 20:53] Jon Stephens
Documented in the 5.0.58, 5.1.24, and 6.0.5 changelogs as follows:

        Repeated creation and deletion of views within prepared statements could
        eventually crash the server.
[31 Mar 2008 19:50] Jon Stephens
Also noted in the 5.1.23-ndb-6.3.11 changelog.