Bug #30099 rename/drop database ignores temp tables
Submitted: 27 Jul 2007 14:29 Modified: 27 Jul 2007 14:44
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.0.48 / 5.1.21 / 6.0.5 OS:FreeBSD
Assigned to: CPU Architecture:Any
Tags: qc, rename database, temporary table
Triage: Triaged: D3 (Medium) / R3 (Medium) / E4 (High)

[27 Jul 2007 14:29] Martin Friebe
Description:
Both command ignores temporary tables present in the database.
(rename database only applies to 5.1 / drop happens in 5.0 too)

Due to Bug #28902 they can still be accessed under the no longer existent old db name.

---
reanme database http://dev.mysql.com/doc/refman/5.1/en/rename-database.html
currently only mentions limits on priveleges, SP and events.

---
drop database does not document any limitations (only privileges are not droped automated)
http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

the docomentation does also need review
>>> quote 1
 DROP DATABASE drops all tables in the database and deletes the database
>>> quote 2
DROP DATABASE returns the number of tables that were removed. This corresponds to the number of .frm files removed.
<<<

All tables would include temporary tables (which have no frm file).

quote 2 describes the current behaviour, but quote 2 will be wrong if drop database does remove temp tables.

How to repeat:
# 5.1 / rename
drop database if exists dbx1;
drop database if exists dbx2;
create database dbx1;

create temporary table dbx1.t1 (b int); insert into dbx1.t1 select 22;
select * from dbx1.t1;

rename database dbx1 to dbx2;

select * from dbx2.t1; # expected, but not there
select * from dbx1.t1; # still there, unexpected

drop table dbx1.t1;

# 5.0, 5.1 / drop
create database newdb;
create temporary table newdb.t1 (a int);
insert into newdb.t1 values(2);
select * from newdb.t1;
drop database newdb;  # table will not be removed
select * from newdb.t1;

drop table newdb.t1;

Suggested fix:
-
[27 Jul 2007 14:44] Miguel Solorzano
Thank you for the bug report.
[16 Jun 2008 15:00] Jørgen Løland
A related problem: If you create a temporary table with the same name as an existing table, drop database will fail:

> create database dbase;
> use dbase;
> create table t (i int);
> insert into t values (1);
> create temporary table t (i int);
> insert into t values (2);
> drop database dbase;
ERROR 1010 (HY000): Error dropping database (can't rmdir './dbase', errno: 39)
> use dbase;
> select * from t;
+------+
| i    |
+------+
|    1 | 
+------+

Notice that the result is from the base table t, not the temporary table t.
[16 Jun 2008 15:03] Jørgen Løland
The above drop database problem was observed on mysql-6.0.5 on linux/64bit
[17 Jun 2008 7:05] Jørgen Løland
This bug causes some problems for backup. See bug#34903
[17 Sep 2009 14:52] Konstantin Osipov
Lars-Erik, please contact me on IRC I will show what function to patch and how.
[15 Oct 2009 6:20] Lars-Erik Bjørk
The related problem reported by Jørgen Løland, has now been filed as a separate bug: bug#48067
[1 Dec 2009 16:45] Konstantin Osipov
Bug #49194 was marked a duplicate of this bug.
[20 May 2014 7:25] Marko Mäkelä
Bug#72686 is a duplicate of this.