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.