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

Question

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;

This is exactly what i did in mariadb 10.2.16 on fedora when i had a table that showed exactly the same errors in the log file i suppose...

2018-07-11  9:43:58 140323764213504 [Note] InnoDB: The file './database_name/innodb_table.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. You can resolve the problem by removing the file.
2018-07-11  9:44:29 140323764213504 [Warning] InnoDB: Tablespace 'database_name/innodb_table' exists in the cache with id 2836 != 2918

your mileage and errors may vary but the main one i assume is

...already exists though the corresponding table did not exist in the InnoDB data dictionary...

with drop table not working as well as alter table...

MariaDB [database_name]> drop table innodb_table;
ERROR 1051 (42S02): Unknown table 'database_name.innodb_table'

MariaDB [database_name]> alter table innodb_table discard tablespace;
ERROR 1146 (42S02): Table 'database_name.innodb_table' doesn't exist

create table also fails like so:

MariaDB [database_name]> create table  innodb_table(`id` int(10) unsigned NOT NULL);
ERROR 1813 (HY000): Tablespace for table '`database_name`.`innodb_table`' exists. Please DISCARD the tablespace before IMPORT

in order to fix this, what i did was first

create table  innodb_table2(`id` int(10) unsigned NOT NULL);
Query OK, 0 rows affected (0.07 sec)

then in the /var/lib/mysql/database_name directory i did the following as root acknowledging the overwriting of innodb_table.ibd causing us issues

cp -a innodb_table2.frm innodb_table.frm
cp -a innodb_table2.ibd innodb_table.ibd
systemctl restart mariadb

then back in the mysql console i issued a successful drop command on both tables

MariaDB [database_name]> drop table innodb_table;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    8
Current database: database_name

Query OK, 0 rows affected (0.08 sec)

MariaDB [database_name]> drop table innodb_table2;
Query OK, 0 rows affected (0.25 sec)

and everything is now all square and i can recreate the one single table...

MariaDB [database_name]> create table  innodb_table (`id` int(10) unsigned NOT NULL);
Query OK, 0 rows affected (0.08 sec)

EDIT: I was going to add in a

restorecon -Rv /var/lib/mysql/database_name 

command after the copying of the database to get all selinux contexts the way they should be, even though we are deleting them from the database almost immediately, but in the alternative you could just add the --archive or -a option to the two cp commands, so yes actually the archive option shortens this:

cp innodb_table2.frm innodb_table.frm
cp innodb_table2.ibd innodb_table.ibd
chown mysql:mysql innodb_table.frm innodb_table.ibd
chmod 660 innodb_table.frm innodb_table.ibd
restorecon -Rv /var/lib/mysql/database_name
systemctl restart mariadb

to just the following which i think is better and it keeps the selinux context that is set for the already made table.

cp -a innodb_table2.frm innodb_table.frm
cp -a innodb_table2.ibd innodb_table.ibd
systemctl restart mariadb

I have replaced the above longer list of commands for the shorter list which could be shortened still with an *