Database Tablespace Error: How to Resolve \'Tablespace for table \'database.temp\' exists\' When the Table Doesn\'t?

Database Tablespace Error: Troubleshooting Unseen Tablespace

Problem:

When attempting to create or drop a table, you encounter the error message "Tablespace for table 'database.temp' exists. Please DISCARD the tablespace before IMPORT." However, attempting to discard the tablespace results in an error claiming the table does not exist.

Answer:

This error can occur when the database runs in "innodb_file_per_table" mode and the default tablespace is insufficient. In such cases, an "orphaned" .ibd file (without a corresponding .frm file) may exist in the database's file directory.

To resolve the issue:

1. Locate the orphaned .ibd file in the database's file directory (e.g., /var/lib/mysql).
2. Move the .ibd file to a temporary location for safekeeping (e.g., /tmp/mysql_orphans).

After moving the orphaned file, you should be able to successfully create or drop the table. Ensure that the underlying problem causing the orphaned file (e.g., long running query) has been resolved to prevent recurrence.

The above is the detailed content of Database Tablespace Error: How to Resolve \'Tablespace for table \'database.temp\' exists\' When the Table Doesn\'t?. For more information, please follow other related articles on the PHP Chinese website!