Bug #60413 Dropped tables remains present and prevent recreation
Submitted: 10 Mar 2011 9:34 Modified: 10 Apr 2011 13:07
Reporter: Christophe Fondacci Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.1.41 OS:MacOS ((at least MacOS X))
Assigned to: CPU Architecture:Any

[10 Mar 2011 9:34] Christophe Fondacci 
Description:
This bug does not happen all the time.

Here is an example of the problem (the 2 following commands are entered consecutively, in a single-user environment, therefore the state of the database is exactly the same when first and second statements are issued) :

mysql> desc BENG_VERSIONABLE_DELIVERIES
    -> ;
ERROR 1146 (42S02): Table 'tutu.beng_versionable_deliveries' doesn't exist
mysql> CREATE TABLE BENG_VERSIONABLE_DELIVERIES ( 
    ->            DELIVERY_ID BIGINT
    ->           ,VERSION_ID BIGINT
    -> )
    ->  Engine=InnoDB default charset=latin1;
ERROR 1050 (42S01): Table 'beng_versionable_deliveries' already exists
mysql> 

A "show tables" does not list the table.

It happens only when the table already existed but has been dropped. It generally happens when many drops have been performed on a database.

Not that I also had a very similar problem after dropping a database. I don't have the exact test case here, but it was something like :

drop database test;
create database test;
use test
create table mytab(a varchar(1));
alter table mytab add primary key (a);

And the last PK creation was resulting in a "Multiple primary key defined". It happened when the previously dropped database had a same table with a primary key.

It happens randomly, but quite often.

How to repeat:
1. Create a db
2. Create many objects using a script
3. Drop all objects
4. Recreate same objects using same scripts

If it succeeds, repeat step 3 to 4 

Suggested fix:
Properly drop elements...
[10 Mar 2011 13:07] Valeriy Kravchuk 
Please, check with a newer version, 5.1.56, and inform about the results.
[10 Apr 2011 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".