<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="myisam-repair">
      </a>
      9.6.3 How to Repair MyISAM Tables
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045230580192">
  </a>
  <a class="indexterm" name="idm46045230578736">
  </a>
  <p>
   The discussion in this section describes how to use
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   on
   <code class="literal">
    MyISAM
   </code>
   tables
        (extensions
   <code class="filename">
    .MYI
   </code>
   and
   <code class="filename">
    .MYD
   </code>
   ).
  </p>
  <p>
   You can also use the
   <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
    <code class="literal">
     CHECK TABLE
    </code>
   </a>
   and
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR TABLE
    </code>
   </a>
   statements to
        check and repair
   <code class="literal">
    MyISAM
   </code>
   tables. See
   <a class="xref" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
    Section 15.7.3.2, “CHECK TABLE Statement”
   </a>
   , and
   <a class="xref" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    Section 15.7.3.5, “REPAIR TABLE Statement”
   </a>
   .
  </p>
  <p>
   Symptoms of corrupted tables include queries that abort
        unexpectedly and observable errors such as these:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Can't find file
      <code class="filename">
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       .MYI
      </code>
      (Errcode:
      <em class="replaceable">
       <code>
        nnn
       </code>
      </em>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Unexpected end of file
     </p>
    </li>
    <li class="listitem">
     <p>
      Record file is crashed
     </p>
    </li>
    <li class="listitem">
     <p>
      Got error
      <em class="replaceable">
       <code>
        nnn
       </code>
      </em>
      from table handler
     </p>
    </li>
   </ul>
  </div>
  <p>
   To get more information about the error, run
   <a class="link" href="perror.html" title="6.8.1 perror — Display MySQL Error Message Information">
    <span class="command">
     <strong>
      perror
     </strong>
    </span>
   </a>
   <em class="replaceable">
    <code>
     nnn
    </code>
   </em>
   , where
   <em class="replaceable">
    <code>
     nnn
    </code>
   </em>
   is the error number. The
        following example shows how to use
   <a class="link" href="perror.html" title="6.8.1 perror — Display MySQL Error Message Information">
    <span class="command">
     <strong>
      perror
     </strong>
    </span>
   </a>
   to
        find the meanings for the most common error numbers that
        indicate a problem with a table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa16515084"><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">perror</span> 126 127 132 134 135 136 141 144 145
MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted <span class="token punctuation">(</span>or record file crashed<span class="token punctuation">)</span>
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or update
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired</code></pre>
  </div>
  <p>
   Note that error 135 (no more room in record file) and error 136
        (no more room in index file) are not errors that can be fixed by
        a simple repair. In this case, you must use
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   to increase the
   <code class="literal">
    MAX_ROWS
   </code>
   and
   <code class="literal">
    AVG_ROW_LENGTH
   </code>
   table option values:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa7076154"><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 keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">MAX_ROWS</span><span class="token operator">=</span><em class="replaceable">xxx</em> <span class="token keyword">AVG_ROW_LENGTH</span><span class="token operator">=</span><em class="replaceable">yyy</em><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If you do not know the current table option values, use
   <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
    <code class="literal">
     SHOW CREATE TABLE
    </code>
   </a>
   .
  </p>
  <p>
   For the other errors, you must repair your tables.
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   can usually detect and fix most
        problems that occur.
  </p>
  <p>
   The repair process involves up to three stages, described here.
        Before you begin, you should change location to the database
        directory and check the permissions of the table files. On Unix,
        make sure that they are readable by the user that
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   runs as (and to you, because you need
        to access the files you are checking). If it turns out you need
        to modify files, they must also be writable by you.
  </p>
  <p>
   This section is for the cases where a table check fails (such as
        those described in
   <a class="xref" href="myisam-check.html" title="9.6.2 How to Check MyISAM Tables for Errors">
    Section 9.6.2, “How to Check MyISAM Tables for Errors”
   </a>
   ), or you want
        to use the extended features that
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   provides.
  </p>
  <p>
   The
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   options used for table
        maintenance with are described in
   <a class="xref" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    Section 6.6.4, “myisamchk — MyISAM Table-Maintenance Utility”
   </a>
   .
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   also has variables that you can set
        to control memory allocation that may improve performance. See
   <a class="xref" href="myisamchk-memory.html" title="6.6.4.6 myisamchk Memory Usage">
    Section 6.6.4.6, “myisamchk Memory Usage”
   </a>
   .
  </p>
  <p>
   If you are going to repair a table from the command line, you
        must first stop the
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   server. Note that
        when you do
   <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
    <span class="command">
     <strong>
      mysqladmin shutdown
     </strong>
    </span>
   </a>
   on a remote
        server, the
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   server is still available
        for a while after
   <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
    <span class="command">
     <strong>
      mysqladmin
     </strong>
    </span>
   </a>
   returns, until
        all statement-processing has stopped and all index changes have
        been flushed to disk.
  </p>
  <p>
   <span class="bold">
    <strong>
     Stage 1: Checking your tables
    </strong>
   </span>
  </p>
  <p>
   Run
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk *.MYI
     </strong>
    </span>
   </a>
   or
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk -e
        *.MYI
     </strong>
    </span>
   </a>
   if you have more time. Use the
   <code class="option">
    -s
   </code>
   (silent) option to suppress unnecessary
        information.
  </p>
  <p>
   If the
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   server is stopped, you should
        use the
   <a class="link" href="myisamchk-check-options.html#option_myisamchk_update-state">
    <code class="option">
     --update-state
    </code>
   </a>
   option
        to tell
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   to mark the table as
   <span class="quote">
    “
    <span class="quote">
     checked.
    </span>
    ”
   </span>
  </p>
  <p>
   You have to repair only those tables for which
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   announces an error. For such
        tables, proceed to Stage 2.
  </p>
  <p>
   If you get unexpected errors when checking (such as
   <code class="literal">
    out
        of memory
   </code>
   errors), or if
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   crashes, go to Stage 3.
  </p>
  <p>
   <span class="bold">
    <strong>
     Stage 2: Easy safe repair
    </strong>
   </span>
  </p>
  <p>
   First, try
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk -r -q
      <em class="replaceable">
       <code>
        tbl_name
       </code>
      </em>
     </strong>
    </span>
   </a>
   (
   <code class="option">
    -r
        -q
   </code>
   means
   <span class="quote">
    “
    <span class="quote">
     quick recovery mode
    </span>
    ”
   </span>
   ). This
        attempts to repair the index file without touching the data
        file. If the data file contains everything that it should and
        the delete links point at the correct locations within the data
        file, this should work, and the table is fixed. Start repairing
        the next table. Otherwise, use the following procedure:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Make a backup of the data file before continuing.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk -r
         <em class="replaceable">
          <code>
           tbl_name
          </code>
         </em>
        </strong>
       </span>
      </a>
      (
      <code class="option">
       -r
      </code>
      means
      <span class="quote">
       “
       <span class="quote">
        recovery mode
       </span>
       ”
      </span>
      ).
            This removes incorrect rows and deleted rows from the data
            file and reconstructs the index file.
     </p>
    </li>
    <li class="listitem">
     <p>
      If the preceding step fails, use
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk
            --safe-recover
         <em class="replaceable">
          <code>
           tbl_name
          </code>
         </em>
        </strong>
       </span>
      </a>
      . Safe recovery
            mode uses an old recovery method that handles a few cases
            that regular recovery mode does not (but is slower).
     </p>
    </li>
   </ol>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    If you want a repair operation to go much faster, you should
          set the values of the
    <a class="link" href="server-system-variables.html#sysvar_sort_buffer_size">
     <code class="literal">
      sort_buffer_size
     </code>
    </a>
    and
    <a class="link" href="server-system-variables.html#sysvar_key_buffer_size">
     <code class="literal">
      key_buffer_size
     </code>
    </a>
    variables
          each to about 25% of your available memory when running
    <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
     <span class="command">
      <strong>
       myisamchk
      </strong>
     </span>
    </a>
    .
   </p>
  </div>
  <p>
   If you get unexpected errors when repairing (such as
   <code class="literal">
    out of memory
   </code>
   errors), or if
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   crashes, go to Stage 3.
  </p>
  <p>
   <span class="bold">
    <strong>
     Stage 3: Difficult repair
    </strong>
   </span>
  </p>
  <p>
   You should reach this stage only if the first 16KB block in the
        index file is destroyed or contains incorrect information, or if
        the index file is missing. In this case, it is necessary to
        create a new index file. Do so as follows:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Move the data file to a safe place.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use the table description file to create new (empty) data
            and index files:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa72030050"><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">mysql</span> <em class="replaceable">db_name</em></code></pre>
     </div>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa32744611"><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">SET</span> autocommit<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">TRUNCATE</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> quit</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Copy the old data file back onto the newly created data
            file. (Do not just move the old file back onto the new file.
            You want to retain a copy in case something goes wrong.)
     </p>
    </li>
   </ol>
  </div>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    If you are using replication, you should stop it prior to
          performing the above procedure, since it involves file system
          operations, and these are not logged by MySQL.
   </p>
  </div>
  <p>
   Go back to Stage 2.
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk -r -q
     </strong>
    </span>
   </a>
   should
        work. (This should not be an endless loop.)
  </p>
  <p>
   You can also use the
   <code class="literal">
    REPAIR TABLE
    <em class="replaceable">
     <code>
      tbl_name
     </code>
    </em>
    USE_FRM
   </code>
   SQL
        statement, which performs the whole procedure automatically.
        There is also no possibility of unwanted interaction between a
        utility and the server, because the server does all the work
        when you use
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR TABLE
    </code>
   </a>
   . See
   <a class="xref" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    Section 15.7.3.5, “REPAIR TABLE Statement”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
