InnoDB: Cannot open table from the internal data dictionary of InnoDB though the .frm file for the table exists

Question

I am running Windows 10 with XAMPP and several dozen Drupal sites installed on localhost. Everything has been working fine for months.

This morning I performed a Windows restore from a restore point two days ago to get rid of an unwanted Windows update. After I did that, my MySQL stopped working. I tried deleting the file ibdata1 (I now know that that was a bad idea), but when things got worse, I restored the initial ibdata1 that I'd deleted. All the table data (.frm and .ibd files) is still in C:\xampp\mysql\data. Now MySQL will at least start, but all the tables are "gone"... I can load phpMyAdmin in the browser, and when I drop down the databases at left, all the tables show... but when I try to click on one, it tells me "table not found."

In mysql_error.log there are several errors like InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. There is a URL mentioned in the error message that supposedly tells you how to resolve this issue, but it is uninformative.

I don't think ibdata1 is corrupted... and all the articles regarding recovering data are complicated to a degree that is insanely out of proportion to the problem. No one has a simple spelled-out solution. Am I Googling wrong? This has happened to me at least twice before, and each time the problem spontaneously resolved itself before I was able to start following the 10-hour-long procedures I found online. Not this time, unfortunately. Surely I'm missing something simple. I had hundreds and hundreds of tables in a few dozen databases, and it seems hopeless to try to find the schema for each one and manually plug it in, which is what most recovery articles seem to suggest.

Shouldn't the database just know how to read the .frm and .ibd files, and, you know... work? It feels like there's just one value out of place somewhere, and if I could just find it, everything would just "pop" back into place.

Answer 1

What did the work for us was this link (Restoring Orphan File-Per-Table ibd Files).

In brief, you do:

ALTER TABLE ..... DISCARD TABLESPACE;

For each affected table. Then, for each affected table:

ALTER TABLE .... IMPORT TABLESPACE; SHOW WARNINGS; 

You could do that on the same schema or create a new schema in other database and copy all *.ibd files. In this case:

• Create the new schema with table definitions.
• Do the discard tablespace for all tables.
• Copy the new *.ibd files to the right folder of new schema.
• Do de import tablespace for all tables.

IMPORTANT! It's better that the new schema has the same name as the old one.

Answer 2

What worked for me:

https://dba.stackexchange.com/a/42932

My dbs was InnoDB, so there is a problem with id and when upgrading the macos, I got this. I will quote the main part:

You must have:

-ibdata1

-ib_logfile0

-ib_logfile1

-.FRM files from your mysql_database folder

-Fresh installation of MAMP / MAMP Pro that you are willing to destroy (if need be)

1. SSH into your web server (dev, production, no difference) and browse to your mysql folder (mine was at /var/lib/mysql for a Plesk installation on Linux)
2. Compress the mysql folder
3. Download an archive of mysql folder which should contain all mySQL databases, whether MyISAM or innoDB (you can scp this file, or move this to a downloadable directory, if need be)
4. Install MAMP (Mac, Apache, MySQL, PHP)
5. Browse to /Applications/MAMP/db/mysql/
6. Backup /Applications/MAMP/db/mysql to a zip archive (just in case)

7. Copy in all folders and files included in the archive of the mysql folder from the production server (mt Plesk environment in my case) EXCEPT DO NOT OVERWRITE:

   -/Applications/MAMP/db/mysql/mysql/

   -/Applications/MAMP/db/mysql/mysql_upgrade_info

   -/Applications/MAMP/db/mysql/performance_schema