Mysql – Table was deleted during ALTER, how to recover

foreign keyinnodbMySQLrecovery

I tried to ALTER a table to add a FOREIGN KEY as:

ALTER TABLE artists ADD 
FOREIGN KEY(country_id) REFERENCES countries(country_id) ON DELETE CASCADE;

and I received an error of

ERROR 2013 (HY000): Lost connection to MySQL server during query

I tried twice and the same error. The third time, my table was gone. How can I recover the dropped table? MySQL server (Ver 14.14 Distrib 5.7.21, for Linux (x86_64)) is on localhost.

The entries in other tables which have FK to this table still exist. Thus, I believe the table has not been deleted, but InnoDB map is damaged.

I checked the files, table_name.ibd exists but table_name.frm is missing. I cannot CREATE the table as the TABLESPACE already exists. How can I recover or create the table schema to use the available data? My concern is about the FOREIGN KEYS, which still exist in other tables.

I had a backup, but it is too old to restore the backup. I have to keep the current version. And it is a huge database. The table has about 1 million rows. The ibd file is over 100MB.

Best Answer

Install the same version of MySQL onto another VM. Create the same database. Create that same table; just the schema, no data. Shutdown original MySQL server. Copy the .frm from VM-MySQL to original in proper location. Fix permissions, if necessary. Start original MySQL. This should allow MySQL to see the table.

Related Question