CODEWITHSUNDEEP
mysqlinnodb
Zoltan Fedor
Zoltan Fedor

Reputation: 2107

MySQL 5.6.10 InnoDB cannot create table as table already exists

I have a very interesting case. Several of my InnoDB tables got somehow corrupted. The .frm files of the tables disappeared and only the .ibd files were left.

I thought no problem, I will restore these tables from the backup, so I deleted the .frm files and try to create the new tables with the same name, but every time I get "the table already exists", the .ibd file gets created, but the .frm not.

Basically now I cannot create InnoDB tables with the given names in the given database. My only solution was to create MyISAM tables instead, but I would still like to know how to resolve this.

Below is a detailed log of the events:

  1. Try to create the table:

CREATE TABLE employee ( employee_id varchar(20) NOT NULL,
PRIMARY KEY (employee_login_id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=1 ROW_FORMAT=COMPRESSED

table already exists

  1. Try to drop the table to show that it doesn't exist:

DROP TABLE employee

table doesn't exist

  1. I manually deleted the employee.ibd file (there is no employee.frm file)

  2. Try #1 again and receive the "table already exists" again

I have restarted MySQL, no change. I have restarted the whole server, no change.

I cannot create an InnoDB table with the name "employee" in this tablespace anymore.

Any idea?

Upvotes: 1

Views: 2082

Answers (1)

akuzminsky
akuzminsky

Reputation: 2258

Information about a table is stored in two places:

  1. Server-wide table.frm file
  2. Storage-engine specific InnoDB dictionary

These two must be in-sync, but there is no reliable mechanism to enforce this consistency. Due to number of reasons InnoDB dictionary gets out of sync.

In your case there is an orphaned record in the dictionary. You need to delete it.

InnoDB doesn't provide any way to manually modify the dictionary records. But you can craft a fake table.frm (if innodb_file_per_table=ON then table.ibd too) files, put it into the database directory and drop the table.

Old versions of InnoDB might complain about mismatching SPACENO. Then check how to fix InnoDB dictionary

Upvotes: 3

Related Questions