如果不涉及软件错误，是否会发生MySQL InnoDB损坏，导致存储引擎无法从中恢复? [英] Can MySQL InnoDB corruption occur that the storage engine cannot recover from if there is NO software bug involved?

问题描述

我正在运行MySQL 5.1的嵌入式Linux系统上工作.在极少数情况下，质量检查会报告由于mysqld无法启动而导致系统无法正常启动.如果发生这种情况，MySQL日志文件看起来类似于此摘录:

I am working on an embedded Linux system running MySQL 5.1. In rare cases QA reports systems not starting properly because of mysqld not starting. If this happens the MySQL log files looks similar to this excerpt:

150716 14:29:42  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
150716 14:29:42  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 133478.
InnoDB: Doing recovery: scanned up to log sequence number 0 133478
150716 14:29:42  InnoDB: Started; log sequence number 0 133478
/usr/libexec/mysqld: Unknown error 130
150716 14:29:42 [ERROR] Can't open the mysql.plugin table. Please run the mysql_upgrade script to create it.
150716 14:29:42 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'host'

这可能是由于以下事实:这是一台不带电源开关的嵌入式设备，通过拔下网络电缆关闭了电源，从而导致PoE供电中断.当然，在这种情况下，mysqld将异常终止.

This is probably due to the fact that this is an embedded device without a power switch and it is powered off by unplugging the network cable and thus killing the PoE supply. In this case of course mysqld will be terminated quite abnormaly.

my.cnf除了大小限制为18 MB外，没有任何花哨的内容.

my.cnf contains nothing fancy besides a size limitation to 18 MB.

问题

如果不涉及错误(在MySQL本身或例如错误的fsync()实现中)，InnoDB表是否有可能损坏并且无法恢复?即使所有软件组件都正常工作，是否存在可能导致损坏(无法从数据库中恢复数据库)的状况?可以在正常运行"中发生断电的环境中安全地使用这样的数据库吗? 我最终要问的是: 是否有寻找解决此问题的方法，还是没有解决此问题的方法?

Is it possible that InnoDB tables get corrupted and recovery is NOT possible if there is NO bug involved (either in MySQL itself or e.g. a faulty fsync() implementation)? Are there situtations that can cause a corruption (that the DB cannot be recovered from) even if all software components are working correctly? Can such a DB be safely used in an environment where power failures occur "in normal operation"? What I am ultimately asking is: Is there a point searching for a fix to this problem or is there no fix to this problem whatsoever?

推荐答案

我终于找到了根本原因. InnoDB表实际上不会发生此问题，而系统表实际上会发生此问题.

I finally found the root cause. The problem does not actually occur with the InnoDB tables, but with the system tables.

在MySQL 5.1系统中，表是使用MyISAM引擎存储的.这使得这些表在功耗方面非常脆弱.

In MySQL 5.1 system tables are stored using the MyISAM engine. This makes these tables very fragile on power loss.

对于所有系统表，MYI(索引)和MYD(数据)文件的内容都丢失了.

For all system tables the content of the MYI (index) and MYD (data) files were lost.

缺少这些数据-当然-其余数据库有问题...

Missing this data - of course - the rest of the databases had a problem...

对我来说重要的提示是

mysql.plugin表

mysql.plugin table

最后查看了包含系统表的目录，发现它们正在使用MyISAM存储引擎.那么后果就很明显了.

Finally looked into the directory containing the system tables and saw they were using the MyISAM storage engine. Then the consequences are quite obvious.

(仅)解决方案:

转到较新的版本(在我的情况下，我使用了MariaDB). 您不能将InnoDB用作MySQL 5.1上系统表的存储引擎.

Go to a newer version (I used MariaDB in my case). You cannot use InnoDB as storage engine for the system tables on MySQL 5.1.

这篇关于如果不涉及软件错误，是否会发生MySQL InnoDB损坏，导致存储引擎无法从中恢复?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！