🚨 MySQL Error Guide: 'ERROR 1146 (42S02)' Table Doesn't Exist

Overview

ERROR 1146 (42S02) means MySQL could not find the table you referenced in the current (or named) database:

ERROR 1146 (42S02): Table 'appdb.user_sessions' doesn't exist

The message gives the fully qualified name as MySQL resolved it (appdb.user_sessions). SQLSTATE 42S02 is the standard “base table not found” class. The table usually does exist somewhere — the failure is almost always a mismatch: wrong database, wrong case, a migration that never ran on this server, or a privilege that makes a real table invisible. A genuinely dropped or corrupt-on-disk table is the less common case.

It appears on any statement that names a table: SELECT, INSERT, JOIN, SHOW CREATE TABLE, and so on.

Symptoms

• Table '<db>.<name>' doesn't exist on a query that used to work.
• The table is present in one environment but missing in another.
• After a deploy, the app references a table the database does not have.
• Case-only differences between the query and the actual table name.

mysql -u app -p appdb -e "SELECT COUNT(*) FROM user_sessions;"

ERROR 1146 (42S02): Table 'appdb.user_sessions' doesn't exist

Common Root Causes

1. Wrong database selected

The table exists, but in a different schema than the one the connection defaulted to.

SELECT table_schema, table_name FROM information_schema.tables
WHERE table_name = 'user_sessions';

+--------------+----------------+
| table_schema | table_name     |
+--------------+----------------+
| appdb_v2     | user_sessions  |
+--------------+----------------+

The table lives in appdb_v2, but the query ran against appdb. Selecting the right database (or qualifying the name) fixes it.

2. Case sensitivity (lower_case_table_names)

On Linux, table names are case-sensitive by default (lower_case_table_names=0). User_Sessions and user_sessions are different tables.

SHOW VARIABLES LIKE 'lower_case_table_names';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'appdb' AND LOWER(table_name) = 'user_sessions';

+----------------+
| table_name     |
+----------------+
| User_Sessions  |
+----------------+

The real name is User_Sessions; a query for user_sessions fails on a case-sensitive server. This commonly bites migrations from macOS/Windows (case-insensitive) to Linux.

3. A migration never ran on this server

The application expects a table that a schema migration was supposed to create, but the migration did not run here (failed deploy, skipped replica, fresh environment).

SELECT version, name FROM schema_migrations ORDER BY version DESC LIMIT 3;

+----------------+-------------------------+
| version        | name                    |
+----------------+-------------------------+
| 20260601090000 | add_orders_index        |
| 20260520120000 | create_invoices         |
| 20260515110000 | create_users            |
+----------------+-------------------------+

The create_user_sessions migration is absent — it was never applied. Running pending migrations creates the table.

4. The table was dropped (or never existed)

A DROP TABLE (manual, or a migration rollback) removed it.

SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_name = 'user_sessions';

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

Zero rows confirms it is genuinely gone from the catalog — restore from backup or re-create it.

5. Orphaned InnoDB table (files vs data dictionary)

The .ibd file may exist on disk while the data dictionary disagrees (after a botched copy or crash), so the table is “missing” to SQL.

ls -l /var/lib/mysql/appdb/user_sessions.ibd 2>/dev/null
mysql -u root -p appdb -e "SELECT 1 FROM user_sessions LIMIT 1;"

-rw-r----- 1 mysql mysql 114688 Jun 23 09:00 /var/lib/mysql/appdb/user_sessions.ibd
ERROR 1146 (42S02): Table 'appdb.user_sessions' doesn't exist

The file is present but the table is not in the dictionary — an orphaned tablespace needing import/discard recovery.

6. A privilege issue hiding the table

In some setups a user without any privilege on a table may not see it via the client’s table list, leading to confusion that looks like 1146 (and direct access fails on grants).

SHOW GRANTS FOR CURRENT_USER();

+----------------------------------------------------------+
| Grants for app@%                                         |
+----------------------------------------------------------+
| GRANT SELECT, INSERT ON `appdb`.`orders` TO `app`@`%`    |
+----------------------------------------------------------+

The grant covers orders but not user_sessions — the app account cannot use that table even if it exists.

Diagnostic Workflow

Step 1: Read the qualified name MySQL reported

ERROR 1146 (42S02): Table 'appdb.user_sessions' doesn't exist

Note both the schema (appdb) and table — the schema part reveals wrong-database problems instantly.

Step 2: Search every schema for the table

SELECT table_schema, table_name FROM information_schema.tables
WHERE LOWER(table_name) = 'user_sessions';

If it appears in another schema or with different casing, you have your answer (Steps 1/2 root causes).

Step 3: Check case sensitivity and exact spelling

SHOW VARIABLES LIKE 'lower_case_table_names';
SHOW TABLES FROM appdb LIKE 'user%';

Compare the exact stored name to what the query used.

Step 4: Verify migrations and grants

SELECT MAX(version) FROM schema_migrations;
SHOW GRANTS FOR CURRENT_USER();

A missing migration (table truly absent) or a missing grant (table present but inaccessible) both surface here.

Step 5: For orphaned tablespaces, recover

-- If the .ibd exists but the table is missing from the dictionary:
CREATE TABLE user_sessions (...) ENGINE=InnoDB;  -- matching original DDL
ALTER TABLE user_sessions DISCARD TABLESPACE;
-- copy the original .ibd into place, then:
ALTER TABLE user_sessions IMPORT TABLESPACE;

This reattaches an orphaned .ibd to a freshly created table definition.

Example Root Cause Analysis

A service deployed fine to staging but throws on production:

ERROR 1146 (42S02): Table 'appdb.AuditLog' doesn't exist

The DBA searches all schemas case-insensitively:

SELECT table_schema, table_name FROM information_schema.tables
WHERE LOWER(table_name) = 'auditlog';

+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| appdb        | auditlog   |
+--------------+------------+

The real table is auditlog (all lowercase), but the application’s ORM emits AuditLog. Staging ran lower_case_table_names=1 (case-insensitive) so it matched; production runs Linux default 0 (case-sensitive), so AuditLog does not match auditlog.

Fix options: standardize the server setting across environments, or normalize the model/table name to a single case. The team renames the table reference to lowercase in the ORM mapping to match the stored name, and the error clears. Long-term they standardize on lower_case_table_names=1 fleet-wide to remove the footgun.

Prevention Best Practices

• Standardize lower_case_table_names across every environment (set it at initialization — it cannot be changed safely afterward) so case behavior never differs between dev, staging, and production.
• Always fully qualify tables (db.table) or set the default schema explicitly in the connection, so a wrong-database default cannot masquerade as a missing table.
• Gate deploys on migration status: the app should refuse to start, or a CI check should fail, if pending migrations have not been applied to the target database.
• Use consistent naming conventions (e.g. all lowercase, snake_case) for tables to avoid case mismatches between ORMs and the database.
• Grant the application account access to exactly the tables it needs and verify grants as part of provisioning, so access gaps are caught early.
• For quick triage of a 1146 after a deploy, the free incident assistant can flag whether it is a schema, case, or migration mismatch. More in MySQL guides.

Quick Command Reference

-- Find the table in any schema, any case
SELECT table_schema, table_name FROM information_schema.tables
WHERE LOWER(table_name) = 'user_sessions';

-- Case-sensitivity setting and exact names
SHOW VARIABLES LIKE 'lower_case_table_names';
SHOW TABLES FROM appdb LIKE 'user%';

-- Is it truly absent?
SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_name = 'user_sessions';

-- Migration + grant checks
SELECT MAX(version) FROM schema_migrations;
SHOW GRANTS FOR CURRENT_USER();

-- Orphaned tablespace recovery
ALTER TABLE user_sessions DISCARD TABLESPACE;
ALTER TABLE user_sessions IMPORT TABLESPACE;

Conclusion

ERROR 1146 (42S02) means MySQL could not resolve the table name in the schema it looked in. The usual root causes:

1. The connection defaulted to the wrong database.
2. Case sensitivity (lower_case_table_names=0) making names differ by case.
3. A schema migration that never ran on this server.
4. The table was genuinely dropped or never created.
5. An orphaned InnoDB tablespace where the .ibd and the dictionary disagree.
6. A privilege gap making a real table inaccessible.

Read the qualified name in the error, search information_schema.tables across schemas and cases, then decide between selecting the right database, fixing casing, running migrations, or recovering the table.