Fix: MySQL ERROR 1146 (42S02): Table 'database.table' doesn't exist

The Error

You run a query against your MySQL database and get:

ERROR 1146 (42S02): Table 'mydb.users' doesn't exist

Or through an application framework:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mydb.users' doesn't exist

django.db.utils.ProgrammingError: (1146, "Table 'mydb.django_session' doesn't exist")

Error: ER_NO_SUCH_TABLE: Table 'mydb.users' doesn't exist

MySQL is telling you it cannot find the table you referenced. The table either does not exist in the current database, was created with different casing, lives in a different database, or its tablespace is corrupted.

Why This Happens

MySQL stores tables as files on disk. When you query a table, MySQL looks up its metadata in the data dictionary (MySQL 8.0+) or the .frm files (MySQL 5.7 and earlier). If the lookup fails, you get ERROR 1146.

The root causes fall into several categories:

• Wrong database selected. You connected to mysql or test instead of your application database. The table exists, just not where you are looking.
• Case sensitivity mismatch. On Linux, MySQL table names are case-sensitive by default. A table created as Users cannot be queried as users. On Windows and macOS, table names are case-insensitive by default.
• Migrations not run. Your ORM or migration tool has not created the table yet.
• Table was dropped or renamed. Someone ran DROP TABLE or RENAME TABLE and the table no longer exists under its old name.
• InnoDB tablespace corruption. The .ibd file was deleted or corrupted, but MySQL still has metadata referencing the table.
• Docker volume not persisted. The MySQL container restarted and the data directory was not mounted to a persistent volume.
• Imported from a different OS. A database dump created on Windows (case-insensitive) was imported to Linux (case-sensitive), and the table names don’t match the queries.

Fix 1: Check Which Database You Are Connected To

The most common and most overlooked cause. You might be in the wrong database entirely.

Check your current database:

SELECT DATABASE();

If the result is NULL or a different database than expected, switch:

USE mydb;

Then retry your query:

SELECT * FROM users;

List all databases to find the right one:

SHOW DATABASES;

When connecting from the command line, always specify the database:

mysql -u myuser -p mydb

In connection strings, verify the database name:

mysql://myuser:password@localhost:3306/mydb

This is similar to the PostgreSQL issue where connecting to the wrong database causes “relation does not exist” errors. See Fix: PostgreSQL relation does not exist for the PostgreSQL equivalent.

Common Mistake: Many MySQL installations have multiple databases — test, mysql, information_schema, performance_schema, and your application database. If your client defaults to no database or the wrong one, every query fails with 1146. Always check SELECT DATABASE() first.

Fix 2: Check Table Name Case Sensitivity

MySQL’s case sensitivity for table names depends on the operating system and the lower_case_table_names system variable.

Check your setting:

SHOW VARIABLES LIKE 'lower_case_table_names';

If the value is 0 (Linux default), these are different tables:

SELECT * FROM Users;  -- looks for 'Users'
SELECT * FROM users;  -- looks for 'users'
SELECT * FROM USERS;  -- looks for 'USERS'

List all tables to see the exact names:

SHOW TABLES;

If you see Users but your code queries users, either rename the table:

RENAME TABLE Users TO users;

Or update your queries to match the exact case.

Warning: You can only set lower_case_table_names when initializing the MySQL data directory. Changing it on an existing database with mixed-case table names can cause data loss. If you need to change it, dump all databases, reinitialize, and reimport.

Pro Tip: For cross-platform compatibility, always use lowercase table names in your schema definitions. This avoids surprises when moving between Linux, macOS, and Windows. Many ORMs like Django and Rails use lowercase by default.

Fix 3: Verify the Table Exists

Before debugging further, confirm whether the table actually exists:

SHOW TABLES LIKE 'users';

Or search across all databases:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'users';

If the query returns nothing, the table genuinely does not exist. You need to create it — either by running migrations (Fix 4) or manually.

If the query returns a result in a different database, you are connected to the wrong one (Fix 1).

You can also check from the command line:

mysql -u myuser -p -e "SHOW TABLES" mydb | grep users

Fix 4: Run Pending Migrations

If you use an ORM or migration framework, the table may not have been created yet.

Django:

python manage.py makemigrations
python manage.py migrate

Django’s migration system creates tables incrementally. If you cloned a project and skipped migrate, none of the tables exist. This is covered in detail in Fix: Django OperationalError: no such table.

Rails:

rails db:migrate

Laravel:

php artisan migrate

Sequelize (Node.js):

npx sequelize-cli db:migrate

Prisma:

npx prisma migrate deploy

Flyway:

flyway migrate

After running migrations, verify the table was created:

SHOW TABLES LIKE 'users';

If migrations fail, check the error output. A common issue is the migration trying to reference another table that doesn’t exist yet — migration ordering matters.

Fix 5: Recover from InnoDB Tablespace Issues

If the table’s .ibd file (InnoDB tablespace file) was manually deleted or corrupted while MySQL still has metadata for the table, you get a particularly confusing situation: MySQL knows about the table but cannot access it.

The error might look like:

ERROR 1146 (42S02): Table 'mydb.users' doesn't exist

Or:

ERROR 1812 (HY000): Tablespace is missing for table 'mydb/users'

Step 1: Check if the tablespace file exists:

ls /var/lib/mysql/mydb/users.ibd

If the file is missing but MySQL has metadata, you need to clean up:

DROP TABLE IF EXISTS users;

If DROP TABLE also fails, use:

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS users;
SET FOREIGN_KEY_CHECKS = 1;

Step 2: Recreate the table and restore data from a backup.

If you have a SQL dump:

mysql -u myuser -p mydb < backup.sql

In MySQL 8.0+, tablespace management is handled by the data dictionary, which reduces these issues. If you are on MySQL 5.7, consider upgrading.

Fix 6: Fix Docker Volume Issues

If MySQL runs in a Docker container without a persistent volume, all data is lost when the container restarts. The tables existed in the old container but not in the new one.

Check if your Docker setup uses a volume:

docker inspect mysql-container | grep -A 5 Mounts

If no volume is mounted to /var/lib/mysql, your data is ephemeral. Fix it by adding a volume:

# docker-compose.yml
services:
  db:
    image: mysql:8.0
    volumes:
      - mysql_data:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: mydb

volumes:
  mysql_data:

Then recreate the container:

docker compose down
docker compose up -d

You will need to reimport your data or run migrations again. Going forward, the volume ensures data persists across container restarts.

If your Docker containers are running into other issues, check Fix: Docker no space left on device for disk-related problems.

Fix 7: Handle Cross-OS Imports

When you export a database from Windows or macOS (lower_case_table_names = 1 or 2) and import it on Linux (lower_case_table_names = 0), table names may not match.

On Windows, the dump might contain:

CREATE TABLE `Users` (...);

Your application queries:

SELECT * FROM users;

On Windows this works (case-insensitive). On Linux it fails (case-sensitive).

Fix the dump before importing:

sed -i 's/CREATE TABLE `Users`/CREATE TABLE `users`/g' dump.sql

Or convert all table names to lowercase in the dump:

sed -i 's/CREATE TABLE `\([^`]*\)`/CREATE TABLE `\L\1`/g' dump.sql

A safer approach is to set lower_case_table_names = 1 on the Linux server before initializing the data directory:

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
lower_case_table_names = 1

Then initialize and import. This makes the Linux server behave like Windows for table name casing.

Fix 8: Check for Views and Temporary Tables

The error message says “Table” but the object might be a view or a temporary table.

Views appear in SHOW TABLES but not in SHOW CREATE TABLE if permissions are restricted:

SHOW FULL TABLES WHERE Table_type = 'VIEW';

If the view references a table that was dropped, querying the view produces the 1146 error — but the error points to the underlying table, not the view itself.

Temporary tables exist only in the session that created them:

CREATE TEMPORARY TABLE staging (id INT, data TEXT);

-- This works in the same session
SELECT * FROM staging;

-- Another session gets:
-- ERROR 1146: Table 'mydb.staging' doesn't exist

If your application creates temporary tables and you see this error intermittently, check if your connection pool is recycling connections. A temporary table created in one connection does not exist in another. This is especially relevant when using connection poolers or MySQL connection limits.

Fix 9: Check User Permissions

In some MySQL configurations, a user without SELECT privilege on a table gets ERROR 1146 instead of an access denied error. MySQL hides the table’s existence from unauthorized users.

Check your privileges:

SHOW GRANTS FOR CURRENT_USER();

If you see limited permissions, ask the database administrator to grant access:

GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.users TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

Connect as root to verify the table exists:

mysql -u root -p -e "SHOW TABLES" mydb

If the table appears when connected as root but not as your application user, permissions are the issue. This is similar to how MySQL access denied errors can manifest in unexpected ways.

Fix 10: Debug with INFORMATION_SCHEMA

When nothing else works, query INFORMATION_SCHEMA directly. It contains metadata about every table in every database:

-- Find the table across all databases
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%users%';

-- List all tables in a specific database
SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydb'
ORDER BY TABLE_NAME;

-- Check if the table exists but with different casing
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydb'
AND UPPER(TABLE_NAME) = 'USERS';

If INFORMATION_SCHEMA shows the table but SHOW TABLES does not, you likely have a permissions issue or a corrupted data dictionary.

Still Not Working?

If none of the fixes above resolved the error, try these additional steps:

Check MySQL error log. The error log often contains more detail than the client error:

sudo tail -100 /var/log/mysql/error.log

Look for tablespace errors, crash recovery messages, or permission warnings.

Check for typos. This sounds obvious, but single-character typos are the most common cause:

-- Wrong
SELECT * FROM user;     -- singular
SELECT * FROM uesrs;    -- typo

-- Right
SELECT * FROM users;    -- correct

Check the MySQL socket connection. If you see both 1146 errors and connection issues, you might be connecting to the wrong MySQL instance entirely. See Fix: MySQL can’t connect through socket for troubleshooting.

Check for foreign key constraints. If you are trying to create a table that references another table via a foreign key, and that referenced table does not exist, MySQL throws ERROR 1146. Create the referenced table first, or disable foreign key checks temporarily:

SET FOREIGN_KEY_CHECKS = 0;
-- CREATE or ALTER TABLE statements
SET FOREIGN_KEY_CHECKS = 1;

Restore from backup. If the table was accidentally dropped and you have a backup:

# Restore a single table from a mysqldump file
mysql -u root -p mydb < backup.sql

If you used mysqldump with --single-transaction, the backup is consistent and safe to restore.

Check binary logs for accidental drops. If you have binary logging enabled, you can find when and who dropped the table:

mysqlbinlog /var/log/mysql/mysql-bin.000001 | grep -i "drop table"

Upgrade MySQL. MySQL 5.7 stored table metadata in .frm files, which were prone to corruption. MySQL 8.0 uses a transactional data dictionary that is much more resilient. If you are still on 5.7, consider upgrading.