Bug #49194 Dropping a database linked to a TEMPORARY table does not drop the table.
Submitted: 30 Nov 2009 5:33 Modified: 1 Dec 2009 16:45
Reporter: Roel Van de Paar (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.0.23+, 5.1.40, 5.1.41 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[30 Nov 2009 5:33] Roel Van de Paar
Description:
--------
mysql> CREATE DATABASE TMPT;
Query OK, 1 row affected (0.00 sec)

mysql> USE TMPT
Database changed

mysql> CREATE TEMPORARY TABLE tmpt1 (ID INT);
Query OK, 0 rows affected (0.02 sec)

mysql> DROP DATABASE TMPT;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TEMPORARY TABLE tmpt1;
ERROR 1046 (3D000): No database selected

mysql> USE test
Database changed

mysql> DROP TEMPORARY TABLE tmpt1;
ERROR 1051 (42S02): Unknown table 'tmpt1'

mysql> CREATE DATABASE TMPT;
Query OK, 1 row affected (0.00 sec)

mysql> DROP TEMPORARY TABLE tmpt1;
ERROR 1051 (42S02): Unknown table 'tmpt1'

mysql> USE TMPT;
Database changed

mysql> DROP TEMPORARY TABLE tmpt1;
Query OK, 0 rows affected (0.03 sec)
--------

How to repeat:
CREATE DATABASE TMPT;
USE TMPT
CREATE TEMPORARY TABLE tmpt1 (ID INT);
DROP DATABASE TMPT;
DROP TEMPORARY TABLE tmpt1;
USE test
DROP TEMPORARY TABLE tmpt1;
CREATE DATABASE TMPT;
DROP TEMPORARY TABLE tmpt1;
USE TMPT;
DROP TEMPORARY TABLE tmpt1;

Suggested fix:
Drop TEMPORARY table linked to db when db is dropped.
[30 Nov 2009 5:37] Roel Van de Paar
Verifying as D2.
[1 Dec 2009 4:46] Valeriy Kravchuk
Same with 5.0.x:

77-52-7-73:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE TMPT;
Query OK, 1 row affected (0.00 sec)

mysql> USE TMPT
Database changed
mysql> CREATE TEMPORARY TABLE tmpt1 (ID INT);
Query OK, 0 rows affected (0.01 sec)

mysql> DROP DATABASE TMPT;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TEMPORARY TABLE tmpt1;
ERROR 1046 (3D000): No database selected
mysql> USE test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP TEMPORARY TABLE tmpt1;
ERROR 1051 (42S02): Unknown table 'tmpt1'
mysql> CREATE DATABASE TMPT;
Query OK, 1 row affected (0.01 sec)

mysql> DROP TEMPORARY TABLE tmpt1;
ERROR 1051 (42S02): Unknown table 'tmpt1'
mysql> USE TMPT;
Database changed
mysql> DROP TEMPORARY TABLE tmpt1;
Query OK, 0 rows affected (0.01 sec)
[1 Dec 2009 16:45] Konstantin Osipov
Thank you for writing to us, but please do not report the same bug more than once.
This is a duplicate of Bug#30099