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 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

[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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.