php XAMPP/MySQL: 重启 MySQL 后无法打开单表表空间文件 .\mysql\innodb_index_stats.ibd

XAMPP/MySQL: could not open single-table tablespace file .\mysql\innodb_index_stats.ibd after restart of MySQL

提问by leiseliesel

I've installed Drupal on my local XAMPP Server. It worked all fine, no problems with including and working with the database/site till i restarted XAMPP. Since then I get the following at my logfile:

我已经在本地 XAMPP 服务器上安装了 Drupal。一切正常，在我重新启动 XAMPP 之前，包括和使用数据库/站点都没有问题。从那时起，我在我的日志文件中得到以下信息：

2013-09-02 16:18:46 2544 [Note] Plugin 'FEDERATED' is disabled.

2013-09-02 16:18:46 3e8 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.

2013-09-02 16:18:46 2544 [Note] InnoDB: The InnoDB memory heap is disabled

2013-09-02 16:18:46 2544 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions

2013-09-02 16:18:46 2544 [Note] InnoDB: Compressed tables use zlib 1.2.3

2013-09-02 16:18:46 2544 [Note] InnoDB: Not using CPU crc32 instructions

2013-09-02 16:18:46 2544 [Note] InnoDB: Initializing buffer pool, size = 16.0M

2013-09-02 16:18:46 2544 [Note] InnoDB: Completed initialization of buffer pool

2013-09-02 16:18:46 2544 [Note] InnoDB: Highest supported file format is Barracuda.

2013-09-02 16:18:47 2544 [Note] InnoDB: The log sequence numbers 1600614 and 1600614 in ibdata files do not match the log sequence number 1600644 in the ib_logfiles!

2013-09-02 16:18:47 2544 [Note] InnoDB: Database was not shutdown normally!

2013-09-02 16:18:47 2544 [Note] InnoDB: Starting crash recovery.

2013-09-02 16:18:47 2544 [Note] InnoDB: Reading tablespace information from the .ibd files...

2013-09-02 16:18:47 2544 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace drupal/variable uses space ID: 2 at filepath: .\drupal\variable.ibd. Cannot open tablespace mysql/innodb_index_stats which uses space ID: 2 at filepath: .\mysql\innodb_index_stats.ibd

InnoDB: Error: could not open single-table tablespace file .\mysql\innodb_index_stats.ibd

InnoDB: We do not continue the crash recovery, because the table may become

InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.

InnoDB: To fix the problem and start mysqld:

InnoDB: 1) If there is a permission problem in the file and mysqld cannot

InnoDB: open the file, you should modify the permissions.

InnoDB: 2) If the table is not needed, or you can restore it from a backup,

InnoDB: then you can remove the .ibd file, and InnoDB will do a normal

InnoDB: crash recovery and ignore that table.

InnoDB: 3) If the file system or the disk is broken, and you cannot remove

InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf

InnoDB: and force InnoDB to continue crash recovery here.

2013-09-02 16:18:46 2544 [注意] 插件“联邦”被禁用。

2013-09-02 16:18:46 3e8 InnoDB：警告：不推荐使用 innodb_additional_mem_pool_size。此选项可能会在未来版本中与选项 innodb_use_sys_malloc 和 InnoDB 的内部内存分配器一起删除。

2013-09-02 16:18:46 2544 [注意] InnoDB：InnoDB 内存堆被禁用

2013-09-02 16:18:46 2544 [注意] InnoDB：互斥锁和 rw_locks 使用 Windows 互锁函数

2013-09-02 16:18:46 2544 [注意] InnoDB：压缩表使用 zlib 1.2.3

2013-09-02 16:18:46 2544 [注意] InnoDB：不使用 CPU crc32 指令

2013-09-02 16:18:46 2544 [注意] InnoDB：初始化缓冲池，大小 = 16.0M

2013-09-02 16:18:46 2544 [注意] InnoDB：缓冲池初始化完成

2013-09-02 16:18:46 2544 [注意] InnoDB：支持的最高文件格式是梭子鱼。

2013-09-02 16:18:47 2544 [注意] InnoDB：ibdata 文件中的日志序列号 1600614 和 1600614 与 ib_logfiles 中的日志序列号 1600644 不匹配！

2013-09-02 16:18:47 2544 [注意] InnoDB：数据库没有正常关闭！

2013-09-02 16:18:47 2544 [注意] InnoDB：启动崩溃恢复。

2013-09-02 16:18:47 2544 [注意] InnoDB：从 .ibd 文件中读取表空间信息...

2013-09-02 16:18:47 2544 [错误] InnoDB：试图打开以前打开的表空间。以前的表空间 drupal/variable 在文件路径中使用空间 ID：2：.\drupal\variable.ibd。无法打开在文件路径中使用空间 ID：2 的表空间 mysql/innodb_index_stats：.\mysql\innodb_index_stats.ibd

InnoDB：错误：无法打开单表表空间文件 .\mysql\innodb_index_stats.ibd

InnoDB：我们不继续崩溃恢复，因为表可能会变成

InnoDB：如果我们无法将 InnoDB 日志中的日志记录应用到它，则损坏。

InnoDB：解决问题并启动 mysqld：

InnoDB：1）如果文件存在权限问题，mysqld不能

InnoDB：打开文件，应该修改权限。

InnoDB：2）如果不需要该表，或者您可以从备份中恢复它，

InnoDB：然后你可以删除.ibd文件，InnoDB会做一个正常的

InnoDB：崩溃恢复并忽略该表。

InnoDB: 3) 如果文件系统或磁盘损坏，无法删除

InnoDB：.ibd文件，可以在my.cnf中设置innodb_force_recovery > 0

InnoDB：并在此处强制 InnoDB 继续崩溃恢复。

I looked for a solution via google but it seems to be a problem just with the drupal database because it's able to connect with MySQL if I remove the database.

我通过谷歌寻找了一个解决方案，但它似乎只是 Drupal 数据库的问题，因为如果我删除数据库，它能够与 MySQL 连接。

I hope someone could help me :(.

我希望有人可以帮助我:(。

回答by Panos Angelopoulos

Move (DON'T DELETE) those files, into another folder:

将这些文件移动（不要删除）到另一个文件夹中：

innodb_index_stats.frm
innodb_table_stats.frm
slave_master_info.frm
slave_relay_log_info.frm
slave_worker_info.frm

and .ibd files with the same filename:

和 .ibd 文件具有相同的文件名：

innodb_index_stats.ibd
innodb_table_stats.ibd
slave_master_info.ibd
slave_relay_log_info.ibd
slave_worker_info.ibd

Try start MySQL.

尝试启动 MySQL。

回答by Charles P.

You can solve this problem by adding a line in your mysql config file: my.cnf or my.ini (depends on your distro)

您可以通过在 mysql 配置文件中添加一行来解决此问题：my.cnf 或 my.ini（取决于您的发行版）

just under [mysqld] add this line: innodb_force_recovery = 1

在 [mysqld] 下添加这一行：innodb_force_recovery = 1

..
[mysqld]
innodb_force_recovery = 1 
..

Then restart your MySql Server. You could have lost some data but you'll get the server working again with your data.

然后重新启动您的 MySql 服务器。您可能会丢失一些数据，但您将使服务器再次使用您的数据。

Regards,

问候，

回答by Janis Benda

dev_khan, try restarting MySQL in Read-Only mode with the innodb_force_recoveryoption enabled:

dev_khan，尝试在innodb_force_recovery启用该选项的情况下以只读模式重新启动 MySQL ：

1. Edit my.cnf - find the line: # innodb_force_recovery = 2
2. Comment the line in (remove the #)
3. Restart MySQL to let the MySQL engine repair itself.
4. Comment the innodb_force_recoveryline in again (add #)
5. Restart MySQL again and you have full access again without a Read-Only-Restriction.

1. 编辑 my.cnf - 找到该行： # innodb_force_recovery = 2
2. 注释（删除#）中的行
3. 重启 MySQL 让 MySQL 引擎自我修复。
4. innodb_force_recovery再次注释该行（添加#）
5. 再次重新启动 MySQL，您将再次拥有完全访问权限，而没有只读限制。

Greetings from Germany

来自德国的问候

回答by mandrill

This happens with Wordpress too. It only seems to happen with the latest version as I've rolled back to previous versions of AMPPS and it works fine without throwing up this innodb issue.

Wordpress 也会发生这种情况。它似乎只发生在最新版本中，因为我已经回滚到以前版本的 AMPPS 并且它工作正常而不会抛出这个 innodb 问题。