Hello,
I have some trouble with a created table that disappeared and cannot be created again…
Let me explain :
I’ve created table ‘phone_contacts’, perform some ALTER TABLE on it (add colums). Then, I tried adding a foreign key on it (referencing an existing table). At this point, MySQL server crashed (sorry, no log at that point …)
When it came back online, my table was missing.
Running CREATE TABLE failed with error -1.
On disk, I only found the .ibd file, the .frm was missing. I then created a duplicate table phone_contacts2, copied phone_contacts2.frm to phone_contacts.frm : table appeared again.
I issued a DROP TABLE phone_contact (to try to cleanup innodb dictionary), query failed but table was dropped …
Now, instead of having an error -1 when issuing CREATE TABLE, I have error 150 : this is because the foreign key is still out there, blocking the creation …
Now :
- I have no phone_contacts table
- no phone_contacts.* file on disk.
- when trying to create table :
ERROR 1005 (HY000): Can’t create table ‘xxxx.phone_contacts’ (errno: 150)
Syslog :
InnoDB: Error: in ALTER TABLE xxxx.phone_contacts
InnoDB: has or is referenced in foreign key constraints
InnoDB: which are not compatible with the new table definition.
So, it seems my foreign key constraint is still written somewhere, but I don’t know how to drop it.
There is no reference on it on INFORMATION_SCHEMA.TABLE_CONSTRAINTS, so do not know where else to check.
I’m using Percona Server 5.5.39