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.

  • 2
    whats the error message you received when you executed drop table x; – Ahmad Abuhasna Jul 21 '15 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. – Dimitris Papageorgiou Jul 21 '15 at 10:11
  • Can you run SHOW CREATE TABLE tblname \G on the table right now ??? – RolandoMySQLDBA Jul 21 '15 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 – Dimitris Papageorgiou Jul 21 '15 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? – ypercubeᵀᴹ Jul 21 '15 at 10:40

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.

  • How am I going to open ibdata1...I tried with notepad and the program crashes. – Dimitris Papageorgiou Jul 21 '15 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. – RolandoMySQLDBA Jul 21 '15 at 12:15
  • 1
    No, no, no, you do not open that file. No. (ΟΧΙ, apropos ;) – ypercubeᵀᴹ Jul 21 '15 at 12:15
  • 2
    And the real take-home from all of this is: Don't delete .frm and .ibd files by hand. – Colin 't Hart Jul 21 '15 at 12:18
  • ok...I do not open ibdata1...how am I going to remove the ID from the data dictionary....read the posts? – Dimitris Papageorgiou Jul 21 '15 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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