Not the answer you need?
Register and ask your own question!

Foreign key and table lost after mysqld crash

OlivierDOlivierD EntrantCurrent User Role Beginner
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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.