Fix 'Table Doesn't Exist in Engine InnoDB' Error in MySQL

When This Error Hits

You open your MySQL error log or run a query on a table you know exists — and you see something like this:

ERROR 1813 (HY000): Tablespace for table '`mydb`.`users`' exists. But table doesn't exist in engine.

This usually happens after a server crash, a failed ALTER TABLE, or an incomplete import where the .ibd file got corrupted or deleted but the .frm file survived. I've seen it most often on MySQL 5.7 and 8.0 after a power loss when the server was writing to the shared tablespace.

Root Cause in Plain English

InnoDB stores table definitions in .frm files (or in the data dictionary in MySQL 8.0) and the actual data in .ibd files. When the engine says "table doesn't exist in engine," it means the data dictionary in the InnoDB memory is out of sync with the files on disk. The .frm file is still there, so MySQL thinks the table exists. But the .ibd file is missing or damaged, so InnoDB can't open it. The engine then complains because it can't find the data it was told to expect.

The Fix: Recreate the .ibd File

This fix works for both MySQL 5.7 and 8.0. You'll need command-line access to the MySQL server and the database directory.

1. Back up everything. Copy the whole database directory (usually /var/lib/mysql/ or C:\ProgramData\MySQL\MySQL Server X.Y\Data\) to a safe folder. You never know when a step might go wrong.
2. Drop the tablespace for the broken table. Connect to MySQL as root and run:

ALTER TABLE mydb.users DISCARD TABLESPACE;

This tells InnoDB to forget about the old .ibd file. If you get an error here, skip to step 5.

3. Delete the old .ibd file manually. Go to the database folder and remove the .ibd file for the broken table. For example:

rm /var/lib/mysql/mydb/users.ibd

4. Create a new .ibd file. Run this SQL:

ALTER TABLE mydb.users IMPORT TABLESPACE;

This forces InnoDB to create a fresh .ibd file from the table definition stored in the .frm file (or the data dictionary).

5. If DISCARD TABLESPACE fails — this is common when the engine is totally confused. Instead, do this:
1. Drop the table from MySQL:

DROP TABLE mydb.users;

2. Delete the .frm file and the .ibd file from the database folder manually (if any remain).
3. Recreate the table from a backup or from your schema dump:

SOURCE /path/to/schema_dump.sql;

What to Check If It Still Fails

If the error still shows up after the steps above, here's what I'd check next:

• File permissions. The MySQL user (usually mysql on Linux, NETWORK SERVICE or SYSTEM on Windows) must own the database folder and all files inside. Run chown -R mysql:mysql /var/lib/mysql/mydb and try again.
• Shared tablespace corruption. If multiple tables are affected, the ibdata1 file might be corrupt. Restore from a full backup or use mysqlcheck --repair on all databases.
• Version mismatch. Did you restore from a different MySQL version? The .frm and .ibd files are version-specific. You might need to use mysql_upgrade or recreate the table using a CREATE TABLE statement from the original server.
• InnoDB dictionary mismatch. In rare cases, the internal InnoDB dictionary has leftover entries. Run SET GLOBAL innodb_force_recovery = 1; to start MySQL in recovery mode, then drop the problematic table. Restart MySQL without the recovery flag and re-import the table.

I know this error is infuriating — it's one of those "everything looks fine on disk but MySQL disagrees" problems. But with these steps, you'll get your data back in most cases. Good luck!