MYSQL MYISAM_RECOVER OPTIONS

MYSQL MYISAM_RECOVER OPTIONS

This variable sets the mode for automatic recovery when a MyISAM table crashed. The option value is any combination of the following values:

•   DEFAULT --Recovery without backup, forcing, or quick checking. It is the same as not giving any option.
•  BACKUP --If the data file was changed during recovery, save a backup of the tbl_name.MYD file as tbl_name-datetime.BAK.
•  FORCE --Run recovery even if we would lose more than one row from the .MYD file.
•  QUICK --Don't check the rows in the table if there aren't any delete blocks.

 Multiple values can be set at the same tiem. To use multiple values, separate them by commas.
   
    myisam_recover=backup,force

To disable this option, set it to “”.

    myisam-recover=””
  
If one or more myisam-recover option is used, each time mysqld opens a MyISAM table, it checks if or not the table is marked as crashed or wasn’t closed properly. If it is the case, mysqld runs a check on the table and attempts to repair it if the table was corrupted. Following are the steps which mysqld will perform according to the MySQL Manual:

•  The server checks the table for errors.
•  If the server finds an error, it tries to do a fast table repair (with sorting and without re-creating the data file).
•  If the repair fails because of an error in the data file (for example, a duplicate-key error), the server tries again, this time re-creating the data file.
•  If the repair still fails, the server tries once more with the old repair option method (write row by row without sorting). This method should be able to repair any type of error and has low disk space requirements.

 If the recovery wouldn't be able to recover all rows from previously completed statements and you didn't specify FORCE in the value of the --myisam-recover option, automatic repair aborts with an error message in the error log:

       Error: Couldn't repair table: test.g00pages

If FORCE was specified, a warning like this is written instead:

      Warning: Found 344 of 354 rows when repairing ./test/g00pages

Note that if the automatic recovery value includes BACKUP, the recovery process creates files with names of the form tbl_name-datetime.BAK. You should have a cron script that automatically moves these files from the database directories to backup media.

Myisam-recover is not a dynamic variable; hence, it requires a server restart to reset the variable.

References:

• MySQL Manual: Server Command Options
• Understanding MySQL Internals: Chapter 5: Configuration Variables
• High Performance MySQL: Chapter 6: Optimizing Server Settings
• Other Reference On the Web

 Related Posts:

• MySQL Delay Key Write Option