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

[27 Jul 2007 14:29] Martin Friebe
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)

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] MySQL Verification Team
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.