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.