14.22.3 InnoDB 数据字典操作故障排除

MySQL 8.0 参考手册 / 第 15 章 InnoDB 存储引擎 / 15.21 InnoDB 故障排除 / 14.22.3 InnoDB 数据字典操作故障排除

14.22.3 InnoDB 数据字典操作故障排除

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

如果数据字典损坏或一致性问题阻止您启动InnoDB，请参阅第 14.22.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客户端的name补全问题。要解决此问题，请使用该选项启动mysql客户端并重--skip-auto-rehash 试DROP TABLE。（打开名称补全后，mysql会尝试构建一个表名列表，当出现刚才描述的问题时，它会失败。）

无法打开数据文件

启用（默认）后，如果缺少 file-per-table 表空间文件 ( file)，则innodb_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?

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

孤儿中间表

如果 MySQL 在就地 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%';

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

在数据库目录中，重命名 #sql-*.frm文件以匹配孤立中间表的基本名称：
```
$> mv #sql-36ab_2.frm #sql-ib87-856498050.frm
```
笔记

如果没有.frm文件，您可以重新创建它。该.frm文件必须具有与孤立中间表相同的表模式（它必须具有相同的列和索引）并且必须放置在孤立中间表的数据库目录中。
通过发出 DROP TABLE语句删除孤立的中间表，在表名前添加前缀并将 #mysql50#表名括在反引号中。例如：
```
mysql> DROP TABLE `#mysql50##sql-ib87-856498050`;
```
该#mysql50#前缀告诉 MySQL 忽略file name safe encodingMySQL 5.1 中引入的。表名需要用反引号括起来，这样才能对表名中带有“ # ”等特殊字符的表名执行SQL语句。

笔记

如果在将表移动到不同表空间的就地操作期间发生意外退出 ALTER TABLE，则恢复过程会将表恢复到其原始位置，但会在目标表空间中留下一个孤立的中间表。

笔记

如果 MySQL 在对分区表进行就地 ALTER TABLE操作的过程中退出，您可能会留下多个孤立的中间表，每个分区一个。在这种情况下，请使用以下过程删除孤立的中间表：

在同一 MySQL 版本的单独实例中，创建一个与分区表具有相同模式名称和列的非分区表。
.frm将非分区表的文件复制到包含孤立中间表的数据库目录中。
.frm为每个表制作一份文件副本，并重命名.frm文件以匹配孤立中间表的名称（如上所述）。
对每个表执行一个DROP TABLE 操作（如上所述）。

孤儿临时表

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

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

笔记

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

要识别系统上的孤立临时表，您可以查询 INFORMATION_SCHEMA.INNODB_SYS_TABLES. 查找以开头的表名#sql。如果原始表位于 file-per-table 表空间中，则孤立临时表的表空间文件（ #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 忽略 file name safe encodingMySQL 5.1 中引入的。表名需要用反引号括起来，这样才能对表名中带有“ # ”等特殊字符的表名执行SQL语句。

笔记

如果 MySQL 在对分区表进行表复制 ALTER TABLE操作的过程中退出，您可能会留下多个孤立的临时表，每个分区一个。在这种情况下，请使用以下过程删除孤立的临时表：

在同一 MySQL 版本的单独实例中，创建一个与分区表具有相同模式名称和列的非分区表。
.frm将非分区表的文件复制到有孤立临时表的数据库目录中。
.frm为每个表制作一份文件副本，并重命名.frm文件以匹配孤立临时表的名称（如上所述）。
对每个表执行一个DROP TABLE 操作（如上所述）。

表空间不存在

启用后，如果或文件（或两者）丢失，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文件丢失。

恢复孤立文件每表 ibd 文件

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

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

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

第 14.6.1.3 节“导入 InnoDB 表”中概述的表导入限制适用于此过程。

在新的 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;

丢弃新创建表的表空间。

mysql> ALTER TABLE sakila.actor DISCARD TABLESPACE;

将孤立.ibd文件从备份目录复制到新的数据库目录。
```
$> cp /backup_directory/actor.ibd path/to/mysql-5.7/data/sakila/
```
确保该.ibd文件具有必要的文件权限。

导入孤立.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

查询表以验证.ibd 文件是否已成功恢复。

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