错误:表 xxx 的表空间存在.请在 IMPORT 之前丢弃表空间
我对 MySQL 还很陌生，我遇到了一个非常有趣的错误，我无法通过 google 和 stackoverflow 搜索找到任何帮助.
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.
我在 MacOS 10.8.3 上运行 MySQL 5.6.10 的本地服务器，并通过 Navicat Essentials for MySQL 管理我的数据库.
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.
我得到的错误是，在运行和管理我的数据库几天/几周之后，某些东西触发(它似乎不完整)删除了我使用 Navicat 中的查询创建的一些表.
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.
当我尝试使用这些表运行查询时，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:
当我尝试创建表格时，例如名为temp"，之前在那里，我收到以下错误消息:
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;
我收到以下错误消息:
Error : Unknown table 'database.temp'
Error : Table 'database.temp' doesn't exist
这意味着建议我丢弃表空间，但是当我尝试这样做时，该表不存在.是否有可能在 DISCARD 查询未检查的其他地方存在某种类型的该表的残余?有没有人知道是什么触发了这一切——看起来完全是随机的?
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?
正如我所说，我是这个主题的新手，几乎一无所知.我怀疑重新启动我的笔记本电脑，即重置我的本地 MySQL 服务器，或者用户权限可能与它有关，但我在这里只是假设.
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.
推荐答案
这里有点晚了，但通常我已经看到当您在innodb_file_per_table"模式下运行时出现表空间已满"错误时会出现此问题.无需赘述(更多此处)，数据库服务器的表空间由 innodb_data_file_path 设置定义，默认情况下相当小.即使变大了，表空间已满"仍然会出现在更大的查询中(许多非表东西"存储在那里，撤消日志，缓存等......).
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...).
无论如何，我发现如果您查看存储每个表的文件的 OS 目录，OSX 上默认为/var/lib/mysql，使用 homebrew iirc 的/usr/local/var/mysql，您将找到一个孤立的 tablename.ibd 文件，没有它的正常伴随 tablename.frm 文件.如果您将该 .ibd 文件移动到一个安全的临时位置(只是为了安全)，那么应该可以解决问题.
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/
但是有一个警告，请确保最初导致问题的原因是什么，例如长时间运行的查询、锁定的表等...已被清除.否则，当您第二次尝试时，您只会得到另一个孤立的 .ibd 文件.
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.
相关文章