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

A little late here but generally I've seen this problem occur when you get a 'tablespace full' error when running in a 'innodb_file_per_table' mode. Without going into too much detail (more here), the database server's tablespace is defined by the innodb_data_file_path setting and by default is rather small. Even made larger, the 'tablespace full' can still occur with larger queries and such (lots of non-table 'stuff' is stored in there, undo logs, caches, etc...).

Anyways, I found that if you look in the OS directory where the files-per-table are stored, /var/lib/mysql by default on OSX, /usr/local/var/mysql with homebrew iirc, you'll find an orphaned tablename.ibd file without it's normal companion tablename.frm file. If you move that .ibd file to a safe temporary location (just to be safe) that should fix the problem.

$ 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/

One caveat though, make sure what ever is causing the problem originally, e.g. long running query, locked table, etc... has been cleared. Otherwise you just end up with another orphaned .ibd file when you try a second time.

Xampp and Mamp Users

Had the same error while importing a database (after emptying it) trough MySQL. I found that i had a tablename.ibd file left while all others were deleted. I deleted it manually from mysql/data/database_name and the error was gone.

If you get the .idb recreated again after you delete it, then read this answer.

This how it worked with me. I had the .idb file without it's corresponding .frm and whenever I delete the .idb file, the database recreate it again. and I found the solution in one line in MySQL documentation (Tablespace Does Not Exist part)

1- Create a matching .frm file in some other database directory and copy it to the database directory where the orphan table is located.

2- Issue DROP TABLE for the original table. That should successfully drop the table and InnoDB should print a warning to the error log that the .ibd file was missing.

I copied another table .frm file and name it like my missing table, then make a normal drop table query and voila, it worked and the table is dropped normally!

my system is XAMPP on windows MariaDB v 10.1.8

For WAMP [Windows 7 Ultimate x64-bit] Users:

I agree with what DangerDave said and so I'm making an answer available for WAMP Users.

Note: First of all, you have to go to your ..\WAMP\Bin\MySQL\MySQL[Your MySQL Version]\Data folder.

Now, you'll see folders of all your databases

• Double-click the folder of the database which has the offending table to open it
• There shouldn't be a file [Your offending MySQL table name].frm, instead there should be a file [Your offending MySQL table name].ibd
• Delete the [Your offending MySQL table name].ibd
• Then, delete it from the Recycle Bin too
• Then run your MySQL query on the database and you're done

In my case:

First remove tableName.ibd in your database directory from Mysql and second run:

ALTER TABLE tableName DISCARD TABLESPACE;
DROP TABLE tableName;