(mysql.info.gz) Repair

 
 5.7.3.9 How to Repair Tables
 ............................
 
 The discussion in this section describes how to use `myisamchk' on
 `MyISAM' tables (extensions `.MYI' and `.MYD').  If you are using
 `ISAM' tables (extensions `.ISM' and `.ISD'), you should use `isamchk'
 instead; the concepts are similar.
 
 If you are using MySQL 3.23.16 and above, you can (and should) use the
 `CHECK TABLE' and `REPAIR TABLE' statements to check and repair
 `MyISAM' tables.  See  `CHECK TABLE' CHECK TABLE.  and 
 `REPAIR TABLE' REPAIR TABLE.
 
 The symptoms of a corrupted table include queries that abort
 unexpectedly and observable errors such as these:
 
    * `TBL_NAME.frm' is locked against change
 
    * Can't find file `TBL_NAME.MYI' (Errcode: ###)
 
    * Unexpected end of file
 
    * Record file is crashed
 
    * Got error ### from table handler
 
 To get more information about the error you can run `perror' ###, where
 ### is the error number. The following example shows how to use
 `perror' to find the meanings for the most common error numbers that
 indicate a problem with a table:
 
      shell> perror 126 127 132 134 135 136 141 144 145
      126 = Index file is crashed / Wrong file format
      127 = Record-file is crashed
      132 = Old database file
      134 = Record was already deleted (or record file crashed)
      135 = No more room in record file
      136 = No more room in index file
      141 = Duplicate unique key or constraint on write or update
      144 = Table is crashed and last repair failed
      145 = Table was marked as crashed and should be repaired
 
 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 have to use `ALTER TABLE' to increase the
 `MAX_ROWS' and `AVG_ROW_LENGTH' table option values:
 
      ALTER TABLE TBL_NAME MAX_ROWS=XXX AVG_ROW_LENGTH=YYY;
 
 If you don't know the current table option values, use `SHOW CREATE
 TABLE tbl_name'.
 
 For the other errors, you must repair your tables. `myisamchk' can
 usually detect and fix most problems that occur.
 
 The repair process involves up to four 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 `mysqld' 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.
 
 The options that you can use for table maintenance with `myisamchk' and
 `isamchk' are described in several of the earlier subsections of 
 Table maintenance.
 
 The following section is for the cases where the above command fails or
 if you want to use the extended features that `myisamchk' and `isamchk'
 provide.
 
 If you are going to repair a table from the command line, you must first
 stop the `mysqld' server. Note that when you do `mysqladmin shutdown'
 on a remote server, the `mysqld' server will still be alive for a while
 after `mysqladmin' returns, until all queries are stopped and all keys
 have been flushed to disk.
 
 *Stage 1: Checking your tables*
 
 Run `myisamchk *.MYI' or `myisamchk -e *.MYI' if you have more time.
 Use the `-s' (silent) option to suppress unnecessary information.
 
 If the `mysqld' server is down, you should use the `--update-state'
 option to tell `myisamchk' to mark the table as 'checked'.
 
 You have to repair only those tables for which `myisamchk' announces an
 error.  For such tables, proceed to Stage 2.
 
 If you get weird errors when checking (such as `out of memory' errors),
 or if `myisamchk' crashes, go to Stage 3.
 
 *Stage 2: Easy safe repair*
 
 Note: If you want a repair operation to go much faster, you should set
 the values of the `sort_buffer_size' and `key_buffer_size' variables
 each to about 25% of your available memory when running `myisamchk' or
 `isamchk'.
 
 First, try `myisamchk -r -q TBL_NAME' (`-r -q' means "quick recovery
 mode"). This will attempt 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:
 
   1. Make a backup of the data file before continuing.
 
   2. Use `myisamchk -r TBL_NAME' (`-r' means "recovery mode"). This will
      remove incorrect records and deleted records from the data file and
      reconstruct the index file.
 
   3. If the preceding step fails, use `myisamchk --safe-recover
      TBL_NAME'.  Safe recovery mode uses an old recovery method that
      handles a few cases that regular recovery mode doesn't (but is
      slower).
 
 If you get weird errors when repairing (such as `out of memory'
 errors), or if `myisamchk' crashes, go to Stage 3.
 
 *Stage 3: Difficult repair*
 
 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's necessary to create a new index
 file. Do so as follows:
 
   1. Move the data file to some safe place.
 
   2. Use the table description file to create new (empty) data and
      index files:
 
           shell> mysql DB_NAME
           mysql> SET AUTOCOMMIT=1;
           mysql> TRUNCATE TABLE TBL_NAME;
           mysql> quit
 
      If your version of MySQL doesn't have `TRUNCATE TABLE', use
      `DELETE FROM TBL_NAME' instead.
 
   3. Copy the old data file back onto the newly created data file.
      (Don't just move the old file back onto the new file; you want to
      retain a copy in case something goes wrong.)
 
 Go back to Stage 2.  `myisamchk -r -q' should work.  (This shouldn't be
 an endless loop.)
 
 As of MySQL 4.0.2, you can also use `REPAIR TABLE TBL_NAME USE_FRM',
 which performs the whole procedure automatically.
 
 *Stage 4: Very difficult repair*
 
 You should reach this stage only if the `.frm' description file has also
 crashed. That should never happen, because the description file isn't
 changed after the table is created:
 
   1. Restore the description file from a backup and go back to Stage 3.
      You can also restore the index file and go back to Stage 2.  In
      the latter case, you should start with `myisamchk -r'.
 
   2. If you don't have a backup but know exactly how the table was
      created, create a copy of the table in another database.  Remove
      the new data file, then move the `.frm' description and `.MYI'
      index files from the other database to your crashed database.
      This gives you new description and index files, but leaves the
      `.MYD' data file alone.  Go back to Stage 2 and attempt to
      reconstruct the index file.
Info Catalog
(mysql.info.gz) Check
(mysql.info.gz) Table maintenance
(mysql.info.gz) Optimization
automatically generated byinfo2html