MySQL 错误：表 xxx 的表空间存在。请在 IMPORT 之前丢弃表空间

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

提问by MattMirabilis

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.

我对 MySQL 相当陌生，我遇到了一个非常有趣的错误，我无法通过 google 和 stackoverflow 搜索找到任何帮助。

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.

我在 MacOS 10.8.3 上运行 MySQL 5.6.10 的本地服务器，并通过 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.

我得到的错误是，在运行和管理我的数据库几天/几周之后，某些东西会触发（似乎不完整）删除我使用 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:

当我尝试使用这些表运行查询时，Navicat 会警告我该特定表不存在。到目前为止一切顺利 - 这是好的部分：

When I try to CREATE the table, e.g. named "temp", that was previously there, I get the following error message:

当我尝试创建以前存在的表，例如名为“temp”的表时，我收到以下错误消息：

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?

所以这意味着我被建议丢弃表空间，但是当我尝试这样做时，该表不存在。是否有可能在 DISCARD 查询未检查的其他地方存在某种类型的该表的残余？有没有人知道是什么触发了这一切——看起来完全是随机的？

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.

正如我所说，我是这个主题的新手，几乎一无所知。我怀疑重新启动我的笔记本电脑，即重置我的本地 MySQL 服务器，或者用户权限可能与它有关，但我只是在这里假设。

回答by DangerDave

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...).

这里有点晚了，但通常我已经看到当您在“innodb_file_per_table”模式下运行时出现“表空间已满”错误时会发生此问题。无需详细介绍（更多信息），数据库服务器的表空间由 innodb_data_file_path 设置定义，默认情况下相当小。即使变大了，“表空间已满”仍然会出现在更大的查询中（许多非表“东西”存储在那里，撤消日志，缓存等......）。

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.

无论如何，我发现如果您查看存储每个表的文件的 OS 目录，OSX 上默认为 /var/lib/mysql，使用 homebrew iirc 的 /usr/local/var/mysql，您会发现一个孤立的 tablename.ibd 文件，没有它是正常的伴随 tablename.frm 文件。如果您将该 .ibd 文件移动到一个安全的临时位置（只是为了安全），那么应该可以解决问题。

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

但需要注意的是，请确保最初导致问题的原因是什么，例如长时间运行的查询、锁定的表等……已被清除。否则，当您第二次尝试时，您只会得到另一个孤立的 .ibd 文件。

回答by Technotronic

Xampp and Mamp Users

Xampp 和 Mamp 用户

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

通过 MySQL 导入数据库（清空后）时出现同样的错误。我发现我还有一个tablename.ibd文件，而所有其他文件都被删除了。我手动删除了它mysql/data/database_name，错误消失了。

回答by Harshul Vijay

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

对于 WAMP [Windows 7 Ultimate x64-bit] 用户：

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

我同意 DangerDave 所说的，所以我正在为WAMP 用户提供答案。

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

注意：首先，您必须转到您的..\WAMP\Bin\MySQL\MySQL[您的 MySQL 版本]\Data文件夹。

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

• 双击包含违规表的数据库文件夹将其打开
• 不应该有文件[Your offending MySQL table name].frm，而应该有文件[Your offending MySQL table name].ibd
• 删除 [Your offending MySQL table name].ibd
• 然后，也从回收站中删除它
• 然后在数据库上运行你的 MySQL 查询，你就完成了

回答by Accountant ?

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

如果.idb删除后再次重新创建，请阅读此答案。

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

这就是它如何与我合作。我有.idb没有对应.frm的.idb文件，每当我删除文件时，数据库都会重新创建它。我在 MySQL文档中的一行中找到了解决方案（表空间不存在部分）

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.

1- 在其他数据库目录中创建匹配的 .frm 文件，并将其复制到孤立表所在的数据库目录。

2- 为原始表发出 DROP TABLE。这应该会成功删除表，并且 InnoDB 应该向错误日志打印一条警告，指出 .ibd 文件丢失。

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

我复制了另一个表.frm文件并将其命名为我丢失的表，然后进行正常的删除表查询，瞧，它工作正常并且表被正常删除！

my system is XAMPP on windows MariaDB v 10.1.8

我的系统是 Windows MariaDB v 10.1.8 上的 XAMPP

回答by Andrey Radomanov

In my case the only work solution was:

就我而言，唯一的工作解决方案是：

1. CREATE TABLE bad_tableENGINE=MyISAM ...
2. rm bad_table.ibd
3. DROP TABLE bad_table

1. 创建表bad_table引擎=MyISAM ...
2. rm bad_table.ibd
3. 掉落表 bad_table

回答by Chris

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...

这正是我在 Fedora 上的 mariadb 10.2.16 中所做的，当时我有一个表，该表显示的日志文件中的错误与我想的完全相同......

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...

drop table 不能像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

然后在 /var/lib/mysql/database_name 目录中，我以 root 身份执行以下操作，确认覆盖 innodb_table.ibd 导致我们出现问题

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

然后回到 mysql 控制台，我在两个表上都发出了成功的 drop 命令

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 *

编辑：我打算添加一个

restorecon -Rv /var/lib/mysql/database_name 

复制数据库后的命令以按应有的方式获取所有 selinux 上下文，即使我们几乎立即从数据库中删除它们，但在替代方案中，您可以将 --archive 或 -a 选项添加到两个 cp命令，所以是的，实际上存档选项缩短了这个：

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

以下是我认为更好的内容，它保留了为已经制作的表设置的 selinux 上下文。

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

我已经将上面较长的命令列表替换为较短的列表，这些列表仍然可以用 * 缩短

回答by jcarlosweb

In my case:

就我而言：

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

首先tableName.ibd从 Mysql 中删除数据库目录，然后运行：

ALTER TABLE tableName DISCARD TABLESPACE;
DROP TABLE tableName;

回答by morgan

I got the same error running it on wampserver while trying to create a users table. I found a users.ibd file and after I deleted this file, I ran the migrate command again and it worked. The file on my windows machine was located in wamp/bin/mysql/mysql5.6.12/data/myproject.

在尝试创建用户表时，我在 wampserver 上运行它时遇到了同样的错误。我找到了一个 users.ibd 文件，在删除这个文件后，我再次运行了 migrate 命令并且它起作用了。我的 Windows 机器上的文件位于 wamp/bin/mysql/mysql5.6.12/data/myproject。

回答by Bhavin Rana

Solution

解决方案

However, the easier option is this: restart MySQL, then do the same four steps as follows:

但是，更简单的选择是：重新启动 MySQL，然后执行以下四个相同的步骤：

1) created a dummy table in the database;
2) discarded its tablespace;
3) moved the .ibd file into the database folder on the system;
4) attached the tablespace back to the table

This way, the tablespace id on the data dictionary and the file matched; thus importing the tablespace succeeded.

这样，数据字典上的表空间id与文件匹配；因此导入表空间成功。

This can give you greater confidence in dealing with some of the InnoDB "gotcha's" during the recovery process or even file transfers.

这可以让您更有信心在恢复过程甚至文件传输期间处理一些 InnoDB“陷阱”。

ref

参考

回答by Mahmoud Rabea

Here is the solution steps:

下面是解决步骤：

1. backup your database (structure with drop option and data)
2. stop mysql engine service
3. remove the database directory manually from inside mysql/data
4. start mysql engine
5. create new database with any name different from your corrupted database
6. create single table with the name of the corrupted table inside the new database (this is the secret). and it is better to create the table with exactly the same structure.
7. rename the database to the old corrupted database
8. restore your backup and your table will be working fine.

1. 备份您的数据库（具有删除选项和数据的结构）
2. 停止mysql引擎服务
3. 从 mysql/data 中手动删除数据库目录
4. 启动mysql引擎
5. 使用与损坏的数据库不同的任何名称创建新数据库
6. 使用新数据库中损坏的表的名称创建单个表（这是秘密）。最好创建具有​​完全相同结构的表。
7. 将数据库重命名为旧的损坏数据库
8. 恢复您的备份，您的表将正常工作。