MySQL MyISAM Utility

MyISAM 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(0×9df119c)’) 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 \

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

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.