MySQL Bugs: #30882: Dropping a temporary table inside a stored function may cause a server crash

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

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();

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>

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).

Pushed into 6.0.4-alpha

Pushed into 5.1.23-rc

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.

Also note bug #29537

Bug#36089 was closed as a duplicate of this bug.