Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT
I am fairly new to MySQL and I am getting a pretty interesting error on which I cannot find any help via google and the stackoverflow search.
I am running a local server of MySQL 5.6.10 on MacOS 10.8.3 and manage my database via Navicat essentials for MySQL.
The error I get is that after running and managing my database just fine for a couple of days/weeks something triggers to (it appears incompletely) delete some of the tables I created using queries from within Navicat.
When I try to run queries using these tables, Navicat then warns me that the particular table does not exist. So far so good - here comes the good part:
When I try to CREATE the table, e.g. named "temp", that was previously there, I get the following error message:
Error : Tablespace for table '`database`.`temp`' exists. Please DISCARD the tablespace before IMPORT.
However, if I try to drop the table, or try to discard the tablespace for this table, using
DROP TABLE temp;
ALTER TABLE temp DISCARD TABLESPACE;
I get the following error messages:
Error : Unknown table 'database.temp'
Error : Table 'database.temp' doesn't exist
So that means that I am advised to discard the table space but when I try to do so the table does not exist. Is it possible that there is some type of remnant of this table at a different place where the DISCARD query isn't checking? And does anybody have an idea what could trigger all that - completely randomly as it seems?
As I said, I'm new to the subject and pretty much clueless. I suspect that re-booting my laptop, i.e. resetting my local MySQL server, or maybe user permission rights might have to do with it, but I am just hypothesizing here.
Answer
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.ibd
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.