How to Fix MySQL Error 1146: Table Doesn't Exist

ERROR 1146 (42S02): Table 'database.table_name' doesn't exist is raised when MySQL cannot find a table in the data dictionary. The table either has never been created, was dropped, or the query references the wrong database or a misspelled name.

Impact

Any query (SELECT, INSERT, UPDATE, DELETE) or DDL (ALTER TABLE, DROP TABLE) targeting a non-existent table fails immediately. In application code this usually surfaces as an unhandled exception. In migration scripts, it halts the migration.

Common Causes

1. Wrong database selected: the session is in a different database than expected, USE wrong_db was called, or no database was selected
2. Table was never created: a migration did not run, or the CREATE TABLE was skipped by an error
3. Case sensitivity mismatch: on Linux (case-sensitive filesystem), users and Users are different tables; lower_case_table_names setting controls this
4. Table was accidentally dropped and the application still references it
5. lower_case_table_names mismatch between development (macOS, case-insensitive) and production (Linux, case-sensitive)
6. InnoDB data dictionary inconsistency: the .ibd file exists but the data dictionary entry does not, or vice versa (after a crash or improper restore)
7. Using the wrong connection string or environment variable pointing to a different database
8. Partitioned table partition missing: the table header exists but a partition's data file is gone
9. View referencing a base table that was dropped

Troubleshooting and Resolution Steps

1. Verify which database the session is using:

   SELECT DATABASE();
   SHOW DATABASES;
   USE mydb;
   

2. Check whether the table exists in any database:

   SELECT table_schema, table_name, engine, create_time
   FROM information_schema.tables
   WHERE table_name = 'orders'
   ORDER BY table_schema;
   

   If it appears in a different schema, your query is using the wrong database context.

3. List all tables in the current database:

   SHOW TABLES;
   SHOW TABLES LIKE '%order%';
   

4. Check for case-sensitivity issues:

   SHOW VARIABLES LIKE 'lower_case_table_names';
   

   • 0 = names stored as given, comparisons case-sensitive (Linux default)
   • 1 = names stored lowercase, comparisons case-insensitive (Windows default)
   • 2 = names stored as given, comparisons case-insensitive (macOS default)

   If lower_case_table_names=0 (Linux production) and the table was created as Orders but the query uses orders, MySQL cannot find it:

   -- Table created as:
   CREATE TABLE Orders (...);
   
   -- Query fails on case-sensitive systems:
   SELECT * FROM orders;   -- 1146 on Linux
   
   -- Fix: use the exact case
   SELECT * FROM Orders;
   
   -- Long-term fix: rename table to lowercase
   RENAME TABLE Orders TO orders;
   

5. Re-run migrations if the table was never created:

   # Check migration status (Flyway example)
   flyway info
   
   # Re-run pending migrations
   flyway migrate
   

   In Django:

   python manage.py showmigrations
   python manage.py migrate
   

6. Recreate a dropped table from your schema definition. If you have version-controlled migrations, replay from the last known-good backup:

   -- Check the binary log for the DROP statement
   mysqlbinlog /var/log/mysql/mysql-bin.000123 | grep -A5 -i "DROP TABLE"
   
   -- Restore from backup if needed
   

7. Fix an InnoDB data dictionary inconsistency (.ibd file exists but table not in dictionary, or vice versa). First check:

   # Does the .ibd file exist on disk?
   ls -la /var/lib/mysql/mydb/orders.ibd
   

   -- Does the table appear in information_schema?
   SELECT * FROM information_schema.tables WHERE table_name = 'orders';
   
   -- If .ibd exists but table is not in dictionary, import the tablespace:
   CREATE TABLE orders (...) ENGINE=InnoDB;   -- create with the original DDL
   ALTER TABLE orders DISCARD TABLESPACE;
   -- Copy the .ibd file to the data directory
   ALTER TABLE orders IMPORT TABLESPACE;
   

8. Fix a view that references a dropped table:

   -- Find views with broken references
   SELECT table_schema, table_name, view_definition
   FROM information_schema.views
   WHERE table_schema = 'mydb';
   
   -- Drop and recreate the view, or fix the underlying table reference
   DROP VIEW IF EXISTS customer_summary;
   CREATE VIEW customer_summary AS SELECT ...;  -- using the correct table
   

9. Qualify table names with the schema in queries to avoid relying on the session's current database:

   -- Explicit schema qualification, always works regardless of USE
   SELECT * FROM mydb.orders WHERE id = 1;
   

Additional Information

• lower_case_table_names must be set consistently across all MySQL instances in a replication topology. Changing it on a running server that already has data requires exporting all data, setting the variable, and reimporting. It cannot be changed online on MySQL 8.0 after initialization.
• On macOS with the default lower_case_table_names=2, code that works locally may fail in production on Linux (lower_case_table_names=0) if table names differ in case. Enforce lowercase table names in migrations to prevent this.
• InnoDB stores each table's data in datadir/db_name/table_name.ibd (with innodb_file_per_table=ON). If a .ibd file is deleted outside of MySQL (e.g., by mistake with rm), the table appears in information_schema but all queries against it fail with error 1146 or a different InnoDB-level error.

Frequently Asked Questions

Q: The table exists in information_schema.tables but queries return error 1146. How? A: This is an InnoDB data dictionary inconsistency. The metadata exists but the physical tablespace file (.ibd) is missing or corrupt. Use SHOW TABLE STATUS LIKE 'tablename'\G, it will show an error for the engine if the file is missing. Restore from backup.

Q: After restoring a backup, some tables return error 1146. Why? A: If the backup was taken as a physical copy (file copy) without InnoDB being in a consistent state, the data dictionary may be out of sync with the .ibd files. Always use mysqldump (logical backup), xtrabackup --prepare, or take a snapshot only after MySQL is stopped or while using mysqldump --single-transaction.

Q: My ORM generates table names differently depending on the environment. How do I standardize? A: Explicitly set the db_table Meta option (Django), __tablename__ (SQLAlchemy), or equivalent in your ORM to a lowercase explicit string, not a derived class name. Relying on class name → table name inference can produce different names on different platforms.

Q: Can I get a list of all missing tables (referenced in views or FKs but not existing)? A: For foreign keys:

SELECT referenced_table_schema, referenced_table_name
FROM information_schema.referential_constraints
WHERE referenced_table_name NOT IN (
    SELECT table_name FROM information_schema.tables
    WHERE table_schema = referenced_table_schema
);

For views: inspect VIEW_DEFINITION in information_schema.views and parse referenced table names.