Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 45.3Mb
PDF (A4) - 45.3Mb
PDF (RPM) - 40.9Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.5Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 208.5Kb
Man Pages (Zip) - 310.6Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Troubleshooting InnoDB Data Dictionary Operations

15.20.3 Troubleshooting InnoDB Data Dictionary Operations

Information about table definitions is stored in the InnoDB data dictionary. If you move data files around, dictionary data can become inconsistent.

If a data dictionary corruption or consistency issue prevents you from starting InnoDB, see Section 15.20.2, “Forcing InnoDB Recovery” for information about manual recovery.

Cannot Open Datafile

With innodb_file_per_table enabled (the default), the following messages may appear at startup if a file-per-table tablespace file (.ibd file) is missing:

[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.

To address the these messages, issue DROP TABLE statement to remove data about the missing table from the data dictionary.

Restoring Orphan File-Per-Table ibd Files

This procedure describes how to restore orphan file-per-table .ibd files to another MySQL instance. You might use this procedure if the system tablespace is lost or unrecoverable and you want to restore .ibd file backups on a new MySQL instance.

The procedure is not supported for general tablespace .ibd files.

The procedure assumes that you only have .ibd file backups, you are recovering to the same version of MySQL that initially created the orphan .ibd files, and that .ibd file backups are clean. See Section, “Moving or Copying InnoDB Tables” for information about creating clean backups.

Tablespace copying limitations outlined in Section, “Copying Tablespaces to Another Instance” are applicable to this procedure.

  1. On the new MySQL instance, recreate the table in a database of the same name.

    mysql> CREATE DATABASE sakila;
    mysql> USE sakila;
    mysql> CREATE TABLE actor (
             first_name VARCHAR(45) NOT NULL,
             last_name VARCHAR(45) NOT NULL,
             PRIMARY KEY  (actor_id),
             KEY idx_actor_last_name (last_name)
           )ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2. Discard the tablespace of the newly created table.

  3. Copy the orphan .ibd file from your backup directory to the new database directory.

    shell> cp /backup_directory/actor.ibd path/to/mysql-5.7/data/sakila/
  4. Ensure that the .ibd file has the necessary file permissions.

  5. Import the orphan .ibd file. A warning is issued indicating that InnoDB will attempt to import the file without schema verification.

    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. Query the table to verify that the .ibd file was successfully restored.

    mysql> SELECT COUNT(*) FROM;
    | count(*) |
    |      200 |

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Chris Calender on November 18, 2011
A missing .ibd file can also occur (albeit rare) if you issue a truncate table on an InnoDB table with no foreign keys (FKs) using the InnoDB Plugin and with innodb_file_per_table enabled, and mysqld crashes after the truncate has dropped after the ibd file has been dropped but not yet created.