Why Does MySQL Show \'Tablespace for Table Exists. Please DISCARD before IMPORT\' Even After Dropping the Tablespace?

MySQL Error: "Tablespace for Table Exists. Please DISCARD before IMPORT"

Question:

When trying to recreate a table that was accidentally deleted, users encounter the error "Tablespace for table 'database.temp' exists. Please DISCARD the tablespace before IMPORT." Despite attempting to drop or discard the tablespace, the error persists, indicating that the table no longer exists.

Answer:

This issue often arises due to a 'tablespace full' error in 'innodb_file_per_table' mode.

• Identifying the Orphaned File:

Check the directory where the tablespaced tables are stored (e.g., /usr/local/var/mysql) for an orphaned .ibd file (e.g., table3.ibd) without its usual .frm counterpart (table3.frm).

• Resolution:

Move the orphaned .ibd file to a temporary location:

mkdir /tmp/mysql_orphans
mv /usr/local/var/mysql/table3.ibd /tmp/mysql_orphans/

• Caution:

Ensure that the underlying cause (e.g., long-running query, locked table) has been resolved to prevent further orphaned files.

The above is the detailed content of Why Does MySQL Show \'Tablespace for Table Exists. Please DISCARD before IMPORT\' Even After Dropping the Tablespace?. For more information, please follow other related articles on the PHP Chinese website!