Fix corrupted MySQL tables with myisamchk

MyISAM is the main, or rather, one of the main (along with InnoDB) storage systems in the MySQL DBMS. At the same time MyISAM tables are damaged very simply - there are no problems with this. It is more difficult to repair all damage, although this can also be done fairly quickly. This article explains how to solve the problem using myisamchk to identify the problem with MyISAM and fix it.

It is well known that when creating a table in MySQL, three different files are created: * .frm - table format, * .MYD (MyData) - data storage, * .MYI (MyIndex) - index. For large databases, use InnoDB because there is some similarity with Oracle and the corresponding functionality.

As an example of the demonstration of the error, we will use this:

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 

')
It is clear that the attach_data table is damaged and needs to be fixed. The table will be fixed using myisamchk.

1. Determine all damaged 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 

If you specify the output of myisamchk to a temporary file, only the names of the damaged tables will be displayed. But in the /tmp/myisamchk_log.txt file there will be much more data including the names of the intact tables:

 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. Fix corrupted tables using myisamchk

To do this, use myisamchk, with the -r option, as shown below:

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

An error message may appear here: clients should, if the tables are still used by any application or other tables. In order to avoid this error, you should complete mysqld before you begin fixing the tables. Here you can use FLUSH TABLES.

3. Run the check and fix for the 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: print only errors. You can use double -s -s to make it as quiet as possible;
-f: automatically restart myisamchk with the -r option, there are errors found;
-F: check only tables that were not closed in normal mode;
-U: mark tables as damaged if errors are found.

4. The use of additional memory for large MySQL databases

In the case of working with large databases, recovery may take several hours. If there are additional resources, they can be used to speed up the process:

 # 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. Using myisamchk to retrieve table data

If necessary, you can get quite a lot of data.

 # 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 options myisamchk

In order to get additional information on the team, you should use the help:

# myisamchk - help

General options:

-s: only error output;
-v: output more information;
-V: output version and exit;
-w: wait if the table is locked.

Scan options:

-c: check tables for errors;
-e: very "rough" check. It should be used only as a last resort, if in normal mode errors are not detected;
-F: quick check, only tables that are not closed correctly are checked;
-C: checking only tables that have changed since the last calibration;
-f: automatically restart myisamchk with the -r option, there are errors found;
-i: show statistics on checked tables;
-m: lite check mode, 99.99% of errors are faster than normal;
-U: status update: mark tables as damaged if any errors are detected;
-T: Do not mark tables as checked.

Correction options:

-B: backup file .MYD, "filename-time.BAK";
--correct-checksum;
-e: attempt to correct the maximum number of lines in the data file. In addition, this command finds “junk” strings. You should not use this command if the situation is not hopeless;
-f: overwrite old temporary files;
-r: fixes almost everything except unique keys that are not really unique;
-n: force sorting, even if the temporary file is very large;
-o: use the old recovery method;
-q: quick fix without modifying the data file;
-u: unpacking a file packed with myisampack.