How to Repair a Crashed MySQL Table

How to Repair a Crashed MySQL Table

Home

Certifications

Cisco

Protocols

RemoteAccess

Resources

Security

Telecommunications

Tools

Unix

Web

mindterm

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. This page was created in 0.00103 seconds Comments and Questions Last modified: July 21 2009.