Bug #30882 Dropping a temporary table inside a stored function may cause a server crash
Submitted: 7 Sep 2007 4:01 Modified: 9 Nov 2007 0:05
Reporter: Davi Arnaut (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0/5.1BK OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any

[7 Sep 2007 4:01] Davi Arnaut
Description:
If a stored function that contains a drop temporary table statement
is invoked by a create temporary table of the same name may cause
a server crash.

How to repeat:
delimiter |
create function f1() returns int
begin
 drop temporary table t1;
 return 1;
end|
delimiter ;
create temporary table t1 as select f1();
[10 Sep 2007 13:39] Miguel Solorzano
Thank you for the bug report. Verified as described.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.50-debug Source distribution

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

mysql> delimiter |
mysql> create function f1() returns int
    -> begin
    ->  drop temporary table t1;
    ->  return 1;
    -> end|
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;
mysql> create temporary table t1 as select f1();
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[1 Nov 2007 20:53] 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/36924

ChangeSet@1.2613, 2007-11-01 18:52:56-02:00, davi@endora.local +15 -0
  Bug#30882 Dropping a temporary table inside a stored function may cause a server crash
  
  If a stored function that contains a drop temporary table statement
  is invoked by a create temporary table of the same name may cause
  a server crash. The problem is that when dropping a table no check
  is done to ensure that table is not being used by some outer query
  (or outer statement), potentially leaving the outer query with a
  reference to a stale (freed) table.
  
  The solution is when dropping a temporary table, always check if
  the table is being used by some outer statement as a temporary
  table can be dropped inside stored procedures.
  
  The check is performed by looking at the TABLE::query_id value for
  temporary tables. To simplify this check and to solve a bug related
  to handling of temporary tables in prelocked mode, this patch changes
  the way in which this member is used to track the fact that table is
  used/unused. Now we ensure that TABLE::query_id is zero for unused
  temporary tables (which means that all temporary tables which were
  used by a statement should be marked as free for reuse after it's
  execution has been completed).
[7 Nov 2007 22:00] Bugs System
Pushed into 6.0.4-alpha
[7 Nov 2007 22:01] Bugs System
Pushed into 5.1.23-rc
[9 Nov 2007 0:05] Paul Dubois
Noted in 5.1.23, 6.0.4 changelogs.

A server crash could occur if a stored function that contained a DROP
TEMPORARY TABLE statement was invoked by a CREATE TEMPORARY TABLE
statement that created a table of the same name.
[7 Dec 2009 6:01] Roel Van de Paar
Also note bug #29537
[1 Mar 2011 14:22] Jon Olav Hauglid
Bug#36089 was closed as a duplicate of this bug.