MySQL Bugs: #8355: When Dropping a db, and then recreating it, data magically reappears.

Bug #8355	When Dropping a db, and then recreating it, data magically reappears.
Submitted:	7 Feb 2005 15:03	Modified:	22 Feb 2005 21:38
Reporter:	Winston	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	4.1.9-standard	OS:	MacOS (Mac OS 10.3)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
When you create a database, fill some tables, and then drop and recreate the database, the old data magically reappears.  Look at the attached script.  But if you drop the tables before you drop the database, then the old data doesn't come back.

How to repeat:
To repeat the bug, run this script twice.  The first time will work find.  The second time, the insert will fail because the old data before being dropped has magically reappeared.
---------
# Uncomment these to get the script to work after the first run.
#use MKBasic;
#drop table _MKClassIds;

drop database if exists MKBasic;
create database MKBasic;
use MKBasic;

create table _MKClassIds (
	id int not null primary key,
	name varchar(100)
);

select 'This will be empty first time it is called.  This will be magically filled with data the second time.';
select 'before insert', id, name from _MKClassIds;

insert into _MKClassIds (id, name) values 	(1, 'Thing');
insert into _MKClassIds (id, name) values 	(2, 'Person');

select 'after insert', id, name from _MKClassIds;

Suggested fix:
If you drop the individual table before you drop the database will get things to work, but that shouldn't be necessary.  After all, if you drop a database and then recreate it, one should never have the tables mysteriously full of data before you insert anything yourself.

mysql> 
mysql> insert into _MKClassIds (id, name) values       (1, 'Thing');
ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> insert into _MKClassIds (id, name) values       (2, 'Person');
ERROR 1062 (23000): Duplicate entry '2' for key 1
mysql>

The tables that were dropped along with the database were not being flushed from the table cache when lower_case_table_names was set to 2 (as it is on Mac OS X). As a workaround, you can add 'FLUSH TABLES' after your 'DROP DATABASE'.

Jim, a more efficient way is possible. Please take a look into sql_cache.cc:

#ifndef FN_NO_CASE_SENCE
...
#else
 VOID(hash_init(&tables,
                 lower_case_table_names ? &my_charset_bin :
                 files_charset_info,
                 def_table_hash_size, 0, 0,query_cache_table_get_key, 0, 0));
#endif

Can you please fix it the same way?

Pushed, will be in 4.1.11.

Noted in 4.1.11 changelog.