How To Repair Corrupted MySQL Tables Using myisamchk

Published by admin on October 1, 2015

MySQL database are generally stored in MyISAM by default. It is known that MyISAM table generally gets corrupt quite often. In such cases How can then the be protected from being corrupt? This is possible with the help of myisamchk, which would help you to identify as well as fix the corrupted table in MyISAM. While creating a table in MySQL, three different files are created they are as follows:

1. *.MYD(MyData) file to store the data,
2. *.MYI(MyIndex) to store the index, and
3. *.frmfile to store table format

In order to store bigger database it is suggested to use InnoDB as the storage engine, because it provides commit, rollback options most importantly it resembles Oracle.

While using MySQL database for the production bugzilla application you might get error messages. This error message indicates that the data table that is attached has been corrupt and it has to be repaired. Using myisamchk it is possible to repair the corrupted table in the following way.

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

Using myisamchk identify all the corrupted tables

# 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

The output of myisamchk would generally redirect you to a temporary file, where you would be able to see the names of the corrupted table on the screen. All the information about the tables can be obtained from /tmp/myisamchk_log.txt file including the good ones, that has been mentioned below:

Checking MyISAM file: user_group_map.MYI

Data records:     182   Deleted blocks:       0

– check file-size

– check record delete-chain

– check key delete-chain

– check index reference

– check data record references index: 1

Repair the corrupted table using myisamchk

In the above step to repair the corrupted tables identified -r option was used and now to execute the myisamchk has been mentioned below:

# myisamchk -r profiles.MYI

– recovering (with sort) MyISAM-table ‘profiles.MYI’

Data records: 80

– Fixing index 1

– Fixing index 2

If your application and other tables are still using this table then in that case you would get the following error message: clients are using or haven’t closed the table properly. If you can afford to shutdown the DB for a while then shutdown mysqld before performing the repair in this way this error message can be avoided. Or else to delete any modification in the table that are still in memory, in that case use FLUSH TABLES in mysqld.

For the entire MySQL database perform check and repair together

# 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

• -U –update-state option: When any error is found the table would be marked as crashed.
• -F, –fast option: Checks only those tables that has not been closed properly.
• -f, –force option: If there are any errors in the table then automatically myisamchk restarts with repair option -r.
• -s, –silent option: Prints only errors. You can use two -s to make myisamchk very silent.

For large MySQL database additional memory should be allocated

In order to recover the tables with large database it might take very long time. This generally depends on the RAM that is available on the PC, while executing the myisamchk would increase the memory parameters as shown below:

# myisamchk –silent –force –fast –update-state \

–key_buffer_size=512M –sort_buffer_size=512M \

–read_buffer_size=4M –write_buffer_size=4M \

/var/lib/mysql/bugs/*.MYI

The information about a table can be obtained by using myisamchk

In order to get detailed information of the table you can use myisamchk, which has been mentioned below.

All myisamchk options

In order to understand all the available option for myisamchk the following command should be executed.

# myisamchk –help

Some of the key options that can be used with myisamchk are as follows:

Repair options (on using ‘-r’ or ‘-o’):

• -B, –backupbackup of the .MYD file can be made as ‘filename-time.BAK’.
• –correct-checksumCorrect checksum for table information.
• -e, –extend-checkfrom the data file every possible row can be recovered. Using thus option it would also find those rows that are not required; it is generally advised not to use this option until you are not totally desperate.
• -f, –forceold temporary files would be overwritten.
• -r, –recoverCan fix almost anything except unique keys that aren’t unique.
• -n, –sort-recoverif the temporary file are very big then force recovering by sorting.
• -p, –parallel-recoversame technique of ‘-r’ and ‘-n’ has to be used for creating  all the keys in parallel and that too in different threads.
• -o, –safe-recoverold recovery method is used, it is generally slower than ‘-r’ but can also  handle those cases where the data file cannot be fixed by using ‘-r’ reports.
• -q, –quickrepair is faster but the data file would not be modified. To force myisamchk to modify the original data file in case of duplicate keys one can give a second ‘-q’. NOTE: With this option the corrupted data file tables cannot be fixed.
• -u, –unpackthe file packed with myisampack can be unpacked.

Check options (check is the default action for myisamchk):

• -c, –checkCheck the error in the table.
• -C, –check-only-changedCheck only those tables which has been changed since the last check.
• -e, –extend-checkthoroughly checks the table. It should be used in extreme cases as myisamchk would normally find out if the table is ok even without this switch.
• -F, –fastCheck those tables which have not closed properly.
• -f, –forceif there are any errors in the table then restart with ‘-r’. States would be updated along with ‘–update-state’.
• -i, –informationThe statistics information about table can be printed which are checked.
• -m, –medium-checkit is good enough for most cases as it can only find 99.99% of all errors and moreover it is faster than extend-check.
• -U –update-stateif you find any errors mark the tables as crashed.
• -T, –read-only Table should not be marked as checked.

Global options:

• -s, –silentOne can use two -s to make myisamchk very silent and it can print only errors.
• -v, –verbosemore information can be printed. This can be used with — check and — description. For more verbosity use many -v.
• -V, –versionPrint version then exits.
• -w, –waitWait if table is locked.

Other actions:

• -a, –analyzeDistribution of keys can be analyze. Will make some joins in MySQL faster. The calculated distribution can be checked by using ‘–description –verbose table_name’.
• -d, –descriptionsome information about the table gets printed.

If still you are facing this error then in that case it can be fixed by using MySQL Repair Tool. It is professional software as it is capable of recovering the information from the corrupted database. This software can also save the retrieved information in Transact-SQL script which is fit for quick database recreation. And also it recovers table structure and data. It supports MySQL versions like 5.x, 4.x, 3.x, 2.x and 1.x and available for all modern platforms that includes windows 95/98/2000/XP/2003 server. It has the ability to repair password protected SQL files smoothly. And most importantly it is very user friendly.

Steps to repair corrupted MySQL table

Step 1: Start with Stellar Phoenix Database Repair for MySQL . Software and you can see the main interface as shown below.

Step 2: Click on ‘Select’ button & then select ‘Repair corrupt database which is stored at default location of MySQL’ option to select your database from default location and then click.

Step 3: The left pane shows the preview of database and its component in a tree like structure after completing scanning process.

Step 4: After previewing your database, to start repair process click ‘Repair’ button in file menu on the main user interface. Stellar Phoenix Database Repair for MySQL dialog box appears. Click Yes.

Step 5: After successful completion of the process, a message box is displayed as ‘repair process finished successfully’. Click ‘OK’ to close the message box.

Step 6: If you want to select the database manually then select ‘Manual selection of database to repair’ option & then click browse. Browse the folder dialog box opens. Select the folder that contains database and click ‘OK’.

408 Total Views 3 Views Today