Friday, September 9, 2011

Incorrect Information in .FRM file of MyISAM storage engine

MySQL database uses tables to store data and all these tables are defined by .FRM file. FRM is an internal file in MySQL database that contains the formating information or structure data for the database. It defines fields and structure of each MySQL's table. Regardless of the storage engine that you choose, every MySQL table is represented by .frm file. File bears the same name as table name with extension .FRM.

Example: suppose you have create a employee table in mysql database then frm file name will be employee.frm that will define the employee table.

Corruption in MySQL database table will resist you to access the table that can be happen due to several reasons like kernel bugs, unexpected power failure, hardware failure, virus attack, and many more. A corrupted table will placed the incorrect information in the frm file and you may got below error message:

Incorrect information in file: './able-name.frm'”

Resolution: you have three solutions to repair MySQL table after getting above error message:
  1. Repair corrupt table by REPAIR TABLE statement
  2. Restore from Backup
  3. Stop and copy method
Stop and copy method details
Note: if you are using MyISAM storage engine then follow the below steps to repair corrupt table:
  • Stop MySQL database
  • Take backup of your .frm, .myd, myi files.
  • Delete these files from mysql data directory.
  • Now start MySQL Database
  • Rereate table again by CREATE Table statement.
  • Stop MySQL database.
  • Copy .myd & .myi files back into the data directory, replace the ones there.
  • Start MySQL database again.
If all three methods fail to fix the corruption from table then at last you should try Stellar Phoenix MySQL repair software. MySQL repair software repairs corrupted table created using MyISAM as well as Innodb. This software has around 99 percent success rate. Its very safe and offers easy-to-use interface.

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.