MySQL 5.6.10 InnoDB无法创建表，因为表已存在 [英] MySQL 5.6.10 InnoDB cannot create table as table already exists

问题描述

我有一个非常有趣的案例.我的几个InnoDB表以某种方式损坏了.这些表的.frm文件消失了，只剩下了.ibd文件.

I have a very interesting case. Several of my InnoDB tables got somehow corrupted. The .frm files of the tables disappeared and only the .ibd files were left.

我认为没问题，我将从备份中还原这些表，因此我删除了.frm文件，并尝试使用相同的名称创建新表，但是每当我得到该表已存在"时，都会显示.ibd文件已创建，但.frm未创建.

I thought no problem, I will restore these tables from the backup, so I deleted the .frm files and try to create the new tables with the same name, but every time I get "the table already exists", the .ibd file gets created, but the .frm not.

基本上现在我无法在给定数据库中使用给定名称创建InnoDB表.我唯一的解决方案是创建MyISAM表，但是我仍然想知道如何解决这个问题.

Basically now I cannot create InnoDB tables with the given names in the given database. My only solution was to create MyISAM tables instead, but I would still like to know how to resolve this.

下面是事件的详细日志:

Below is a detailed log of the events:

1. 尝试创建表:

创建表员工( employee_id varchar(20)NOT NULL，
主键( employee_login_id )使用BTREE)ENGINE = InnoDB默认CHARSET = utf8 PACK_KEYS = 1 ROW_FORMAT = COMPRESSED

CREATE TABLE employee ( employee_id varchar(20) NOT NULL,
PRIMARY KEY (employee_login_id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=1 ROW_FORMAT=COMPRESSED

表已存在

1. 尝试删除该表以显示该表不存在:

DROP TABLE员工

DROP TABLE employee

表不存在

1. 我手动删除了employee.ibd文件(没有employee.frm文件)

1. I manually deleted the employee.ibd file (there is no employee.frm file)

再次尝试#1并再次收到表已存在"

Try #1 again and receive the "table already exists" again

我已经重新启动MySQL，没有任何变化.我已经重新启动了整个服务器，没有任何变化.

I have restarted MySQL, no change. I have restarted the whole server, no change.

我无法再在此表空间中创建名称为"employee"的InnoDB表.

I cannot create an InnoDB table with the name "employee" in this tablespace anymore.

有什么主意吗?

推荐答案

有关表的信息存储在两个位置:

Information about a table is stored in two places:

1. 服务器范围的table.frm文件
2. 特定于存储引擎的InnoDB字典

这两个必须是同步的，但是没有可靠的机制可以强制实现这种一致性.由于多种原因，InnoDB词典不同步.

These two must be in-sync, but there is no reliable mechanism to enforce this consistency. Due to number of reasons InnoDB dictionary gets out of sync.

在您的情况下，词典中有一个孤立的记录.您需要删除它.

In your case there is an orphaned record in the dictionary. You need to delete it.

InnoDB没有提供任何手动修改字典记录的方法.但是，您可以制作伪造的table.frm文件(如果innodb_file_per_table = ON，然后是table.ibd)，也可以将其放入数据库目录中并删除该表.

InnoDB doesn't provide any way to manually modify the dictionary records. But you can craft a fake table.frm (if innodb_file_per_table=ON then table.ibd too) files, put it into the database directory and drop the table.

旧版本的InnoDB可能会抱怨SPACENO不匹配.然后检查如何修复InnoDB字典

Old versions of InnoDB might complain about mismatching SPACENO. Then check how to fix InnoDB dictionary

这篇关于MySQL 5.6.10 InnoDB无法创建表，因为表已存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！