MySQL "ERROR 1146: Table doesn't exist": the database, the case, or the files

1. The wrong database

The database comes from your connection, or from USE. A script that worked on one server may be connected to another database on the next:

SELECT DATABASE();

Name it in full, which also works across databases:

SELECT * FROM shop.orders LIMIT 5;

Or check your connection string. A missing database name in it gives ERROR 1046: No database selected instead.

2. A typo, or a plural

It’s the first thing to rule out. Here the table is orders, and the query asked for order. Search, rather than guess:

SHOW TABLES LIKE '%order%';

3. Upper and lower case on Linux

This is the classic one: it works on Windows or macOS, and fails on the Linux server. On Linux, each table is a file, and file names there are case-sensitive. So Orders and orders are different tables:

CREATE TABLE Orders (id INT PRIMARY KEY);
SELECT * FROM orders;

ERROR 1146 (42S02): Table 'shop.orders' doesn't exist

See how your server treats names:

SHOW VARIABLES LIKE 'lower_case_table_names';

The fix is consistent names, ideally all lowercase. Rename the table:

RENAME TABLE Orders TO orders;

Changing the setting itself is a bigger step. On MySQL 8, lower_case_table_names can only be set when the data folder is first created, and the server refuses to start if you change it later. So it means a fresh install and a reload of the data.

4. The table was never created

• Migrations didn’t run on this server, after a fresh deploy. Check your tool’s status command.
• A dump was restored into another database, or only partly restored. A failed import stops at its first error. Read the import’s output, and see MySQL error 1064 if it says the syntax is wrong.
• In Docker, the scripts in /docker-entrypoint-initdb.d only run on an empty data folder. With an existing volume, nothing new is created.
• A temporary table exists only in the session that created it.

5. It’s on disk, and not in MySQL

Copying files from the data folder doesn’t copy InnoDB tables. Their data and their entry in MySQL’s dictionary have to move together. After a copied folder, or a damaged dictionary, you see the table’s files, and MySQL says:

ERROR 1146 (42S02): Table 'shop.orders' doesn't exist
ERROR 1932 (42S02): Table 'shop.orders' doesn't exist in engine

Don’t copy table files between servers, except with the documented transportable tablespace feature. Restore from a dump instead, made with mysqldump or MySQL Shell’s dump utilities, or from a physical backup tool such as Percona XtraBackup or MySQL Enterprise Backup. Check the error log too: it often explains why InnoDB couldn’t open the table.

6. A view, or a trigger, pointing at a missing table

If the query uses a view whose table was renamed or dropped, you get a related error:

ERROR 1356 (HY000): View 'shop.order_totals' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Find which views use the table, then recreate them:

SELECT table_name FROM information_schema.views
WHERE table_schema = 'shop' AND view_definition LIKE '%orders%';