参考官方文档：
https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html




以下一般准则适用于对InnoDB问题进行故障排除：

• 当操作失败或您怀疑有bug时，请查看MySQL服务器错误日志（请参见第5.4.2节“错误日志”）。 第B.3节“服务器错误代码和消息”提供了您可能遇到的一些常见的InnoDB特定错误的故障排除信息。
• 如果失败与死锁相关，请在启用innodb_print_all_deadlocks选项的情况下运行，以便将有关每个死锁的详细信息打印到MySQL服务器错误日志中。
• 与InnoDB数据字典有关的问题包括失败的CREATE TABLE语句（孤立表文件），无法打开InnoDB文件，并且 system cannot find the path specified errors。
• 进行故障排除时，通常最好从命令提示符运行MySQL服务器，而不是通过mysqld_safe或Windows服务。 然后，您可以看到mysqld打印到控制台的内容，因此可以更好地掌握正在发生的事情。 在Windows上，使用--console选项启动mysqld以将输出定向到控制台窗口。
• 启用InnoDB监视器以获取有关问题的信息（请参见第14.17节“InnoDB监视器”）。 如果问题与性能相关，或者您的服务器似乎挂起，则应启用标准监视器以打印有关InnoDB内部状态的信息。 如果问题出在锁定上，请启用锁定监视器。 如果问题出在表创建，表空间或数据字典操作上，请参考InnoDB信息方案系统表来检查InnoDB内部数据字典的内容。

InnoDB在以下条件下临时启用标准InnoDB Monitor输出：

• 一个长信号量等待
• InnoDB无法在缓冲池中找到空闲块
• 超过67％的缓冲池被lock heaps或自适应哈希索引占用
• 如果您怀疑表已损坏，请在该表上运行CHECK TABLE。

14.21.1 排除 InnoDB I/O 问题


InnoDB I / O问题的故障排除步骤取决于问题发生的时间：MySQL服务器启动期间，或者由于文件系统级别的问题而导致DML或DDL语句失败时的正常操作期间。


初始化问题


如果InnoDB尝试初始化其表空间或其日志文件时出现问题，请删除InnoDB创建的所有文件：所有ibdata文件和所有ib_logfile文件。 如果您已经创建了一些InnoDB表，那么还要从MySQL数据库目录中删除这些表的相应.frm文件，以及任何.ibd文件（如果您使用多个表空间）。 然后再次尝试创建InnoDB数据库。 要进行最简单的故障排除，请从命令提示符启动MySQL服务器，以便查看发生的情况。


运行时的问题


如果InnoDB在文件操作期间打印操作系统错误，通常问题具有以下解决方案之一：

• 确保InnoDB数据文件目录和InnoDB日志目录存在。
• 确保mysqld具有在这些目录中创建文件的访问权限。
• 确保mysqld可以读取正确的my.cnf或my.ini选项文件，以便它以您指定的选项来启动。
• 确保磁盘未满，并且您没有超过任何磁盘配额。
• 确保为子目录和数据文件指定的名称不会发生冲突。
• 检查innodb_data_home_dir和innodb_data_file_path值的语法。 特别是，innodb_data_file_path选项中的任何MAX值都是硬限制，超过该限制会导致致命错误。

14.21.2 强制 InnoDB 恢复


要调查数据库页面损坏，您可以使用SELECT ... INTO OUTFILE从数据库转储表。 通常，以这种方式获得的大多数数据是完整的。 严重损坏可能导致SELECT * FROM tbl_name语句或InnoDB后台操作崩溃或断言，甚至导致InnoDB前滚恢复崩溃。 在这种情况下，您可以使用innodb_force_recovery选项强制InnoDB存储引擎启动，同时防止后台操作运行，以便您可以转储表。 例如，您可以在重新启动服务器之前将以下行添加到选项文件的[mysqld]部分：


[mysqld]
innodb_force_recovery = 1



警告


仅在紧急情况下将innodb_force_recovery设置为大于0的值，以便您可以启动InnoDB并转储表。 在此之前，请确保您拥有数据库的备份副本，以备需要重新创建时使用。 值为4或更大可能会永久损坏数据文件。 仅在测试数据库的单独物理副本上，成功测试设置后，再在生产服务器实例上使用innodb_force_recovery4或更高的值。 强制InnoDB恢复时，应始终以innodb_force_recovery = 1开始，并且只在必要时以递增方式增加值。




innodb_force_recovery默认为0（没有强制恢复的正常启动）。 innodb_force_recovery的允许非零值为1到6.较大的值包括较小值的功能。 例如，3包括1和2的所有功能。


如果您能够以innodb_force_recovery值为3或更低的值转储表，那么您相对安全，只有损坏的单个页面上的某些数据会丢失。 值为4或更高被认为是危险的，因为数据文件可能会永久损坏。 值6被认为是激烈的，因为数据库页面处于过时状态，这反过来可能会在B树和其他数据库结构中引入更多损坏


作为安全措施，当innodb_force_recovery大于0时，InnoDB会阻止INSERT，UPDATE或DELETE操作。如果innodb_force_recovery设置为4或更高，则将InnoDB置于只读模式。

• 1 (SRV_FORCE_IGNORE_CORRUPT)

即使检测到损坏的页面，也允许服务器运行。 尝试使SELECT * FROM tbl_name跳过损坏的索引记录和页面，这有助于转储表。

• 2（ SRV_FORCE_NO_BACKGROUND）

防止主线程和任何清除线程运行。 如果在清除操作期间发生崩溃，则此恢复值会阻止它。

• 3（ SRV_FORCE_NO_TRX_UNDO）

崩溃恢复后不运行事务回滚。

• 4（ SRV_FORCE_NO_IBUF_MERGE）

阻止插入缓冲区合并操作。 如果它们会导致崩溃，则不会这样做。 不计算表统计信息。 此值可能会永久损坏数据文件。 使用此值后，请准备删除并重新创建所有二级索引。 将InnoDB设置为只读。

• 5（ SRV_FORCE_NO_UNDO_LOG_SCAN）

启动数据库时不查看撤消日志：InnoDB甚至将未完成的事务视为已提交。 此值可能会永久损坏数据文件。 将InnoDB设置为只读。

• 6（ SRV_FORCE_NO_LOG_REDO）

不执行与恢复相关的重做日志前滚。 此值可能会永久损坏数据文件。 使数据库页面处于过时状态，这反过来可能会在B树和其他数据库结构中引入更多损坏。 将InnoDB设置为只读。



您可以在表上进行SELECT以转储它们。 如果innodb_force_recovery值为3或更小，您可以DROP或CREATE表。MySQL 5.7.17之前 innodb_force_recovery值大于3时也支持DROP TABLE。 从MySQL 5.7.18开始，不允许在innodb_force_recovery值大于4的情况下使用DROP TABLE。


如果您知道给定的表导致回滚崩溃，则可以删除它。 如果遇到由大量导入失败或ALTER TABLE导致的回滚失控，则可以kill mysqld进程并将innodb_force_recovery设置为3以使数据库无需回滚，然后DROP导致回滚失控的表


如果表数据中的损坏阻止您转储整个表内容，则具有ORDER BY primary_key DESC子句的查询可能能够在损坏的部分之后转储表的一部分。


如果启动InnoDB需要高innodb_force_recovery值，则可能被损坏的数据结构可能导致复杂查询(包含WHERE,ORDER BY 或者其他子句)失败。 在这种情况下，您可能只能运行基本的SELECT * FROM t查询。




14.21.3 排查 InnoDB 数据字典操作


有关表定义的信息存储在.frm文件和InnoDB数据字典中。 如果移动.frm文件，或者如果服务器在数据字典操作过程中崩溃，则这些信息源可能会变得不一致。


如果数据字典损坏或一致性问题阻止您启动InnoDB，请参见第14.21.2节“强制InnoDB恢复”以获取有关手动恢复的信息。




由于孤立表而导致CREATE TABLE失败


不同步数据字典的症状是CREATE TABLE语句失败。 如果发生这种情况，请查看服务器的错误日志。 如果日志表明该表已存在于InnoDB内部数据字典中，则InnoDB表空间文件中有一个孤立表，该表没有相应的.frm文件。 错误消息如下所示：


InnoDB: Error: table test/parent already exists in
InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.



您可以按照错误消息中给出的说明删除孤立表。 如果仍然无法成功使用DROP TABLE，则问题可能是由于mysql客户端中的名称完整性。 要解决此问题，请使用--skip-auto-rehash选项启动mysql客户端，然后再次尝试DROP TABLE。 （在名称完整性时，mysql尝试构造一个表名列表，当出现如上所述的问题时，它会失败。）


不能打开数据文件


启用innodb_file_per_table（默认值）后，如果缺少file-per-table 表空间文件（.ibd文件），则启动时可能会出现以下消息：


[ERROR] InnoDB Operating system error number 2 in a file operation.
[ERROR] InnoDB: The error means the system cannot find the path specified.
[ERROR] InnoDB: Cannot open datafile for read-only: './test/t1.ibd' OS error: 71
[Warning] InnoDB: Ignoring tablespace `test/t1` because it could not be opened.


要解决这些消息，请发出DROP TABLE语句以从数据字典中删除有关缺失表的数据。


不能打开文件 错误


不同步数据字典的另一个症状是MySQL打印出一个无法打开InnoDB文件的错误：
ERROR 1016: Can't open file: 'child2.ibd'. (errno: 1)

在错误日志中，您可以找到如下消息：


InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?


这意味着在InnoDB中有一个没有相应表的孤立.frm文件。 您可以通过手动删除孤立的.frm文件来删除它。




孤儿中间表


如果MySQL存在 in-place的 ALTER TABLE操作（ALGORITHM = INPLACE）中间退出，则可能会留下一个占用系统空间的孤立中间表。 此外，为空的常规表空间中的孤立中间表会阻止您删除常规表空间。 本节介绍如何识别和删除孤立中间表。


中间表名称以#sql-ib前缀开头（例如，＃sql-ib87-856498050）。 随附的.frm文件具有#sql- *前缀，并且命名方式不同（例如，＃sql-36ab_2.frm）。


要识别系统上的孤立中间表，可以查询INFORMATION_SCHEMA.INNODB_SYS_TABLES。 查找以#sql开头的表名。 如果原始表驻留在 file-per-table 表空间中，则孤立中间表的表空间文件（#sql - * .ibd文件）应该在数据库目录中可见。




SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';


要删除孤立中间表，请执行以下步骤：


1.在数据库目录中，重命名#sql - *。frm文件以匹配孤立中间表的基本名称：


shell> mv #sql-36ab_2.frm #sql-ib87-856498050.frm


注意：
如果没有.frm文件，则可以重新创建它。 .frm文件必须与孤立中间表具有相同的表方案（它必须具有相同的列和索引），并且必须放在孤立中间表的数据库目录中。


2.通过发出DROP TABLE语句删除孤立中间表，在表的名称前加上＃mysql50＃，并将表名括在反引号中。 例如：


mysql> DROP TABLE `#mysql50##sql-ib87-856498050`;


#mysql50＃前缀告诉MySQL忽略MySQL 5.1中引入的文件名安全编码。 将表名括在反引号中是因为需要在表名上使用特殊字符（如“＃”）。




注意：
如果在将表移动到其他表空间的就地ALTER TABLE操作期间发生崩溃，则恢复过程会将表还原到其原始位置，但会在目标表空间中保留孤立中间表。



孤儿临时表


如果MySQL在表复制ALTER TABLE操作（ALGORITHM = COPY）的过程中退出，则可能会留下一个占用系统空间的孤立临时表。 此外，否则为空的常规表空间中的孤立临时表会阻止您删除常规表空间。 本节介绍如何识别和删除孤立临时表。


孤立临时表名称以#sql-前缀开头（例如，＃sql-540_3）。 随附的.frm文件与孤立临时表具有相同的基本名称。


注意：
如果没有.frm文件，则可以重新创建它。 .frm文件必须与孤立临时表具有相同的表模式（它必须具有相同的列和索引），并且必须放在孤立临时表的数据库目录中。




要识别系统上的孤立临时表，可以查询INFORMATION_SCHEMA.INNODB_SYS_TABLES。 查找以#sql开头的表名。 如果原始表驻留在每个表的文件表空间中，则孤立临时表的表空间文件（#sql - * .ibd文件）应该在数据库目录中可见。


SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';


要删除孤立临时表，请通过发出DROP TABLE语句来删除表，在表的名称前加上＃mysql50＃，并将表名括在反引号中。 例如：


mysql> DROP TABLE `#mysql50##sql-540_3`;


#mysql50＃前缀告诉MySQL忽略MySQL 5.1中引入的文件名安全编码。 将表名括在反引号中是需要在表名上使用特殊字符（如“＃”）执行SQL语句。




表空间不存在


启用innodb_file_per_table后，如果缺少.frm或.ibd文件（或两者），则可能会出现以下消息：
InnoDB: in InnoDB data dictionary has tablespace id N,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.


如果发生这种情况，请尝试以下过程来解决此问题：

• 在其他一些数据库目录中创建匹配的.frm文件，并将其复制到孤立表所在的数据库目录中。
• 为原始表发出DROP TABLE。 这应该成功删除表，InnoDB应该在错误日志中打印出.ibd文件丢失的警告。

恢复孤立file-per-table ibd文件


此过程描述如何将 file-per-table的 .ibd文件还原到另一个MySQL实例。 如果系统表空间丢失或不可恢复，并且您希望在新的MySQL实例上恢复.idb文件备份，则可以使用此过程。


一般表空间.ibd文件不支持该过程。


该过程假定您只有.ibd文件备份，您正在恢复到最初创建孤立.idb文件的相同版本的MySQL，并且.idb文件备份是干净的。 有关创建干净备份的信息，请参见第14.8.1.3节“移动或复制InnoDB表”。


第14.7.6节“将每个表的表空间复制到另一个实例”中概述的表空间复制限制适用于此过程。


1.在新的MySQL实例上，在同名的数据库中重新创建表。


mysql> CREATE DATABASE sakila;
mysql> USE sakila;
mysql> CREATE TABLE actor ( actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (actor_id), KEY idx_actor_last_name (last_name) )ENGINE=InnoDB DEFAULT CHARSET=utf8;


2.丢弃新创建的表的表空间。
mysql> ALTER TABLE sakila.actor DISCARD TABLESPACE;


3.将备份目录中的孤立.idb文件复制到新数据库目录。


shell> cp /backup_directory/actor.ibd path/to/mysql-5.7/data/sakila/


4.确保.ibd文件具有必要的文件权限。

5.导入孤立的.ibd文件。 发出警告，指示InnoDB将尝试导入文件而不进行模式验证。


mysql> ALTER TABLE sakila.actor IMPORT TABLESPACE; SHOW WARNINGS; Query OK, 0 rows affected, 1 warning (0.15 sec)


Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './sakila/actor.cfg', will attempt to import without schema verification


6.查询表以验证.ibd文件是否已成功还原。


mysql> SELECT COUNT(*) FROM sakila.actor;
+----------+
| count(*) |
+----------+
| 200 |
+----------+



14.21.4 InnoDB 错误处理


以下各项描述了InnoDB如何执行错误处理。 InnoDB有时只会回滚失败的语句，有时会回滚整个事务。

• 如果表空间中的文件空间不足，则会发生MySQL表错误，InnoDB会回滚SQL语句。
• 事务死锁导致InnoDB回滚整个事务。 发生这种情况时重试整个事务。

锁定等待超时会导致InnoDB仅回滚等待锁定并遇到超时的单个语句。 （要使整个事务回滚，请使用--innodb_rollback_on_timeout选项启动服务器。）如果使用当前行为，则重试该语句，如果使用--innodb_rollback_on_timeout，则重试整个事务。



在繁忙的服务器上，死锁和锁定等待超时都是正常的，应用程序必须知道它们可能会发生并通过重试来处理它们。 通过在事务对数据进行第一次更改和提交之间尽可能少的工作，来降低它们的可能性，因此锁可以保持最短的时间和最小的行数。 有时，在不同事务之间分配工作量，可能是实际且有帮助的。


当由于死锁或锁等待超时而发生事务回滚时，它会取消事务中语句的影响。 但是，如果start-transaction语句是START TRANSACTION或BEGIN语句，则回滚不会取消该语句。 在发生COMMIT，ROLLBACK或导致隐式提交的某些SQL语句之前，其他SQL语句将成为事务的一部分。

• 如果未在语句中指定IGNORE选项，则重复键错误将回滚SQL语句。
• 行太长错误回滚SQL语句。
• 其他错误主要由MySQL代码层检测（在InnoDB存储引擎级别之上），并且它们回滚相应的SQL语句。 在单个SQL语句的回滚中不会释放锁。

在隐式回滚期间，以及在执行显式ROLLBACK SQL语句期间，SHOW PROCESSLIST在状态列中显示Rolling bak的相关连接。