Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

IMPORT TABLESPACE fails because the tablespace still exists

ErrorERROR: Tablespace for table 'xxx' exists. Please DISCARD the tablespace before IMPORT

Short 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.

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

Fix it fast

  1. 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\G
  2. 2Match what you see, then jump to its fix

    1. 1The error appears on ALTER TABLE ... IMPORT TABLESPACEThe receiving table's tablespace was not discarded firstFix
    2. 2DISCARD TABLESPACE is refused on a table that has foreign keysForeign keys stop DISCARD TABLESPACEFix
    3. 3Import is refused or a schema mismatch is reportedThe table is not in a file-per-table tablespace, or versions differFix

What does the error look like?

  • ERROR: Tablespace for table 'xxx' exists. Please DISCARD the tablespace before IMPORT
  • Schema 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

Did this fix it?

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

Did this fix it?

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\G

3Verify

Repeat the import after the definition matches.

ALTER TABLE t1 IMPORT TABLESPACE;

Confirmed by dev.mysql.com

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

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