3

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.

8
  • 2
    whats the error message you received when you executed drop table x; Jul 21, 2015 at 10:09
  • unfortunately I cannot remember...in the panic I forgot it...I have this problem for hours and it appeared out of nowhere. Jul 21, 2015 at 10:11
  • Can you run SHOW CREATE TABLE tblname \G on the table right now ??? Jul 21, 2015 at 10:15
  • this is the code SHOW CREATE TABLE appointments.business_users \G; appointments is the database....I get a syntax warning #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\G' at line 1 Jul 21, 2015 at 10:18
  • It's not clear what the problem is now. Do you have access to the table or not? Does select * from table; work or not? Do you want to drop the table and you get an error? Jul 21, 2015 at 10:40

1 Answer 1

3

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

InnoDB Plumbing

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

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.

9
  • How am I going to open ibdata1...I tried with notepad and the program crashes. Jul 21, 2015 at 12:12
  • ibdata1 is a binary file. You cannot manipulate with a text editor. Look at the diagram. There are many moving parts within ibdata1. There is no text manipulation available. Jul 21, 2015 at 12:15
  • 1
    No, no, no, you do not open that file. No. (ΟΧΙ, apropos ;) Jul 21, 2015 at 12:15
  • 2
    And the real take-home from all of this is: Don't delete .frm and .ibd files by hand. Jul 21, 2015 at 12:18
  • ok...I do not open ibdata1...how am I going to remove the ID from the data dictionary....read the posts? Jul 21, 2015 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.