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

What it means

The message names the database and table the server looked for: Table 'shop.orders' doesn't exist means it searched database shop for a table called exactly orders and found none. Read both halves. If the database part isn’t the one you expected, you’re in the wrong database; if the table part differs from the real table only in case, it’s the case rule below.

Letter case: macOS and Windows vs Linux

MySQL and MariaDB store each table as files on disk, so whether Orders and orders are the same table depends on the server’s lower_case_table_names setting, whose default follows the operating system:

So a table created as Orders answers to orders on a Mac (value 2), and the same query fails on a Linux server (value 0) with error 1146. This is the classic “works on my machine” case. Database names follow the same rule; column names never depend on it.

Common causes

1. Letter case differs between the query and the table, and the server runs on Linux.
2. The wrong default database. An unqualified orders means orders in the current database (SELECT DATABASE() shows which). With none selected you get error 1046, No database selected.
3. The table was never created: migrations haven’t run, or ran against a different database or server.
4. A temporary table from another connection. CREATE TEMPORARY TABLE is visible only in the session that made it; a connection pool can send your next query down a different connection.
5. A typo or a plural: order_item and order_items.

How to fix it

Find the table’s real name

Search every database you can see, ignoring case:

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

TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE
inlet	orders	BASE TABLE
seo_mysql	Orders	BASE TABLE

Then use the exact case and, if it’s in another database, qualify it: seo_mysql.Orders.

Settle on one case

The simplest rule for code that runs on more than one operating system is to name every table and database in lower case. To rename one on Linux:

RENAME TABLE Orders TO orders;

Every query, view and stored routine that uses the old case needs updating too.

Don’t try to change lower_case_table_names on an existing server

MySQL 8 and MariaDB only take the value when the data directory is first initialised; changing it afterwards isn’t allowed. To get the Linux behaviour on your Mac, develop against the same server in Docker (Linux, value 0).

Select the right database

SELECT DATABASE();
USE shop;

Or set the database in your connection settings or URL path.

A view that points at a dropped table: error 1356

If the name in your query is a view, and the table behind it is gone, the error is different:

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

Recreate the table or redefine the view with CREATE OR REPLACE VIEW.

Reproduce it

On MySQL 8.4.11 (Linux, lower_case_table_names = 0):

CREATE TABLE Orders (id int PRIMARY KEY, total decimal(10,2));
SELECT * FROM orders;

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

SELECT * FROM Orders works. From database inlet, where no Orders exists:

ERROR 1146 (42S02): Table 'inlet.Orders' doesn't exist

A temporary table read from a second connection:

ERROR 1146 (42S02): Table 'seo_mysql.import_rows' doesn't exist

On a throwaway MySQL 8.4.11 container initialised with --lower-case-table-names=1, the same CREATE TABLE Orders was stored as orders, and ORDERS, Orders and orders all worked:

Tables_in_seo_mysql
orders

MariaDB 11.4.13 printed identical messages in every case. One difference: reading missing_db.some_table gives MariaDB’s 1146 where MySQL reports 1049 Unknown database.

In Inlet

⌘P opens a table by name, and the query editor completes table names from the live schema, spelt as they really are. When a query fails, the error is shown with a hint.