IMPORT TABLESPACE fails because the tablespace still exists
ERROR: Tablespace for table 'xxx' exists. Please DISCARD the tablespace before IMPORTShort answer
ALTER TABLE ... IMPORT TABLESPACE only works on a table whose own tablespace has been discarded. Create a table with the same definition, run DISCARD TABLESPACE, copy the .ibd (and .cfg) file into the database folder, then import. Foreign keys, file-per-table mode and schema differences can each block a step.
Fix it fast
1Find out which cause you have
Check that the table uses its own file and that file-per-table mode is on, then look at the table definition you are importing into.
SHOW VARIABLES LIKE 'innodb_file_per_table'; SHOW CREATE TABLE mydb.t1\G2Match what you see, then jump to its fix
- 1
The error appears on ALTER TABLE ... IMPORT TABLESPACEThe receiving table's tablespace was not discarded firstFix - 2
DISCARD TABLESPACE is refused on a table that has foreign keysForeign keys stop DISCARD TABLESPACEFix - 3
Import is refused or a schema mismatch is reportedThe table is not in a file-per-table tablespace, or versions differFix
- 1
What does the error look like?
ERROR: Tablespace for table 'xxx' exists. Please DISCARD the tablespace before IMPORTSchema mismatch error when importing a tablespace
Cause 1 of 3
The receiving table's tablespace was not discarded first
1Check
Before importing, you must discard the tablespace of the receiving table.
SHOW WARNINGS;2Fix
Follow the order: discard on the destination, quiesce and copy from the source, release the lock, then import.
-- destination
ALTER TABLE t1 DISCARD TABLESPACE;
-- source (keep this connection open until the copy is done)
FLUSH TABLES t1 FOR EXPORT;
-- copy t1.ibd and t1.cfg into the destination database folder, then:
UNLOCK TABLES;
-- destination
ALTER TABLE t1 IMPORT TABLESPACE;3Verify
Count the rows that arrived.
SELECT COUNT(*) FROM t1;Confirmed by dev.mysql.com
Cause 2 of 3
Foreign keys stop DISCARD TABLESPACE
1Check
foreign_key_checks must be disabled before DISCARD TABLESPACE. Import does not check foreign keys on the imported data, so export all related tables at the same logical point in time.
SELECT @@foreign_key_checks;2Fix
Turn the checks off in your session for the discard step, then back on.
SET foreign_key_checks = 0;
ALTER TABLE t1 DISCARD TABLESPACE;
SET foreign_key_checks = 1;3Verify
Run the import and spot-check related rows.
ALTER TABLE t1 IMPORT TABLESPACE;Confirmed by dev.mysql.com
Cause 3 of 3
The table is not in a file-per-table tablespace, or versions differ
1Check
Transportable tablespaces work only for file-per-table tables, not the system or general tablespaces. Both servers must be the same GA version and page size, and the table definition must match, including DATA DIRECTORY and ROW_FORMAT.
SELECT @@version, @@innodb_page_size, @@innodb_default_row_format;
SHOW VARIABLES LIKE 'innodb_file_per_table';2Fix
Recreate the destination table with exactly the source definition, on a server of the same version.
SHOW CREATE TABLE t1\G3Verify
Repeat the import after the definition matches.
ALTER TABLE t1 IMPORT TABLESPACE;Confirmed by dev.mysql.com
How do I stop it coming back?
- Keep the source connection open until the files are copied; closing it removes the .cfg file.
- Always copy the .cfg file with the .ibd file, so MySQL can check the schema.
- Move tables only between servers of the same GA version.
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
SHOW TABLES lists the table, but every query says it does not exist in the engine
SHOW TABLES lists the table, but SELECT on it fails
Error 1932 means the server has the table's .frm definition, but InnoDB has no such table in its own data dictionary.
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