SHOW TABLES lists the table, but every query says it does not exist in the engine
ERROR 1932 (42S02): Table 'shop.orders' doesn't exist in engineShort 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.
Fix it fast
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.*2Match what you see, then jump to its fix
- 1
The 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
Many tables fail at once on a server started from a copied directoryThe whole data directory was copied the wrong wayFix - 3
The table is not in use, or you have no files to importA leftover definition of a table you do not needFix
- 1
What does the error look like?
ERROR 1932 (42S02): Table 'shop.orders' doesn't exist in engineSHOW 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.cfg2Fix
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
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/null2Fix
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 mariadb3Verify
The tables read normally.
SELECT COUNT(*) FROM shop.orders;Confirmed by mariadb.com
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
systemctl start mariadb hangs, then fails with a timeout
mariadb.service start-pre operation timed out. Terminating.
When mariadb.service fails or hangs, the journal and the server's own start-up messages say why.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix