3

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.

  • One: 1 million rows is not hugh, it is tiny. Two: learn the more important lesson: backups. Db important? I take a log backup every 15 minutes, full backup twice per day. And that is on a smalll 4tb database. – TomTom May 4 '18 at 23:32
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.