Bug #12286 Create table after db deletion throws error: Can't create table '.\dbname\table
Submitted: 30 Jul 2005 17:35 Modified: 30 Jul 2005 18:41
Reporter: Ithai Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.13-nt OS:Windows (Win server 2003)
Assigned to: CPU Architecture:Any

[30 Jul 2005 17:35] Ithai

Due to server migration I took a backup of all my sites + seperate dumps of my different dbs.
The whole migration worked out fine without any problems accept for one domain.
This one was IP based and due to some unexpected problems I had to delete the sitedata and db, and did this with ensim pro.
I got everything running again accept for the db.
Somehow I cannot restore the db using phpmyadmin or command line.
The issue is this.
When I try to restore the db from the dump it gives an error.
The error is situated on the creation of the first table (t_admin) so I tried to only create the first table t_admin to see what happens.
Same error.
If I now change the name of the table t_admin into tab_admin, it gets created without a problem.
So I started checking the other tables. 
Each time I try to create a table that has the same name as in the dump (this means as how it originally was on the server) it gives an error, if I change the table name just slitely, it gets created.

The error is:

Can't create table '.\dbname\t_admin.frm' (errno: 121)

I checked everything, the database does not exist anywhere on the server anymore but somehow the mysql engine thnks it still exists.

After posting on several forums + listening to everyone's advice and even upgrading MySql to version 4.1.13-nt the problem still persists.

CREATE TABLE `t_admin` (
  `ADM_id` int(1) NOT NULL auto_increment,
  `ADM_login` varchar(20) NOT NULL default '',
  `ADM_pword` varchar(20) NOT NULL default '',
  `ADM_uname` varchar(50) NOT NULL default '',

Allready checked and not an issue:
- foreign keys (as you can see there aren't any)
- DROP TABLE IF EXISTS t_admin; before statement does not have an effect
- MySQL's data dir for this db is completely empty (so no residu here)
- restrictive permissions on table names not an issue, same problem for other tables

Changing the name of the table to a name other than present in the original db makes it possible to create it, but also changing the type from InnoDb to MyIsam with original name makes it possible to create the table.

So I think it has to do with the fact its InnoDb and maybe there is a residu of the db in the ibdata1 file, but grasping at strows here.


How to repeat:
Create database.
Remove site + database via ensim pro.
Not able to recreate original innodb tables.
[30 Jul 2005 18:41] Ithai
Deleted db completely again via command prompt ... Every residu that may have been existing after first deletion is gone now ... table dumps can be loaded again.