MySQL Bugs: #33083: Stored Function: error and crash if a temporary table is dropped in a loop

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

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

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.

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.

Thank you for the report.

Provided test case fails with syntax error for me. Please provide correct test case.

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

Lowering priority, since the bug only produces an unexpected error, not bad data.

Thank you for the feedback.

Verified as described.

WL#4179 "Stored programs: validation of stored program statements"

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

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.