14.22.3 Troubleshooting InnoDB Data Dictionary Operations — MySQL 5.7

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. Чтобы обойти эту проблему, запустите клиента mysql с параметром --skip-auto-rehash и попробуйте выполнить DROP TABLE снова. (При включенном завершении имени, mysql пытается составить список имён таблиц, что приводит к неудаче при возникновении подобной проблемы.)

Ошибка открытия файла данных

При включенном 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).

Чтобы определить сиротские промежуточные таблицы на вашей системе, вы можете запросить таблицу Информационной схемы INNODB_SYS_TABLES. Найдите имена таблиц, начинающиеся с #sql. Если исходная таблица находится в табличном пространстве, файл табличного пространства (файл #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 encoding, введённые в MySQL 5.1. Использование обратных кавычек вокруг имени таблицы необходимо для выполнения SQL-операций с именами таблиц, содержащими специальные символы, такие как “#”.

Примечание

Если во время операции перемещения таблицы в другое табличное пространство происходит неожиданное завершение операции ALTER TABLE, процесс восстановления возвращает таблицу в исходное местоположение, но оставляет сиротскую промежуточную таблицу в целевом табличном пространстве.

Примечание

Если MySQL завершает работу в середине операции ALTER TABLE над разделяемой таблицей, у вас может остаться несколько сиротских промежуточных таблиц, по одной на каждый раздел. В этом случае используйте следующую процедуру для удаления сиротских промежуточных таблиц:

В отдельной инстанции MySQL той же версии создайте неразделенную таблицу с тем же именем схемы и столбцами, что и разделяемая таблица.
Скопируйте файл .frm неразделенной таблицы в каталог базы данных с сиротскими промежуточными таблицами.
Сделайте копию файла .frm для каждой таблицы и переименуйте файлы .frm, чтобы они соответствовали именам сиротских промежуточных таблиц (как описано выше).
Выполните операцию DROP TABLE (как описано выше) для каждой таблицы.

Сиротские временные таблицы

Если MySQL завершится в середине операции копирования таблицы (ALTER TABLE (ALGORITHM=COPY)), вы можете столкнуться с сиротской временной таблицей, занимающей место на вашей системе. Кроме того, сиротская временная таблица в пустом пространстве препятствует удалению общих табличных пространств. В этом разделе описано, как определить и удалить сиротские временные таблицы.

Имена сиротских временных таблиц начинаются с префикса #sql- (например, #sql-540_3). Сопутствующий файл .frm имеет такое же базовое имя, как и сиротская временная таблица.

Примечание

Если файла .frm нет, вы можете его воссоздать. Файл .frm должен иметь ту же схему таблицы, что и сиротская временная таблица (он должен иметь те же столбцы и индексы) и должен быть помещён в каталог базы данных сиротской временной таблицы.

Для определения сиротских временных таблиц на вашей системе можно запросить таблицу схемы информации 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 игнорировать file name safe encoding, введённые в MySQL 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 с таблицей на файл

В этом разделе описывается, как восстановить сиротские файлы .ibd в другой экземпляр MySQL. Возможно, вы используете эту процедуру, если системное пространство таблиц утеряно или не подлежит восстановлению, и вы хотите восстановить резервные копии файлов .ibd в новом экземпляре MySQL.

Процедура не поддерживается для файлов .ibd.

Процедура предполагает, что у вас есть только резервные копии файлов .ibd, вы восстанавливаетесь до той же версии MySQL, которая изначально создала сиротские файлы .ibd, и резервные копии файлов .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 |
+----------+
```