Repair Myisam Tables By Myisamchk Command
There are two types of storage engine used by the MySQL database
, one is MyISAM and another is InnoDB. By default MySQL uses MyISAM as a storage engine. Records store in the form of table in the MyISAM. The MyISAM table gets damaged or corrupted very easily by the several reasons, like virus infection, power failure, software malfunction, hardware failure or human errors. In this article, i will explain how to use myisamchk command to identify and solve the table corruption in MyISAM storage engine.
When you create a table in the MySQL database, it creates three other different files, which are given below:
.frm File: To save the table format.
.MYD File: To save the data of MySQL. It is also known as MyData file.
.MYI File: To save the index of MySQL database. It is also known as MyIndex file.
Consider a practicle scenario wherein you found attached data files gets corrupted and need to repair them.
undef error - DBD::mysql::db selectrow_array failed: Table 'attach_data' is
marked as crashed and should be repaired [for Statement "SELECT LENGTH(thedata)
FROM attach_data WHERE id = ?"] at Bugzilla/Attachment.pm line 344
Bugzilla::Attachment::datasize('Bugzilla::Attachment=HASH(0x9df119c)') called
The above error message shows that the attached data gets corrupted and need to
repair mysql database and fix the problem.
Solution: For resolving above error message, you have to follow some steps which is given below:
1. Identify all corrupted MyISAM tables by myisamchk command:
# myisamchk /var/lib/mysql/bugs/*.MYI >> /tmp/myisamchk_log.txt
myisamchk: error: Wrong bytesec: 0-0-0 at linkstart: 18361936
MyISAM-table 'attach_data.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table 'groups.MYI' is usable but should be fixed
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table 'profiles.MYI' is usable but should be fixed
By executing above command, you can find all corrupted tables in the MyISAM storage engine.
2. Repair damaged MyISAM table by myisamchk
# myisamchk -r profiles.MYI
- recovering (with sort) MyISAM-table 'profiles.MYI'
Data records: 80
- Fixing index 1
- Fixing index 2
3. Perform repair & check together for entire MySQL:
# myisamchk --silent --force --fast --update-state /var/lib/mysql/bugs/*.MYI
myisamchk: MyISAM file /var/lib/mysql/bugs/groups.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: MyISAM file /var/lib/mysql/bugs/profiles.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
by: mark willium
How to Remove HDD Repair Virus-Get Rid of HDDRepair Virus? Comparing The Windows Phone 7 And The Iphone Pcsupportcaretoday Provides A Healing Remote Pc Repair Service Fix Pc Issues With Computer Repair San Diego, Ca Fitness Equipment Repair: Yowza Keewadin Treadmill Evaluation Furnace Repairs in Lafayette, IN Roof Contractor For New Roof And Repair Needs Good Research How To Fix Unknown USB Device Problems In Windows Top 5 Beautiful Games For Windows Phone 7 Top 5 Apps For The Windows Phone 7 Combat The Monstrous Water Damage Hire Damage Repair Experts Cell Phone Repair - We Repair All Brands And Models Importance Of Looking For The Right Roof Repair Round Rock