Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL says Table 'db.table' doesn't exist but you can see it

ErrorERROR 1146 (42S02): Table 'shop.Orders' doesn't exist

Short answer

Read the name in the message: it is exactly the database and table MySQL looked for. On Linux, database and table names are case-sensitive by default, so Users and users are different tables. A dump moved from Windows or macOS often fails this way. A TEMPORARY table also vanishes when its session ends.

  • Applies to MySQL 8.0, MySQL 8.4, Linux
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    List the tables in that database and compare the real name with the one in the message, letter by letter, including case.

    SELECT DATABASE();
    SHOW TABLES FROM shop;
  2. 2Match what you see, then jump to its fix

    1. 1SHOW TABLES lists orders while the error names OrdersThe table name differs in letter case (Linux server, Windows or macOS dump)Fix
    2. 2The code creates the table in one connection and reads it in another, or after a reconnectThe table was a TEMPORARY table and its session endedFix

What does the error look like?

  • ERROR 1146 (42S02): Table 'shop.Orders' doesn't exist
  • ER_NO_SUCH_TABLE: Table '%s.%s' doesn't exist

Cause 1 of 2

The table name differs in letter case (Linux server, Windows or macOS dump)

1Check

The manual says names are not case-sensitive on Windows but are case-sensitive in most Unix systems. The default of lower_case_table_names is 0 on Unix, 1 on Windows and 2 on macOS.

SHOW VARIABLES LIKE 'lower_case_table_names';

2Fix

Make the query use the exact case that was created, or rename the table to one fixed style, ideally all lowercase. Setting lower_case_table_names to 1 is possible only when the server is initialized; changing it afterwards is prohibited.

RENAME TABLE Orders TO orders;

3Verify

The query that failed now finds the table.

SELECT COUNT(*) FROM shop.orders;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 2

The table was a TEMPORARY table and its session ended

1Check

A TEMPORARY table is visible only in the session that made it, and is dropped when that session closes. Connection pools can hand your next query to a different connection.

SHOW TABLES FROM shop;

2Fix

Create and use the table on the same connection, or use a normal table if other connections need it.

CREATE TABLE report_cache (id INT PRIMARY KEY, total DECIMAL(10,2));

3Verify

A second connection can read the table.

SELECT COUNT(*) FROM shop.report_cache;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Pick one naming style, lowercase with underscores, for every table and database.
  • Run a restore from another operating system in a scratch server and check the names before production.
  • Set lower_case_table_names when initializing a new server if you share dumps between systems.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.