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.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Dimitris Papageorgiou
  • 140
  • 1
  • 7
  • 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
  • Can you run SHOW CREATE TABLE appointments.business_users; and post the exact output in the question (even if it is an error message)? – ypercubeᵀᴹ Jul 21 '15 at 10:45
  • If write select *... from I get message saying that the table does not exist.If I go to create though a table with the exact same name I get the following message:1814 tablespace for table tblname exists.disregard the tablespace before import – Dimitris Papageorgiou Jul 21 '15 at 10:47
  • @ypercube here is the message I get in the above statement:#1146 - Table 'appointments.business_users' doesn't exist – Dimitris Papageorgiou Jul 21 '15 at 10:48

1 Answers1

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.

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520