MySQL Bugs: #6199: Stored procedure packet error if database dropped

Bug #6199	Stored procedure packet error if database dropped
Submitted:	21 Oct 2004 15:21	Modified:	12 Apr 2005 12:02
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Linux (SuSE 8.2)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
Within a stored procedure, I drop the database that the procedure is in. After the procedure 
is called, the next statement causes "Packets out of order". 

How to repeat:
mysql> create database db30; 
Query OK, 1 row affected (0.27 sec) 
 
mysql> use db30; 
Database changed 
 
mysql> delimiter // 
 
mysql> create procedure db30p () begin create table db30.t (s1 int); insert into db30.t values 
(10); drop database db30; select * from db30.t; end;// 
Query OK, 0 rows affected (0.28 sec) 
 
mysql> use db3//                                       /* or any other database other than db30 */ 
Database changed 
 
mysql> call db30.db30p()// 
ERROR 1146 (42S02): Table 'db30.t' doesn't exist 
 
mysql> use db30// 
Packets out of order (Found: 2, expected 1) 
No connection. Trying to reconnect... 
Connection id:    2 
Current database: *** NONE *** 
 
ERROR 1049 (42000): Unknown database 'db30'

As Peter mentions *any* statement after procedure call results in packer out of order error

...

mysql> call db30.db30p()//
ERROR 1146 (42S02): Table 'db30.t' doesn't exist
mysql> select 1//
Packets out of order (Found: 2, expected 1)
ERROR 2013 (HY000): Lost connection to MySQL server during query

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

This is no longer repeatable.
The exact test case can't be used (due to the new table locking scheme), and
using a temporary table instead does not provoke the bug. (Temp. tables mysteriously
survive "drop database", which might be a bug in itself.)

Attempts to rewrite the test case didn't help either, it seems to be working.