Fix MySQL ERROR 1813: Tablespace for table exists. Please DISCARD the tablespace before IMPORT

The Error

You're restoring an InnoDB table by copying .ibd files manually, and MySQL stops you cold:

ERROR 1813 (HY000): Tablespace for table '`mydb`.`mytable`' exists. Please DISCARD the tablespace before IMPORT.

This hits when you're using the transportable tablespace method — dropping a backup .ibd file into a live MySQL data directory. The problem: InnoDB already has an active tablespace registered for that table in its internal dictionary.

MySQL refuses to overwrite a tracked tablespace. You have to explicitly release the current one before importing a replacement.

Why It Happens

InnoDB maintains its own data dictionary, separate from the filesystem. When you run ALTER TABLE ... IMPORT TABLESPACE, MySQL looks up whether a tablespace is already registered for that table. If one exists — even if the .ibd file on disk is already your backup file — ERROR 1813 fires.

Three situations trigger this reliably:

Restoring a single table from backup by copying the .ibd file directly into the data directory
Re-importing after a failed restore that left the old tablespace registered
Migrating a table between servers using FLUSH TABLES ... FOR EXPORT / IMPORT TABLESPACE

Step-by-Step Fix

Four steps: recreate the table structure → discard the tablespace → drop in your backup .ibd → import.

Step 1: Get the table structure from your backup

You need a CREATE TABLE statement that exactly matches the .ibd file you're restoring. Pull it from the source server if it's still up:

-- On the source server
SHOW CREATE TABLE mydb.mytable\G

Copy that output somewhere — you'll need it in the next step.

Step 2: Drop and recreate the table on the target server

On the target MySQL, recreate the table with the same structure. This gives InnoDB a fresh tablespace entry to work with.

USE mydb;

DROP TABLE IF EXISTS mytable;

CREATE TABLE mytable (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  -- ... rest of your columns
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

MySQL now creates a brand-new mytable.ibd file. You're about to throw that file away and replace it with your backup.

Step 3: Discard the tablespace

ALTER TABLE mydb.mytable DISCARD TABLESPACE;

This deletes the .ibd file MySQL just created and unregisters the tablespace from InnoDB's dictionary. The table definition survives — only the data file is gone.

Step 4: Copy the backup .ibd file into place

Find your MySQL data directory first:

mysql -u root -p -e "SELECT @@datadir;"

On a standard Linux install this is /var/lib/mysql/. Now copy the backup files. MySQL 8.0+ requires both .ibd and .cfg (the .cfg is produced by FLUSH TABLES ... FOR EXPORT):

# MySQL 5.7 — only .ibd needed
cp /path/to/backup/mytable.ibd /var/lib/mysql/mydb/

# MySQL 8.0+ — both files required
cp /path/to/backup/mytable.ibd /var/lib/mysql/mydb/
cp /path/to/backup/mytable.cfg /var/lib/mysql/mydb/

Fix ownership or the import will fail silently:

chown mysql:mysql /var/lib/mysql/mydb/mytable.ibd
chown mysql:mysql /var/lib/mysql/mydb/mytable.cfg  # MySQL 8.0+ only

Step 5: Import the tablespace

ALTER TABLE mydb.mytable IMPORT TABLESPACE;

A clean run returns Query OK, 0 rows affected. No message means something went wrong — check the error log.

Verify the Fix

Don't trust a successful import until you've spot-checked the data:

SELECT COUNT(*) FROM mydb.mytable;
SELECT * FROM mydb.mytable LIMIT 5;

Even a clean import can hide corruption warnings in the MySQL error log. Check it:

# Linux default path
tail -50 /var/log/mysql/error.log

# Or find your log location
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_error';"

Then run a full integrity check:

CHECK TABLE mydb.mytable;

If the Error Still Appears After DISCARD

Sometimes DISCARD TABLESPACE itself refuses to run. Flush the table first to clear any active transactions:

FLUSH TABLES mydb.mytable;

ALTER TABLE mydb.mytable DISCARD TABLESPACE;

Still blocked? Foreign key constraints are the usual culprit. They silently prevent tablespace operations. Turn off FK checks for the duration:

SET foreign_key_checks = 0;
ALTER TABLE mydb.mytable DISCARD TABLESPACE;
-- copy your .ibd (and .cfg) files here
ALTER TABLE mydb.mytable IMPORT TABLESPACE;
SET foreign_key_checks = 1;

The Right Way to Export (for Next Time)

If you control the source server, do this before copying files. FLUSH TABLES ... FOR EXPORT generates the .cfg metadata file that makes imports reliable — especially on 8.0+:

-- On source server
FLUSH TABLES mydb.mytable FOR EXPORT;

-- Copy both files from the data directory:
-- /var/lib/mysql/mydb/mytable.ibd
-- /var/lib/mysql/mydb/mytable.cfg

-- Release the lock when done
UNLOCK TABLES;

Skip the .cfg on MySQL 8.0 and the import still works — but MySQL logs a warning that schema mismatch detection was bypassed. Worth avoiding.

Quick Tips

Row format must match: the backup's ROW_FORMAT (COMPACT, DYNAMIC, COMPRESSED) must be identical to the CREATE TABLE on the target. Check with SHOW TABLE STATUS LIKE 'mytable'\G.
Version compatibility: .ibd files from MySQL 8.0 cannot be imported into 5.7. The internal page format changed between versions.
innodb_file_per_table must be ON: this method only works when each table has its own .ibd file. Confirm with SHOW VARIABLES LIKE 'innodb_file_per_table'.
Partitioned tables: each partition gets its own file (mytable#p#p0.ibd, mytable#p#p1.ibd, etc.). Run DISCARD and IMPORT separately for each partition.