ERROR 1146 (42S02): Table 'db.table' doesn't exist

Fix MySQL 'Table doesn't exist' Error for Good

EP
Erropedia Team
📅 May 25, 2026 📅 Updated Jul 31, 2026 👁 11 views Database Errors Intermediate
MySQL says your table doesn't exist, but you know it's there. Here's the real fix: check the table definition cache and rebuild if needed.

The Short Fix

You're looking at ERROR 1146 (42S02): Table 'your_db.your_table' doesn't exist. You know the table's there—maybe you just ran a SELECT against it ten seconds ago. Happened to a client of mine last month with a WordPress site running WooCommerce. Orders table vanished mid-transaction. Panic mode.

Here's the fix that works 90% of the time:

-- Step 1: Check if the table physically exists
SHOW TABLES LIKE 'your_table';

-- Step 2: If it shows up, flush the table cache
FLUSH TABLES your_table;

-- Step 3: Try the query again
SELECT * FROM your_table LIMIT 1;

If that doesn't work, you're dealing with a corrupted table definition in the InnoDB data dictionary. You'll need to rebuild it:

-- Step 1: Backup the table structure
mysqldump --no-data your_db your_table > your_table_struct.sql

-- Step 2: Drop the table (yes, really—this removes the corrupt dictionary entry)
DROP TABLE your_table;

-- Step 3: Recreate from backup
mysql your_db < your_table_struct.sql

-- Step 4: Insert data from a backup (or reload from your latest dump)

I know dropping a table feels wrong when you're trying to fix it. But the InnoDB dictionary entry is corrupted, and the only way to clean it is to remove the bad record and recreate it fresh. As long as you have a backup, you're fine. And if you don't have a backup—well, now you know why you need one.

Why This Happens

MySQL's InnoDB engine stores table definitions in two places: the .frm files (or .ibd files in newer versions) and the internal data dictionary stored in the system tablespace (ibdata1). When these get out of sync—usually after an improper shutdown, a disk full error, or a crash—MySQL sees the data dictionary entry but the underlying file is missing or corrupted, or vice versa.

In the client case I mentioned, their server ran out of disk space during a peak sales hour. MySQL wrote part of the table definition, then died. Next startup: table 'doesn't exist' even though the .ibd file was fine. The dictionary entry was half-baked.

The FLUSH TABLES trick works because it forces MySQL to close and reopen the table, re-reading the definition from disk. If the file's intact, that's often enough. But when the dictionary itself is corrupt, you have to rebuild it.

Less Common Variations

Not every case is identical. Here are a few variations I've seen:

Variation 1: Table Shows in SHOW TABLES but Not in INFORMATION_SCHEMA

You see the table in SHOW TABLES but SELECT * FROM INFORMATION_SCHEMA.TABLES doesn't list it. This usually means the .frm file is fine but the InnoDB dictionary entry was never created or got dropped. Try ALTER TABLE your_table ENGINE=InnoDB; or, if that fails, dump the structure, drop, and recreate as above.

Variation 2: Table Exists in One Database but Not When Referenced with Database Prefix

You can select from the table directly, but SELECT * FROM db.table errors out. I've seen this when a user has a database name with special characters or when the MySQL data directory got messed up. Check the actual directory name under /var/lib/mysql/. It must match the database name exactly, case-sensitive on Linux.

Variation 3: Error After a MySQL Upgrade or Migration

When you move MySQL to a newer version or different host, sometimes the table definition format changes. Older .frm files don't always play nice with newer servers. The fix here is to run mysql_upgrade or re-import the table from a dump.

Variation 4: Locked Table After a Long-Running Query

This isn't really a missing table, but the error message is identical. A long-running transaction can lock the table definition. Kill the offending process with SHOW PROCESSLIST and KILL <thread_id>;.

Prevention

You can't prevent every corruption, but you can stack the odds in your favor:

  • Enable innodb_force_recovery only as a last resort — it lets MySQL start in read-only mode, but it can mask the underlying issue. Set it to 1, diagnose, then set back to 0.
  • Backup daily — and test your backups. A backup you can't restore isn't a backup.
  • Use innodb_file_per_table — it's the default in MySQL 5.6+, but if you're on an old version, enable it. Each table gets its own .ibd file, so corruption doesn't spread.
  • Monitor disk space — set up alerts at 80% full. A full disk during a write is the #1 cause of table corruption I've seen.
  • Upgrade gracefully — always run mysql_upgrade after a major version upgrade. And take a full dump before, just in case.

One last thing: if you're on a shared hosting plan that uses MySQL with InnoDB and you get this error repeatedly, it's usually because the host's ibdata1 is bloated or corrupt. You can't fix their server, but you can export your data and move to a host that knows what they're doing. I've had three clients move away from the same budget host because of exactly this problem.

Related Errors in Database Errors
0X8004D013 XACT_E_XTIONEXISTS (0x8004D013) - Fix for DTC Enlistment Error 0X8004D026 XACT_E_XA_TX_DISABLED (0X8004D026) — XA Transactions Disabled 0X00000559 Fix ERROR_RXACT_INVALID_STATE (0x00000559) in Registry Transactions could not fork new process for connection: Cannot allocate memory Fix 'Cannot allocate memory' in PostgreSQL on Linux
MySQL error 1146 table doesn't exist MySQL table missing table definition cache database table not found MySQL repair table ibdata corruption InnoDB dictionary mismatch MySQL table hidden MySQL table drop error MySQL table access error

Was this solution helpful?

EP
Erropedia Team
Tech Support Editors
The Erropedia editorial team researches and documents real-world tech errors from across Windows, Linux, macOS, networking, databases, cloud platforms, and more. Every solution is reviewed for accuracy and updated as software and systems evolve.