俩二合一了

其实我遇到的问题不是这个 但是看着这文章点赞贼多 收藏一下

最近在数据库转移,库中有的表需要用到事务,所以引擎设置成了InnoDB,无意间把表的frm文件给删除了,想要删除重建提示错误,直接创建也会提示此表已经存在,是删也删不掉,建也建不了,网上查了下说是因为表在 innodb 内部数据字典中已存在,没有相对应的 .frm 文件,在 innodb 的数据文件中就形成了一个孤表(orphaned table)。

经查询在下面的文章中找到了答案:

table test/parent already exists in innodb internal data dictionary.
have you deleted the .frm file and not used drop table?
have you used drop database for innodb tables in mysql version <= 3.23.43?
see the restrictions section of the innodb manual.
you can drop the orphaned table inside innodb creating an innodb table with the same name in another database and moving the .frm file to the current database.
then mysql thinks the table exists, and drop table will innodb: succeed.

意思就是说:你可以通过在另外一个数据库中创建一个相同名字的innodb表,并把.frm拷贝到这个数据库目录下,数据库就会认为这个表存在,再执行drop table就可以删除掉了。

http://www.xuejiehome.com/blread-1701.html

在数据库备份时添加 DROPTABLE IF EXISTS 语句 仍然提示 1050 – Table’`test`.`mytable`’ already exists 错误时,发现网上资料很少,找了好久发现了解决方法 :

http://www.xuejiehome.com/blread-1701.html

说是因为表在 innodb 内部数据字典中已存在,没有相对应的 .frm 文件,在 innodb的数据文件中就形成了一个孤表(orphaned table)。

解决方式: 在另外一个数据库中创建一个相同名字的innodb表,并把.frm拷贝到这个数据库目录下,数据库就会认为这个表存在,再执行droptable就可以删除掉了。

In fact, the problem I encountered is not this, but after reading this article, like the thief and more favorites, I have recently transferred to the database. Some tables in the library need to use transactions, so the engine is set to InnoDB, and the table is inadvertently changed. The frm file has been deleted. If you want to delete and rebuild, it will prompt an error. If you create it directly, you will also be prompted that the table already exists. Even if you delete it, you cannot delete it. Existing, there is no corresponding .frm file, an orphaned table is formed in the innodb data file.
After querying, I found the answer in the following article: table test/parent already exists in innodb internal data dictionary.
have you deleted the .frm file and not used drop table?
have you used drop database for innodb tables in mysql version <= 3.23.43?
see the restrictions section of the innodb manual.
you can drop the orphaned table inside innodb creating an innodb table with the same name in another database and moving the .frm file to the current database.
then mysql thinks the table exists, and drop table will innodb: succeed.
It means: you can create an innodb table with the same name in another database, and copy .frm to this database directory, the database will think that the table exists, and then execute drop table to delete it.
http://www.xuejiehome.com/blread-1701.html When the DROPTABLE IF EXISTS statement is added during database backup, it still prompts 1050 – Table’`test`.`mytable`’ already exists error. After a long time, I found a solution: http://www.xuejiehome.com/blread-1701.html said that because the table already exists in the innodb internal data dictionary, there is no corresponding .frm file, it is in the innodb data file. An orphaned table is formed.
Solution: Create an innodb table with the same name in another database, and copy .frm to this database directory, the database will consider the table to exist, and then execute droptable to delete it.

发表评论

后才能评论