Wednesday, September 7, 2011

REPAIR TABLE statement to Repair corrupt MySQL Table

MySQL database offers seven syntaxes for proper maintaining of mysql table's records. Those syntaxes are as follows:
  1. Repair table syntax 
  2. Analyze table syntax
  3. Backup table syntax
  4. Check  table syntax
  5. Checksum  table syntax
  6. Optimize  table syntax
  7. Restore  table syntax
In this article, we will discuss briefly about first syntax that is Repair Table syntax.

REPAIR Table Syntax: It repairs corrupted mysql database tables. A table gets corrupt due to several reasons like virus attack, system failure, power failure, hardware problem, power failure and so on. Corrupt mysql database tables can be repair by this syntax.

Systax for Repair Table:
“REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]”

Above syntax has three options like quick, extended, and use_frm.
When you select “quick” option then repair table syntax will try to repair index file only. This kind of repair is done by myisamchk.

When you select “extended” option then mysql database creates row by row index in place of one row at a time.  This kind of repair is done by myisamchk.

Use_frm option is available only when header file is corrupted or index file missing.  This kind of repair is not done by myisamchk.

Caution: It is the best method to take a backup of corrupt mysql database table before trying “Repair Table” syntax on it.

Sometimes above repair syntax also fails to fix corruption in mysql database at this situation most of the database administrators suggest to try any third party mysql database repair software. It totally depends upon you to choose the best mysql repair tool that meets your requirement and budget.

1 comment:

  1. Just to clarify, that the CHECK/REPAIR commands really only make sense on MyISAM/Archive tables, which could and do get corrupted; but are meaningless on InnoDB, which is transactional and hence has a recovery mechanism.

    ReplyDelete