MySQL Import Error: Tablespace Exists But Table Doesn\'t?

Error: Tablespace for table exists. Please DISCARD the tablespace before IMPORT

Question:

A MySQL user encounter an error attempt to create a table that previously existed. The error message indicates that the tablespace for the table already exists and that they should discard it before importing. However, when attempting to drop the table or discard the tablespace, error messages state that the table doesn't exist. What could be causing this issue?

Answer:

This error commonly occurs when running in 'innodb_file_per_table' mode and encountering a 'tablespace full' error. The database server's tablespace is defined by the innodb_data_file_path setting, which can be limited by default.

In such cases, it's likely that an orphaned .ibd file exists without its companion .frm file in the OS directory where files-per-table are stored (typically /var/lib/mysql or /usr/local/var/mysql).

Solution:

To resolve the issue:

1. Locate the orphaned .ibd file in the directory where files-per-table are stored.
2. Move the .ibd file to a safe temporary location (e.g., /tmp/mysql_orphans).

Example:

$ ls /var/lib/mysql

table1.frm
table1.idb
table2.frm
table2.idb
table3.idb  <-- problem table, no table3.frm
table4.frm
table4.idb

$ mkdir /tmp/mysql_orphans
$ mv /var/lib/mysql/table3.ibd /tmp/mysql_orphans/

Note: Ensure that any underlying cause, such as long-running queries or locked tables, has been resolved to prevent further orphaned .ibd files from being created.

The above is the detailed content of MySQL Import Error: Tablespace Exists But Table Doesn\'t?. For more information, please follow other related articles on the PHP Chinese website!