MySQL Error 1146

Error number

1146

SQLSTATE

42S02

Symbol

ER_NO_SUCH_TABLE

Product

MySQL Server

Versions

All MySQL versions (checked on MySQL 8.0.46)

Category

Syntax & Names

Last reviewed

24 October 2026

What it means

Error 1146 means the database exists but has no table or view with the name the statement used.

The message always shows the full name MySQL looked for, database.table. That is the quickest check: is the database part the one you expected, and is the table name spelt exactly as it was created? For an unqualified name, the database part is the connection's default database, so 1146 can also mean the query ran against the wrong database. If the database itself is missing you get 1049 instead, and if no database is selected at all, 1046.

Letter case matters on most Linux servers. Table names map to files, and with lower_case_table_names = 0 (the default on Unix) Customers and customers are different tables. On Windows (default 1) and macOS (default 2) names are compared in lowercase, so code that works on a developer laptop can fail with 1146 after deployment to Linux. The setting can only be chosen when the server is initialised, so the lasting fix is consistent lowercase names in the code.

Common causes

• A typo, or singular versus plural (customer for customers).
• The connection's default database is not the one that holds the table, for example a staging database selected instead of production.
• Letter case that differs from the table name on a server with lower_case_table_names = 0, typically after moving from Windows or macOS to Linux.
• Migrations have not run (or ran in the wrong order), so the table is not created yet, or a restore was partial and skipped some tables.
• A TEMPORARY table created in another connection. Temporary tables are visible only to the session that created them and are dropped when it ends, which matters with connection pools.

Diagnose it

Read-only These statements only read metadata (information_schema, performance_schema and SHOW statements) or your own data. They do not change data, privileges or configuration, so they are safe to run first.

SELECT DATABASE() AS current_db;

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY table_name;

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name LIKE '%customer%'
  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY table_schema, table_name;

SELECT @@lower_case_table_names AS lctn, @@version_compile_os AS os;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

SELECT id, name
FROM shop.customers
WHERE id = 1;

SELECT id, name FROM customers;  -- not Customers

CREATE TABLE IF NOT EXISTS order_items (
    order_id   INT NOT NULL,
    line_no    INT NOT NULL,
    product_id INT NOT NULL,
    qty        INT NOT NULL,
    PRIMARY KEY (order_id, line_no)
);

Examples

Singular instead of plural

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100));

SELECT * FROM customer;

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

A JOIN to a table that has not been created yet

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT);

SELECT o.id, i.product_id
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id;

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

A migration that alters a table before creating it

-- 002_add_paid_flag.sql ran before 001_create_invoices.sql
ALTER TABLE invoices ADD COLUMN paid TINYINT NOT NULL DEFAULT 0;

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

A temporary table read from another connection

-- connection A
CREATE TEMPORARY TABLE tmp_totals (customer_id INT, total DECIMAL(10,2));

-- connection B (for example the next request from a pool)
SELECT * FROM tmp_totals;

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

How to prevent it

Use lowercase table names, keep the database name in the connection settings, and run migrations in a fixed order from a tool that records which ones have run. Check that the schema is complete after every restore by comparing table counts with the source.

Test on the same operating system and lower_case_table_names setting as production, so case mistakes appear in development rather than after deployment.

Common questions

Learn more on CodeWithSQL

• MySQL dump and restore with mysqldump
• DDL commands (CREATE, ALTER, DROP)

Official documentation

• Identifier case sensitivity (lower_case_table_names)
• CREATE TEMPORARY TABLE statement
• The INFORMATION_SCHEMA TABLES table
• MySQL 8.0 server error message reference

Last technically reviewed 24 October 2026; examples and diagnostics run on MySQL 8.0.46. Message templates checked against the server's error catalogue (perror) for that version.