<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-troubleshooting-datadict">
      </a>
      17.20.4 Troubleshooting InnoDB Data Dictionary Operations
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045148911232">
  </a>
  <p>
   Information about table definitions is stored in the InnoDB
   <a class="link" href="glossary.html#glos_data_dictionary" title="data dictionary">
    data dictionary
   </a>
   . If
      you move data files around, dictionary data can become
      inconsistent.
  </p>
  <p>
   If a data dictionary corruption or consistency issue prevents you
      from starting
   <code class="literal">
    InnoDB
   </code>
   , see
   <a class="xref" href="forcing-innodb-recovery.html" title="17.20.3 Forcing InnoDB Recovery">
    Section 17.20.3, “Forcing InnoDB Recovery”
   </a>
   for information about
      manual recovery.
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-cannot-open-data-file-error">
       </a>
       Cannot Open Datafile
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045148904880">
   </a>
   <p>
    With
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    enabled (the default), the following messages may appear at
        startup if a
    <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
     file-per-table
    </a>
    tablespace file (
    <code class="literal">
     .ibd
    </code>
    file) is missing:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa72388707"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token property"><span class="token punctuation">[</span>ERROR<span class="token punctuation">]</span></span> InnoDB<span class="token punctuation">:</span> Operating system error number 2 in a file operation<span class="token punctuation">.</span>
<span class="token property"><span class="token punctuation">[</span>ERROR<span class="token punctuation">]</span></span> InnoDB<span class="token punctuation">:</span> The error means the system cannot find the path specified<span class="token punctuation">.</span>
<span class="token property"><span class="token punctuation">[</span>ERROR<span class="token punctuation">]</span></span> InnoDB<span class="token punctuation">:</span> Cannot open datafile for read-only<span class="token punctuation">:</span> <span class="token atrule">'./test/t1.ibd'</span> OS error<span class="token punctuation">:</span> 71
<span class="token punctuation">[</span>Warning<span class="token punctuation">]</span> InnoDB<span class="token punctuation">:</span> Ignoring tablespace `test/t1` because it could not be opened<span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    To address these messages, issue
    <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
     <code class="literal">
      DROP
        TABLE
     </code>
    </a>
    statement to remove data about the missing table
        from the data dictionary.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="restoring-orphaned-ibd-files">
       </a>
       Restoring Orphan File-Per-Table ibd Files
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045148895344">
   </a>
   <p>
    This procedure describes how to restore orphan
    <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
     file-per-table
    </a>
    <code class="filename">
     .ibd
    </code>
    files to another MySQL instance. You
        might use this procedure if the system tablespace is lost or
        unrecoverable and you want to restore
    <code class="filename">
     .ibd
    </code>
    file backups on a new MySQL instance.
   </p>
   <p>
    The procedure is not supported for
    <a class="link" href="glossary.html#glos_general_tablespace" title="general tablespace">
     general
        tablespace
    </a>
    <code class="filename">
     .ibd
    </code>
    files.
   </p>
   <p>
    The procedure assumes that you only have
    <code class="filename">
     .ibd
    </code>
    file backups, you are recovering to
        the same version of MySQL that initially created the orphan
    <code class="filename">
     .ibd
    </code>
    files, and that
    <code class="filename">
     .ibd
    </code>
    file backups are clean. See
    <a class="xref" href="innodb-migration.html" title="17.6.1.4 Moving or Copying InnoDB Tables">
     Section 17.6.1.4, “Moving or Copying InnoDB Tables”
    </a>
    for information about
        creating clean backups.
   </p>
   <p>
    Table import limitations outlined in
    <a class="xref" href="innodb-table-import.html" title="17.6.1.3 Importing InnoDB Tables">
     Section 17.6.1.3, “Importing InnoDB Tables”
    </a>
    are applicable to this
        procedure.
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       On the new MySQL instance, recreate the table in a database
            of the same name.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa85663268"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">DATABASE</span> sakila<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">USE</span> sakila<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> actor <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     actor_id <span class="token datatype">SMALLINT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     first_name <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">45</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     last_name <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">45</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     last_update <span class="token datatype">TIMESTAMP</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span>  <span class="token punctuation">(</span>actor_id<span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">KEY</span> idx_actor_last_name <span class="token punctuation">(</span>last_name<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8mb4<span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Discard the tablespace of the newly created table.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa77444156"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> sakila<span class="token punctuation">.</span>actor <span class="token keyword">DISCARD</span> <span class="token keyword">TABLESPACE</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Copy the orphan
       <code class="literal">
        .ibd
       </code>
       file from your
            backup directory to the new database directory.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa54312260"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">cp</span> /backup_directory/actor<span class="token punctuation">.</span>ibd <em class="replaceable">path/to/mysql-5<span class="token punctuation">.</span>7/data</em>/sakila/</code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Ensure that the
       <code class="filename">
        .ibd
       </code>
       file has the
            necessary file permissions.
      </p>
     </li>
     <li class="listitem">
      <p>
       Import the orphan
       <code class="literal">
        .ibd
       </code>
       file. A warning is
            issued indicating that
       <code class="literal">
        InnoDB
       </code>
       is
            attempting to import the file without schema verification.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa53060577"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> sakila<span class="token punctuation">.</span>actor <span class="token keyword">IMPORT</span> <span class="token keyword">TABLESPACE</span><span class="token punctuation">;</span> <span class="token keyword">SHOW</span> <span class="token keyword">WARNINGS</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected, 1 warning (0.15 sec)</span>

Warning <span class="token operator">|</span> <span class="token number">1810</span> <span class="token operator">|</span> InnoDB: <span class="token keyword">IO</span> <span class="token keyword">Read</span> <span class="token keyword">error</span>: <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token keyword">No</span> such <span class="token keyword">file</span> <span class="token operator">or</span> <span class="token keyword">directory</span><span class="token punctuation">)</span>
<span class="token keyword">Error</span> opening <span class="token string">'./sakila/actor.cfg'</span><span class="token punctuation">,</span> will attempt <span class="token keyword">to</span> <span class="token keyword">import</span>
<span class="token keyword">without</span> <span class="token keyword">schema</span> verification</code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Query the table to verify that the
       <code class="literal">
        .ibd
       </code>
       file was successfully restored.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97536051"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> sakila<span class="token punctuation">.</span>actor<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> count(*) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>      200 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
      </div>
     </li>
    </ol>
   </div>
  </div>
 </div>
 <br/>
</div>
