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.