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
• Status: Duplicate
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 5.0.23+, 5.1.40, 5.1.41
• OS: Any
• CPU Architecture: Any

[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