MySQL 'Table Doesn't Exist' Despite It Being There — Real Fix

The 30-Second Fix — Check Your Case and Database Name

You're running a query, you see the table in SHOW TABLES, and MySQL still throws error 1146. Had a client last month whose entire CRM stopped because they migrated from Windows to Linux and every table name had mixed case.

First thing: check case sensitivity. On Linux, MySQL table names are case-sensitive by default. On Windows, they're not. If you built your database on Windows and moved to Linux, users and Users are two different tables. Run this:

SHOW VARIABLES LIKE 'lower_case_table_names';

If it returns 0 on Linux and your table names have uppercase letters, that's your problem. The real fix is to set lower_case_table_names=1 in your my.cnf and restart MySQL. But be careful — changing this after data exists can cause issues. Backup first.

Also check your USE statement. I've debugged this for an hour only to realize they were connected to the wrong database. Run SELECT DATABASE(); to confirm.

The Moderate Fix (5 Minutes) — Rebuild the .frm File

If case isn't the issue, the table's .frm file (the table definition file) might be corrupted. This happens a lot after a crash or a kill -9 on mysqld. You'll see the table in INFORMATION_SCHEMA but can't SELECT from it.

Here's the fix for MyISAM tables (less common now but still exists):

1. Stop MySQL.
2. Go to the database folder (usually /var/lib/mysql/yourdb/).
3. Find the .frm file for the broken table.
4. Rename it to something else (like tablename.frm.bak).
5. Start MySQL.
6. Run: CREATE TABLE tablename (id INT) ENGINE=MyISAM; — this creates a fresh .frm.
7. Stop MySQL again, delete the new .frm, rename your backup back to original name.
8. Restart MySQL. Should work.

For InnoDB tables, this approach doesn't work because InnoDB stores metadata in the system tablespace. But you can still recover data — keep reading.

The Advanced Fix (15+ Minutes) — InnoDB Force Recovery

InnoDB corruption is trickier. I had a client whose entire WordPress site collapsed because a power outage hit during an ALTER TABLE. Every single table returned error 1146 despite showing in the list.

Step one: add this to your my.cnf under [mysqld]:

innodb_force_recovery = 1

Restart MySQL. Try a SELECT again. If it works, dump your data immediately:

mysqldump -u root -p yourdb tablename > tablename.sql

If recovery level 1 doesn't work, bump it to 2, then 3, up to 6. Each level disables more InnoDB operations. At level 4, you can't do SELECTs anymore — only dump with --no-data and rebuild. Level 6 means you're basically reading raw pages.

Warning: Never run production with innodb_force_recovery set. Remove it after dumping. Then drop and recreate the table from your backup.

When All Else Fails — Check the .ibd File

If you're using file-per-table InnoDB (which you probably are), each table has a .ibd file. If that file is missing or zero bytes, MySQL sees the .frm but can't open the data. Check with:

ls -la /var/lib/mysql/yourdb/tablename.ibd

If the file is 0 bytes, you're looking at a restore from backup. No shortcut here. But if it's a decent size, you can try copying a fresh .frm from a known good table structure (same schema) and running ALTER TABLE ... IMPORT TABLESPACE. That's expert-level though — only attempt if you have a backup already.

One More Thing — The "Table Doesn't Exist" After ALTER TABLE

This one's sneaky. If you ran ALTER TABLE and got error 2013 (lost connection), the table might be in a half-created state. Check SHOW TABLE STATUS — if the table shows as NULL or has weird engine values, you need to drop it and restore. Don't waste time trying to fix the .frm in this case.

Real scenario last week: a dev ran ALTER TABLE on a 50GB table without --quick. Timeout after 2 hours. Table gone. No backup. They learned that day what a replication slave is for.

Bottom line: error 1146 is almost always one of these three things — case sensitivity, corrupted file, or incomplete ALTER. Start with the 30-second check, move to the moderate fix, and only go to force recovery if you're desperate. And for the love of all that's holy, back up your databases.