Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

SHOW TABLES lists the table, but every query says it does not exist in the engine

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

Short answer

Error 1932 means the server has the table's .frm definition, but InnoDB has no such table in its own data dictionary. The usual cause is table files copied into the data directory by hand or a data directory copied while the server ran. Import the tablespace if you have its files, restore the directory, or drop the leftover.

  • Applies to MariaDB 10.11 and 11.4 LTS, InnoDB
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Confirm the table is listed but unusable, and see which files exist for it in the data directory. The .frm file is the server's side; the .ibd file is InnoDB's.

    SHOW TABLES LIKE 'orders';
    SELECT * FROM orders LIMIT 1;
    -- on the server shell:
    -- sudo ls -l /var/lib/mysql/shop/orders.*
  2. 2Match what you see, then jump to its fix

    1. 1The error follows a manual copy of .frm and .ibd files from another server or from a backupTable files were copied in without an importFix
    2. 2Many tables fail at once on a server started from a copied directoryThe whole data directory was copied the wrong wayFix
    3. 3The table is not in use, or you have no files to importA leftover definition of a table you do not needFix

What does the error look like?

  • ERROR 1932 (42S02): Table 'shop.orders' doesn't exist in engine
  • SHOW TABLES lists the table, but SELECT on it fails

Cause 1 of 3

Table files were copied in without an import

1Check

InnoDB knows only the tables in its data dictionary, so copied files are not usable by themselves. Look for the matching .cfg file made by FLUSH TABLES ... FOR EXPORT.

sudo ls -l /var/lib/mysql/shop/orders.frm /var/lib/mysql/shop/orders.ibd /var/lib/mysql/shop/orders.cfg

2Fix

Import the tablespace. On MariaDB 11.2 and later this works directly on the copied files. On older versions create an empty table with the same definition, discard its tablespace, copy the .ibd and .cfg files in, then import.

-- MariaDB 11.2 and later
ALTER TABLE orders IMPORT TABLESPACE;

-- older versions: after creating an identical empty table
ALTER TABLE orders DISCARD TABLESPACE;
-- copy orders.ibd and orders.cfg into the schema folder, set owner mysql
ALTER TABLE orders IMPORT TABLESPACE;

3Verify

The table can be read.

SELECT COUNT(*) FROM orders;

Confirmed by mariadb.com

Did this fix it?

Cause 2 of 3

The whole data directory was copied the wrong way

1Check

A copy taken while the server was running, or one that left out the InnoDB system tablespace and log files, does not match the .frm files.

sudo ls -l /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile* 2>/dev/null

2Fix

Copy the data directory again with the server stopped, including the system tablespace and log files, or restore from a mariadb-backup backup.

sudo systemctl stop mariadb
sudo rsync -a /backup/mysql/ /var/lib/mysql/
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mariadb

3Verify

The tables read normally.

SELECT COUNT(*) FROM shop.orders;

Confirmed by mariadb.com

Did this fix it?

Cause 3 of 3

A leftover definition of a table you do not need

1Check

Only the .frm file remains. The table has no data to recover.

sudo ls -l /var/lib/mysql/shop/orders.*

2Fix

Drop the table. This removes the leftover .frm file, and you can create the table again from your schema.

DROP TABLE orders;

3Verify

SHOW TABLES no longer lists it.

SHOW TABLES LIKE 'orders';

Confirmed by mariadb.com

Did this fix it?

How do I stop it coming back?

  • Move InnoDB tables between servers with FLUSH TABLES ... FOR EXPORT and IMPORT TABLESPACE, or with a dump.
  • Take file-level backups with mariadb-backup, not by copying a live data directory.
  • Stop the server before any manual copy of its data directory.

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.