cannot delete db table

I tried to use a DROP statement to delete a table but I could not.Instead I tried the following....I deleted the .frm and .ibd files from of the respective table from the directory.

Then I used a create statement from a backup file to recreate the table...and the weird thing is that I get message saying that the table already exists (despite having deleted the aforementioned files)...

After the create statement the directory has again the .ibd file.

I do not know what to do or assume.

First have a look at the InnoDB Architecture (from Percona CTO Vadim Tkachenko)

Your problem is very simple. You have a broken data dictionary entry. Inside the system tablespace file ibdata1 is a list of tablespace ids.

There is an entry for appointments.business_users. Deleting the .frm and .ibd of appointments.business_users does not remove its tablespace id from the data dictionary. You need to remove that entry from the data dictionary.

The reason you are getting the error still ? Although you can the CREATE TABLE and generated the .frm and .ibd file, the tablespace id in the .ibd file does not match the original tablespace id in the data dictionary.

Here are some old posts on how to repair it in place

• From @akuzminsky : InnoDB issue after power outage a few weeks ago - no backup

• From @DerekDowney : ERROR Cannot find or open table?

• From me : restore table from .frm and .ibd file?

If the database isn't too big, you could do the following:

• mysqldump everything EXCEPT appointments.business_users

• drop all databases (except mysql schema)

• shutdown mysql

• delete ibdata1

• start mysql (recreated ibdata1)

• reload the mysqldump

• manually run the CREATE TABLE command for appointments.business_users

Either way, you have some homework to do.

StackExchange Database Administrators Q#107608, answer score: 3