≡ Menu

How To Repair Corrupted MySQL Tables Using myisamchk

MySQL Application LogoMyISAM is the default storage engine for MySQL database. MyISAM table gets corrupted very easily. In this article, I’ll explain how to use myisamchk to identify and fix table corruption in MyISAM. When a table is created under MySQL, it creates three different files: *.frm file to store table format, *.MYD (MyData) file to store the data, and *.MYI (MyIndex) to store the index. I prefer to use InnoDB as the storage engine for bigger database, as it resembles Oracle and provides commit, rollback options.

I got the following error from a production bugzilla application that is using MySQL database. From the error message, it is clear that attach_data table is corrupted and needs to be reparied. The corrupted table can be repaired using myisamchk as explained below.

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

1. Identify all corrupted tables using myisamchk

# 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

When you redirect the output of myisamchk to a temporary file, it will display only the corrupted table names on the screen. The /tmp/myisamchk_log.txt file will contain information about all the tables including the good ones, as shown 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

2. Repair the corrupted table using myisamchk

Execute the myisamchk as shown below, with -r option to repair the corrupted tables identified in the above step.

# myisamchk -r profiles.MYI

- recovering (with sort) MyISAM-table 'profiles.MYI'
Data records: 80
- Fixing index 1
- Fixing index 2

You may get error message: clients are using or haven’t closed the table properly, if the tables are still getting used by your application and other tables. To avoid this error message, shutdown mysqld before performing the repair, if you can afford to shutdown the DB for a while. If not, use FLUSH TABLES to force mysqld to flush any table modification that are still in memory.

3. Perform check and repair together for entire MySQL database

# 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
  • -s, --silent option: Prints only errors. You can use two -s to make myisamchk very silent.
  • -f, --force option: Restart myisamchk automatically with repair option -r, if there are any errors in the table.
  • -F, --fast option: Check only tables that haven’t been closed properly.
  • -U --update-state option: Marks tables as crashed, when it finds any error.

4. Allocate additional memory for large MySQL database

For large database, it may take several hours to recover the tables. Depending on RAM available on your system, increase the memory parameters as shown below while executing the myisamchk:

# myisamchk --silent --force --fast --update-state \
--key_buffer_size=512M --sort_buffer_size=512M \
--read_buffer_size=4M --write_buffer_size=4M \

5. Use myisamchk to get information about a table

You can also use myisamchk to get detailed information about a table, as shown below.

# myisamchk -dvv profiles.MYI

MyISAM file:         profiles.MYI
Record format:       Packed
Character set:       latin1_swedish_ci (8)
File-version:        1
Creation time:       2007-08-16 18:46:59
Status:              open,changed,analyzed,optimized keys,sorted index pages
Auto increment key:              1  Last value:                    88
Data records:                   88  Deleted blocks:                 0
Datafile parts:                118  Deleted data:                   0
Datafile pointer (bytes):        4  Keyfile pointer (bytes):        4
Datafile length:              6292  Keyfile length:              6144
Max datafile length:    4294967294  Max keyfile length: 4398046510079
Recordlength:                 2124

table description:
Key Start Len Index   Type                     Rec/key         Root  Blocksize
1   2     3   unique  int24                          1         1024       1024
2   5     765 unique  char packed stripped           1         2048       4096

Field Start Length Nullpos Nullbit Type
1     1     1
2     2     3                      no zeros
3     5     765                    no endspace

6. All myisamchk options

Execute the following command to understand all the available option for myisamchk.

# myisamchk --help

Following are some of the key options that you can use along with myisamchk.

Global options:

  • -s, --silent Only print errors. One can use two -s to make myisamchk very silent.
  • -v, --verbose Print more information. This can be used with --description and --check. Use many -v for more verbosity.
  • -V, --version Print version and exit.
  • -w, --wait Wait if table is locked.

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

  • -c, --check Check table for errors.
  • -e, --extend-check Check the table VERY throughly. Only use this in extreme cases as myisamchk should normally be able to find out if the table is ok even without this switch.
  • -F, --fast Check only tables that haven’t been closed properly.
  • -C, --check-only-changed Check only tables that have changed since last check.
  • -f, --force Restart with ‘-r’ if there are any errors in the table. States will be updated as with ‘--update-state’.
  • -i, --information Print statistics information about table that is checked.
  • -m, --medium-check Faster than extend-check, but only finds 99.99% of all errors. Should be good enough for most cases.
  • -U --update-state Mark tables as crashed if you find any errors.
  • -T, --read-only Don’t mark table as checked.

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

  • -B, --backup Make a backup of the .MYD file as ‘filename-time.BAK’.
  • --correct-checksum Correct checksum information for table.
  • -e, --extend-check Try to recover every possible row from the data file. Normally this will also find a lot of garbage rows; Don’t use this option if you are not totally desperate.
  • -f, --force Overwrite old temporary files.
  • -r, --recover Can fix almost anything except unique keys that aren’t unique.
  • -n, --sort-recover Forces recovering with sorting even if the temporary file would be very big.
  • -p, --parallel-recover Uses the same technique as ‘-r’ and ‘-n’, but creates all the keys in parallel, in different threads.
  • -o, --safe-recover Uses old recovery method; Slower than ‘-r’ but can handle a couple of cases where ‘-r’ reports that it can’t fix the data file.
  • -q, --quick Faster repair by not modifying the data file. One can give a second ‘-q’ to force myisamchk to modify the original datafile in case of duplicate keys. NOTE: Tables where the data file is currupted can’t be fixed with this option.
  • -u, --unpack Unpack file packed with myisampack.

Other actions:

  • -a, --analyze Analyze distribution of keys. Will make some joins in MySQL faster. You can check the calculated distribution by using ‘--description --verbose table_name’.
  • -d, --description Prints some information about table.
Add your comment

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

Comments on this entry are closed.

  • Lucas September 2, 2008, 4:44 am

    Great stuff. Thanks a lot for sharing! I needed this just a few days ago…

  • Ajith Edassery November 25, 2008, 4:47 am

    Good stuff indeed Ramesh. I am stumbling it right now

  • Govi April 16, 2009, 11:55 am

    myisamchk *.MYI is not working in my database. Any advice?

  • Jack December 16, 2009, 3:57 pm

    Hello folks!
    Why is it so complicated to achieve small things such resetting a root password or something else with mysql after re-installing it? This is like frustrating newcomers…I think that a software with million of codes like mysql should be able to reset everything itself after you get it re-installed- This is really said!
    I was trying tutorials to understand it and saidly i should restart my Windows and re-install mysql as well in wamp, but that one still keep thinking an old password is still anywhere… Trying to give a new or old password dont help- I’m not familiar with lot of codes as just newcomer but i’m forced to act like a pro for such small thing that all softwares normally do themselves in general… Damn!
    for those they think time is money here’s a lot of time to lose for nothing…

  • Shyam October 28, 2010, 6:28 pm

    Very useful article. Thanks.

  • claus August 2, 2011, 3:40 am

    Thank You very much for the description, it works also on a windows server.

  • sultan sadiq March 24, 2012, 3:38 am

    thax you tried to help me in your level best

  • kathy January 19, 2013, 11:10 pm

    All links seem to be corrupted. Someone told me to run myisamchk command, but how do I do this on a Wordpress site hosted by Yahoo? Thanks so much. k

  • Vankush Sen November 20, 2013, 4:35 am

    Very nice information you have share to all users but i have share some thing new like Kernel for MySQL repair tool is the best solution for how to repair database in MySQL 2000, MySQL Server 2005 and MySQL Studio Express. It is a simple to use solution which allows users of all levels to repair MySQL databases.

  • Spudweb January 30, 2016, 6:54 am

    Thank you so much, this article was very helpful.