Bug #33083 Stored Function: error and crash if a temporary table is dropped in a loop
Submitted: 8 Dec 2007 1:22 Modified: 20 Jun 2012 16:48
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.0, 5.1, 5.2 OS:Any
Assigned to: CPU Architecture:Any

[8 Dec 2007 1:22] Konstantin Osipov
Description:
A stored function statement does not detect changes in metadata.
If a temporary table is dropped in a loop, and thus a view becomes visible, 
a crash happens.

mysql> drop function if exists crash|
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create view v1 as select A.a, A.b from t1 A, t1 B|
ERROR 1050 (42S01): Table 'v1' already exists
mysql> 
mysql> create function crash() returns int
    -> begin
    ->   declare iteration integer default 0;
    ->   while iteration < 10 do
    ->     if iteration % 1 then
    ->       drop temporary table v1;
    ->     else
    ->       drop temporary table if exists t1, v1, v2;
    ->       create temporary table t1 (a int, b int);
    ->       insert into t1 values (1,1),(2,2);
    -> 
    ->       create temporary table v1 as select * from t1;
    ->       create temporary table v2 as select * from t1;
    ->     end if;
    ->     drop temporary table if exists t3;
    ->     create temporary table t3 as select * from v1, v2;
    ->     set iteration= iteration+1;
    ->   end while;
    ->   return 0;
    -> end|
Query OK, 0 rows affected (0.00 sec)

mysql> call crash()|
ERROR 2013 (HY000): Lost connection to MySQL server during query

How to repeat:
drop function if exists crash|
create view v1 as select A.a, A.b from t1 A, t1 B|

create function crash() returns int
begin
  declare iteration integer default 0;
  while iteration < 10 do
    if iteration % 1 then
      drop temporary table v1;
    else
      drop temporary table if exists t1, v1, v2;
      create temporary table t1 (a int, b int);
      insert into t1 values (1,1),(2,2);

      create temporary table v1 as select * from t1;
      create temporary table v2 as select * from t1;
    end if;
    drop temporary table if exists t3;
    create temporary table t3 as select * from v1, v2;
    set iteration= iteration+1;
  end while;
  return 0;
end|

Suggested fix:
WL#4179
[8 Dec 2007 1:25] Konstantin Osipov
See Bug#27690 for the origin of the test case.
The important distinct property of this bug is that the server runs stored functions under pre-locking.
[8 Dec 2007 1:37] Konstantin Osipov
Note: a similar bug could be reported when the loop is inside a trigger.
Triggers are also run under pre-locking, therefore the problem with triggers falls into the same category.
[8 Dec 2007 18:57] Sveta Smirnova
Thank you for the report.

Provided test case fails with syntax error for me. Please provide correct test case.
[8 Dec 2007 22:37] Konstantin Osipov
Sveta,
you're right, there is no crash but I can't reproduce one. 
But there is an error, and it is not supposed to happen.
It is output by the previous test case, but here is a new one:

drop table if exists t1, t2, t3, v1,v2;
drop view if exists v1,v2;
drop function if exists crash;
create table t1 (a int, b int);
create view v1 as select A.a, A.b from t1 A, t1 B;
drop table t1;

create table t1 (a int, b int);
insert into t1 values (1,1),(2,2);
create table v2 as select * from t1;

create temporary table v1 as select * from t1;

delimiter |
create function crash() returns int
begin
  declare iteration integer default 1;
  while iteration <= 2 do
    if iteration = 2 then
      drop temporary table if exists v1;
      drop temporary table t3;
    end if;
    create temporary table t3 select v1.a, v1.b from v1, v2; 
    set iteration= iteration+1;
  end while;
  return 0;
end|
delimiter ;
select crash();

It produces:

mysql> select crash();
ERROR 1146 (42S02): Table 'test.A' doesn't exist

No statement refers to 'test.A'.

Compare with execution outside a stored function:

drop table if exists t1, t2, t3, v1,v2;
drop view if exists v1,v2;
drop function if exists crash;
create table t1 (a int, b int);
create view v1 as select A.a, A.b from t1 A, t1 B;
drop table t1;

create table t1 (a int, b int);
insert into t1 values (1,1),(2,2);
create table v2 as select * from t1;

create temporary table v1 as select * from t1;

create temporary table t3 select v1.a, v1.b from v1, v2; 
drop temporary table if exists v1;
drop temporary table t3;
create temporary table t3 select v1.a, v1.b from v1, v2; 

It outputs:
mysql> create temporary table t3 select v1.a, v1.b from v1, v2; 
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0
[8 Dec 2007 22:38] Konstantin Osipov
Lowering priority, since the bug only produces an unexpected error, not bad data.
[9 Dec 2007 6:23] Sveta Smirnova
Thank you for the feedback.

Verified as described.
[8 Feb 2008 11:42] Konstantin Osipov
WL#4179 "Stored programs: validation of stored program statements"
[11 Dec 2009 19:18] MySQL Verification Team
This is a crashing bug. Try running the stored procedure twice.  Stack trace from 5.1.41:

mysqld.exe!make_cond_for_table()[sql_select.cc:12627]
mysqld.exe!make_join_select()[sql_select.cc:6425]
mysqld.exe!JOIN::optimize()[sql_select.cc:1075]
mysqld.exe!mysql_select()[sql_select.cc:2421]
mysqld.exe!handle_select()[sql_select.cc:269]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2714]
mysqld.exe!sp_instr_stmt::exec_core()[sp_head.cc:2910]
mysqld.exe!sp_lex_keeper::reset_lex_and_exec_core()[sp_head.cc:2737]
mysqld.exe!sp_instr_stmt::execute()[sp_head.cc:2854]
mysqld.exe!sp_head::execute()[sp_head.cc:1257]
mysqld.exe!sp_head::execute_function()[sp_head.cc:1776]
mysqld.exe!Item_func_sp::execute_impl()[item_func.cc:6024]
mysqld.exe!Item_func_sp::execute()[item_func.cc:5952]
mysqld.exe!Item_func_sp::val_int()[item_func.h:1642]
mysqld.exe!Item::send()[item.cc:5548]
mysqld.exe!select_send::send_data()[sql_class.cc:1644]
mysqld.exe!JOIN::exec()[sql_select.cc:1726]
mysqld.exe!mysql_select()[sql_select.cc:2437]
mysqld.exe!handle_select()[sql_select.cc:269]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5052]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2246]
mysqld.exe!mysql_parse()[sql_parse.cc:5974]
mysqld.exe!dispatch_command()[sql_parse.cc:1233]
mysqld.exe!do_command()[sql_parse.cc:872]
mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:295]
mysqld.exe!_threadstart()[thread.c:275]
kernel32.dll!BaseThreadStart()
ariables.
 invalid and cause the dump to abort...
00042FB030=create temporary table t3 select v1.a, v1.b from v1, v2
[20 Jun 2012 16:48] Paul DuBois
Noted in 5.6.6 changelog.

Errors could occur for a TEMPORARY table used within a stored program
if the table was dropped between executions of the program or while
the view was used within a program loop.