How to Repair a Crashed MySQL Table

Today, I had a server crash when the /var partition filled up while reindexing. I attempted to clear out extra log files and such but df was still showing greater than 100% utilization. Checking /var/db/mysql showed I had one table that was taking up too much space. du -d 1 -h is a very useful command when tracking down the largest folder usage on partition.

Jul 21 07:39:06 plab2catsg01 kernel: pid 36500 (mysqld), uid 88 inumber 329992 o
n /var: filesystem full
Jul 21 07:39:22 plab2catsg01 kernel: pid 36500 (mysqld), uid 88 inumber 353413 o
n /var: filesystem full
Jul 21 07:39:23 plab2catsg01 kernel: pid 36500 (mysqld), uid 88 inumber 353288 o
n /var: filesystem full
	

I attempted to stop MySQL by executing /usr/local/etc/rc.d/mysql-server stop and unfortunately, it kept looping while trying to stop the process. I was eventually forced to reboot the server.

On reboot, I thought all was well but then I found the Eventum application I use wasn't showing support emails. I checked the MySQL log files:

plab2catsg01# tail -f plab2catsg01.ten-net.net.err
...
090721  9:15:54 [ERROR] /usr/local/libexec/mysqld: Table './eventum/eventum_mail
_queue' is marked as crashed and last (automatic?) repair failed
090721  9:15:54 [ERROR] /usr/local/libexec/mysqld: Table './eventum/eventum_mail
_queue' is marked as crashed and last (automatic?) repair failed
090721  9:15:55 [ERROR] /usr/local/libexec/mysqld: Table './eventum/eventum_mail
_queue' is marked as crashed and last (automatic?) repair failed
090721  9:15:55 [ERROR] /usr/local/libexec/mysqld: Table './eventum/eventum_mail
_queue' is marked as crashed and last (automatic?) repair failed
...
	

That's not good. Fortunately, if I lose the table it's not critical but this is how you can try to repair a crashed table:

plab2catsg01# myisamchk eventum_mail_queue
Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200
Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
Checking MyISAM file: eventum_mail_queue
Data records:       0   Deleted blocks:       0
myisamchk: warning: Table is marked as crashed and last repair failed
- check file-size
myisamchk: warning: Size of indexfile is: 14201856      Should be: 1024
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check record links
myisamchk: error: Record-count is not ok; is 489063       Should be: 0
myisamchk: warning: Found     489063 parts                Should be: 0 parts
MyISAM-table 'eventum_mail_queue' is corrupted
Fix it using switch "-r" or "-o"
	

The myisamchk command wasn't able to repair the table using its default flags so it advised I try the -r or -o options. The -r option is to repair the table which is what I did below:

plab2catsg01# myisamchk -r eventum_mail_queue
Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200
Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
- recovering (with sort) MyISAM-table 'eventum_mail_queue'
Data records: 0
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
Data records: 489063
You have new mail.
plab2catsg01#
	

If you have a lot of corruption, you can check and repair an entire MySQL database as such:

# myisamchk --silent --force --fast --update-state /var/lib/mysql/eventum/*.MYI

myisamchk: MyISAM file /var/lib/mysql/eventum/groups.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: MyISAM file /var/lib/mysql/eventum/profiles.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
	

The options are:

  • -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.

Additional things you can do with myisamcheck include identifying all corrupted ables:

# 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

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

You can also use myisamchk to get basic information about a table such as:

# myisamchk -dvv albums.MYI

MyISAM file:         albums.MYI
Record format:       Packed
Character set:       latin1_swedish_ci (8)
File-version:        1
Creation time:       2012-01-16 13:07:26
Status:              open,changed
Auto increment key:              1  Last value:                   155
Data records:                   97  Deleted blocks:                 0
Datafile parts:                180  Deleted data:                   0
Datafile pointer (bytes):        6  Keyfile pointer (bytes):        6
Datafile length:             10708  Keyfile length:              9216
Max datafile length: 281474976710654  Max keyfile length: 288230376151710719
Recordlength:                 1893

table description:
Key Start Len Index   Type                     Rec/key         Root  Blocksize
1   5     4   unique  unsigned long                  1         2048       1024
2   13    255 multip. varchar prefix                 0         7168       2048

Field Start Length Nullpos Nullbit Type
1     1     4                                                         
2     5     4                      no zeros                           
3     9     4      1       1       no zeros                           
4     13    256                    varchar                            
5     269   10     1       2       blob                               
6     279   10     1       4       blob                               
7     289   8      1       8       no zeros                           
8     297   10     1       16      blob                               
9     307   4                      no zeros                           
10    311   4                      no zeros                           
11    315   4                      no zeros                           
12    319   256    1       32      varchar                            
13    575   4      1       64      no zeros                           
14    579   21     1       128     varchar                            
15    600   21     2       1       varchar                            
16    621   4      2       2       no zeros                           
17    625   4      2       4       no zeros                           
18    629   4      2       8       no zeros                           
19    633   4      2       16      no zeros                           
20    637   256                    varchar                            
21    893   10     2       32      blob                               
22    903   4      2       64      no zeros                           
23    907   4      2       128     no zeros                           
24    911   12     3       1       blob                               
25    923   10     3       2       blob                               
26    933   4      3       4       no zeros                           
27    937   10     3       8       blob                               
28    947   128    3       16      varchar                            
29    1075  193    3       32      varchar                            
30    1268  4                      no zeros                           
31    1272  4      3       64      no zeros                           
32    1276  258    3       128     varchar                            
33    1534  10     4       1       blob                               
34    1544  256    4       2       varchar                            
35    1800  65     4       4       varchar                            
36    1865  8      4       8       no zeros                           
37    1873  8      4       16      no zeros                           
38    1881  8      4       32      no zeros