错误:表xxx的表空间已存在.请在导入之前丢弃表空间 [英] Error: Tablespace for table xxx exists. Please DISCARD the tablespace before 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 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，在OSB上使用/usr/local/var/mysql并使用自制iirc，找不到孤立的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.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/

请注意，请确保最初是什么原因导致了问题，例如长期运行的查询，锁定的表等已被清除.否则，当您再次尝试时，最终只会得到另一个孤立的.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.

这篇关于错误:表xxx的表空间已存在.请在导入之前丢弃表空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！