How to fix ERROR 1146: Table 'database.table' doesn't exist in MySQL

MySQLINTERMEDIATEHIGHUpdated Dec 25, 2025· 265 views

MySQL Error 1146 (42S02) occurs when a query tries to access a table that does not exist or cannot be found. Common causes include typos in table names, case sensitivity issues on Linux, selecting the wrong database, corrupted table files, and missing InnoDB data dictionary files. Fixes involve verifying table existence, checking case sensitivity, and repairing corrupted files.

Quick fix

Fixes 1146

-- List all tables in the currently selected database:
SHOW TABLES;

-- Or get more details including table type (BASE TABLE vs. VIEW):
SHOW FULL TABLES;

-- Search for a specific table name (case-insensitive on most systems):
SHOW TABLES LIKE 'users';
SHOW TABLES LIKE '%user%';
  1. Verify the table exists by listing all tables in the current database
  2. Select the correct database before querying the table
  3. Check table name case sensitivity and exact spelling
  4. Check filesystem permissions on the MySQL data directory
mysql — error
-- List all tables in the currently selected database:
SHOW TABLES;
-- Or get more details including table type (BASE TABLE vs. VIEW):
SHOW FULL TABLES;
-- Search for a specific table name (case-insensitive on most systems):
SHOW TABLES LIKE 'users';
SHOW TABLES LIKE '%user%';
ERROR 1146: Table 'database.table' doesn't exist

What this error means

MySQL Error 1146 indicates that the database server cannot locate the requested table. This error occurs at the table lookup stage, before the actual query execution begins. The error message format is "Table 'database_name.table_name' doesn't exist". MySQL uses a hierarchical file system to store tables: databases map to directories in the data directory (/var/lib/mysql), and each table corresponds to one or more files (.frm, .ibd, .MYD, .MYI files). When a table cannot be found, either the table file is missing, corrupted, or the table name in your query does not match the actual table name in the database. This error is common during database migrations, after accidental deletions, when switching between case-sensitive and case-insensitive systems, or when the data dictionary becomes out of sync with the actual filesystem. Error 1146 is one of the most straightforward errors to diagnose—either the table exists or it does not—but the root cause may require investigation into file permissions, filesystem corruption, or configuration issues.

Typical symptoms

  • A specific table query fails with "ERROR 1146: Table doesn't exist" while other tables work normally
  • The table worked previously but started failing after a migration, backup restore, or server restart
  • Table appears to exist when using SHOW TABLES, but queries still fail
  • Application errors mentioning a missing table, often during the first query after connection
  • mysqldump fails with error 1146 when trying to export a specific table
  • InnoDB crash recovery reports missing tables that appear to exist in SHOW TABLES output
  • Different behavior on Linux vs. Windows servers for the same table names

Common causes

  1. Typo in the table name—the query references a table name that does not exist in the selected database
  2. Case sensitivity mismatch on Linux/Unix systems where table names are case-sensitive (lower_case_table_names=0)
  3. Selecting the wrong database before the query—using the default database instead of the target database
  4. Table was accidentally dropped or deleted, and you are querying a table that no longer exists
  5. Missing or corrupted .frm, .ibd, or .MYI files in the MySQL data directory that define the table structure
  6. Incomplete or failed database migration, backup restore, or replication sync
  7. InnoDB table references in the data dictionary (ibdata) but .ibd files are missing from the data directory
  8. Permission issues preventing MySQL from reading table files, causing them to be invisible to queries
  9. File name mismatch when exporting to a case-sensitive system and importing to a case-insensitive system (or vice versa)

How to fix "ERROR 1146: Table 'database.table' doesn't exist"

1Verify the table exists by listing all tables in the current database

The first step is to confirm whether the table actually exists in the database.

sql
-- List all tables in the currently selected database:
SHOW TABLES;

-- Or get more details including table type (BASE TABLE vs. VIEW):
SHOW FULL TABLES;

-- Search for a specific table name (case-insensitive on most systems):
SHOW TABLES LIKE 'users';
SHOW TABLES LIKE '%user%';

If the table does not appear in this list, it may have been dropped or was never created. If it appears but queries still fail, investigate case sensitivity or permissions (see next steps).

2Select the correct database before querying the table

Many "table doesn't exist" errors are caused by querying a table in the wrong database.

sql
-- Check which database is currently selected:
SELECT DATABASE();

-- Switch to the correct database:
USE your_database_name;

-- Verify you're in the right database and the table exists:
SHOW TABLES;
SHOW TABLES LIKE 'your_table';

-- Now query the table:
SELECT * FROM your_table LIMIT 1;

When connecting via an application, specify the database in the connection string or call USE database_name immediately after connecting.

3Check table name case sensitivity and exact spelling

On Linux/Unix, table names are case-sensitive. On Windows and macOS, they are case-insensitive (unless lower_case_table_names is set to 0).

sql
-- Check the current setting:
SHOW VARIABLES LIKE 'lower_case_table_names';

-- On Linux/Unix (case-sensitive), these are different tables:
SELECT * FROM Users;      -- Error if table is named users
SELECT * FROM users;      -- Works if table is named users

-- To find the exact table name:
SHOW TABLES WHERE TABLES_IN_database_name = 'users';

-- Or query the information schema:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME LIKE 'user%';

Always match the exact case when querying. Best practice: define and query tables with lowercase names and use backticks for safety: SELECT * FROM \your_table\;

4Check filesystem permissions on the MySQL data directory

If the table exists in the data dictionary but files are unreadable, MySQL cannot access it.

bash
# Find the data directory:
mysql -u root -p -e "SELECT @@datadir;"

# Check permissions on the database directory:
ls -ld /var/lib/mysql/your_database/

# List table files:
ls -la /var/lib/mysql/your_database/ | grep your_table

# These should be owned by mysql:mysql with permissions 660 (files) or 770 (directories):
# Fix if needed:
sudo chown mysql:mysql /var/lib/mysql/your_database/your_table.*
sudo chmod 660 /var/lib/mysql/your_database/your_table.*
sudo chown mysql:mysql /var/lib/mysql/your_database/
sudo chmod 770 /var/lib/mysql/your_database/

After fixing permissions, restart MySQL:

bash
sudo systemctl restart mysql
5Repair corrupted InnoDB tables using mysqlcheck

Corrupted or missing InnoDB files can cause the table to be unreadable despite existing in the data dictionary.

bash
# Check and repair all tables in a database:
mysqlcheck -u root -p your_database

# Repair a specific table:
mysqlcheck -u root -p your_database your_table

# Repair with --repair flag for aggressive repair:
mysqlcheck -u root -p --repair your_database

# Check status of a specific table:
mysqlcheck -u root -p --check your_database your_table

# For MyISAM tables, use myisamchk (must be done offline):
mysql -u root -p -e "SET GLOBAL myisam_recover_options = 'BACKUP,FORCE';" 
sudo systemctl restart mysql

The mysqlcheck command will report the table status and attempt repairs. Restart MySQL after major repairs.

6Restore the table from a backup if it was accidentally dropped

If the table no longer exists and was accidentally dropped, restore from the most recent backup.

bash
# List available backups:
ls -la /path/to/backups/

# Restore a full database backup (example with mysqldump):
mysql -u root -p your_database < /path/to/backup.sql

# Restore a specific table from a backup (extract relevant SQL):
grep -A 1000 "CREATE TABLE \`your_table\`" /path/to/backup.sql | grep -B 1000 "INSERT INTO \`other_table\`" | mysql -u root -p your_database

# Or use Percona XtraBackup for InnoDB backups:
xtrabackup --prepare --target-dir=/path/to/backup/
xtrabackup --copy-back --target-dir=/path/to/backup/

If no backup is available, the table data cannot be recovered. Implement regular backups immediately.

7Verify and recover the InnoDB data dictionary if needed

For InnoDB tables, if the .ibd file is missing but the table exists in SHOW TABLES, the data dictionary is out of sync.

bash
# Check for missing InnoDB table files:
ls -la /var/lib/mysql/your_database/ | grep -E "\.ibd$"

# If .ibd file is missing but table exists in SHOW TABLES:
# Option 1: Drop and recreate the table from backup
mysql -u root -p your_database -e "DROP TABLE your_table;"
# Then restore from backup (see previous step)

# Option 2: Force InnoDB recovery (advanced, requires downtime):
sudo systemctl stop mysql
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add: innodb_force_recovery=1
sudo systemctl start mysql
mysql -u root -p your_database -e "SHOW TABLES;"  # Check recovery
# After confirming tables are visible, dump the database
mysqldump -u root -p your_database > recovery.sql
# Remove the innodb_force_recovery setting and restart
sudo systemctl restart mysql

Use innodb_force_recovery only as a last resort to extract data from a corrupted database.

Advanced notes

For cross-platform issues: when moving databases between Linux (case-sensitive) and Windows (case-insensitive), always use lowercase table names and the lower_case_table_names variable consistently. Set lower_case_table_names=1 on all platforms to avoid compatibility issues.

When SHOW TABLES displays a table but queries fail, check the error log for additional context: "tail -f /var/log/mysql/error.log". InnoDB may report "Table 'database.table' not found" with errno details that guide diagnosis.

During binary log recovery or replication, replicas may skip tables from the binary log if they don't exist on the replica but do on the master. Check SHOW SLAVE STATUS for errors and use SET GLOBAL SQL_SLAVE_SKIP_COUNTER if necessary (advanced).

In containerized environments (Docker, Kubernetes), ensure volume mounts persist between container restarts. A container restart without persistent storage will lose all tables. Verify volume mounts with docker inspect or kubectl describe pvc.

For performance optimization: if you repeatedly encounter this error for the same table, add the table to connection pooling warm-up queries to catch issues early. Use application-level connection retries with exponential backoff for transient lookup failures.

Frequently asked questions

What does "1146" mean?
MySQL Error 1146 indicates that the database server cannot locate the requested table. This error occurs at the table lookup stage, before the actual query execution begins. table_name' doesn't exist".
How do I fix "1146"?
MySQL Error 1146 (42S02) occurs when a query tries to access a table that does not exist or cannot be found. Common causes include typos in table names, case sensitivity issues on Linux, selecting the wrong database, corrupted table files, and missing InnoDB data dictionary files. Fixes involve verifying table existence, checking case sensitivity, and repairing corrupted files.
What causes "1146"?
Typo in the table name—the query references a table name that does not exist in the selected database Case sensitivity mismatch on Linux/Unix systems where table names are case-sensitive (lower_case_table_names=0) Selecting the wrong database before the query—using the default database instead of the target database
Is the "1146" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading