MySQL InnoDB:由于缺少.ibd文件，因此无法计算统计信息 [英] MySQL InnoDB: cannot calculate statistics because the .ibd file is missing

问题描述

环境:

Windows 7(最新XAMPP) 阿帕奇2.4.4 PHP 5.5 MySQL 5.6.11

Windows 7 (XAMPP latest) Apache 2.4.4 PHP 5.5 MySQL 5.6.11

我正在尝试从MySQL 5.1备份数据库并将其导入MySQL 5.6.

I am trying to backup a database from MySQL 5.1 and import it to MySQL 5.6.

在MySQL 5.1中，有一些MyISAM和InnoDB表.我使用mysqldump通过--add-drop-database开关将SQL文件转储出去.

In MySQL 5.1, there are some MyISAM and InnoDB tables. I use mysqldump to dump the SQL file out, with --add-drop-database switch.

现在，当我回到本地主机并使用MySQL工作台导入SQL文件时，会出现错误:

Now when I go back to my localhost and import the SQL file using MySQL workbench, an error occur:

InnoDB: cannot calculate statistics for table "database"."tables" because the .ibd file is missing.

我尝试使用以下方法删除数据库

I tried drop the database using:

drop schema database

它使MySQL 5.6崩溃，并显示如下错误:

And it crashes the MySQL 5.6, with error like this:

2013-09-10 17:18:23 fc4  InnoDB: Warning: MySQL is trying to drop database `database`.``
InnoDB: though there are still open handles to table `database`.`table`.

在my.ini中，我设置了:

In my.ini I set:

innodb_force_recovery = 4

我尝试过:

• 使用其他数据库名称创建一个新数据库并再次运行导入，无法创建所有innodb表.
• 从数据目录中正在运行的5.1服务器复制所有* .frm表，覆盖现有数据库，重新启动MySQL 5.6.11，无法访问所有innodb表.
• 如果我使用engine = InnoDB运行create table语句，它将失败并表示该表已经存在，实际上该表不存在.
• 如果我运行一个使用InnoDB的drop table语句，它将说该表不存在...

有人可以给我一些建议吗?

Can anyone please give me some advices on it?

谢谢.

推荐答案

我在Mac OS X上也遇到了类似的问题，但有一种奇怪的方式:我在5.6.19之前的版本上运行良好，然后升级到5.6.19，开始出现上述错误消息，但我的表均未使用大小写混合.

I had a similar problem on Mac OS X, but in a slightly bizarre way: I was running fine on some pre-5.6.19 version and then upgraded to 5.6.19, which started to give me the error message above, except none of my tables use mixed case.

事实证明，我的一个数据库使用大写字符作为首字母.长期以来，这种方法一直运行良好，但在次要版本升级后的今天早上失败了，并让我进行了2小时的搜索以查找问题所在.

As it turned out, one of my databases is using an uppercase character as its first letter. This has worked fine for a long time, but failed this morning after the minor version upgrade and sent me into a 2-hour search for what went wrong.

修复很简单:使用小写版本创建符号链接，重新启动mysqld，一切都很好.但是，即使我了解使表区分大小写的逻辑，也没有数据库名称含糊不清的危险，因为操作系统总是会阻止这种情况.

The fix is simple: create a symlink with the lower case version, restart mysqld and all is well. However, even though I understand the logic for making tables case-safe, there is no danger of the database name being ambiguous since the OS would always prevent that.

这篇关于MySQL InnoDB:由于缺少.ibd文件，因此无法计算统计信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！