MySQL Bugs: #30099: rename/drop database ignores temp tables

Bug #30099	rename/drop database ignores temp tables
Submitted:	27 Jul 2007 16:29	Modified:	27 Jul 2007 16:44
Reporter:	Martin Friebe (Gold Quality Contributor) (SCA)
Status:	Verified
Category:	Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.48 / 5.1.21 / 6.0.5	OS:	FreeBSD
Assigned to:	Lars-Erik Bjørk	Target Version:
Tags:	qc, rename database, temporary table
Triage:	Triaged: D3 (Medium) / R3 (Medium) / E4 (High)

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

Thank you for the bug report.

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.

The above drop database problem was observed on mysql-6.0.5 on linux/64bit

This bug causes some problems for backup. See bug#34903

Lars-Erik, please contact me on IRC I will show what function to patch and how.

The related problem reported by Jørgen Løland, has now been filed as a separate bug:
bug#48067