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)
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.0.48 / 5.1.21 / 6.0.5
• OS: FreeBSD
• CPU Architecture: Any
• Tags: qc, rename database, temporary table

[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] 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.