MySQL table is marked as crashed and last repair failed
1.Problem description. One table in my database was crashed, and automatic repair of the MySQL system failed. error message occured when I use desc table:
(root:cz:)[foot]> desc others_ipstat;
ERROR 144 (HY000): Table './foot/others_cz' is marked as crashed and last (automatic?) repair failed
(root:cz:)[foot]> show create table others_ipstat\G
ERROR 144 (HY000): Table './foot/others_cz' is marked as crashed and last (automatic?) repair failed
alter disable keys failed:
(root:cz:)[foot]> alter table others_cz disable keys;
ERROR 144 (HY000): Table './foot/others_cz' is marked as crashed and last (automatic?) repair failed
The following lists the table’s info, it identified the index file others_cz.MYI may be the reason of the repair failed, because frm and MYD file had a long time no update.
-rw-rw---- 1 mysql mysql 8.6K 2011-07-12 others_cz.frm
-rw-rw---- 1 mysql mysql 17G 03-15 23:00 others_cz.MYD
-rw-rw---- 1 mysql mysql 16G 06-05 19:38 others_cz.MYI
- The error message was presented when I use myisamchk to recovry:
[root@cz foot]# /usr/local/mysql/bin/myisamchk -r others_cz - recovering (with sort) MyISAM-table 'others_cz' Data records: 0 - Fixing index 1 - Fixing index 2 - Fixing index 3 myisamchk: error: myisam_sort_buffer_size is too small MyISAM-table 'others_cz' is not fixed because of errors Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
Modify the tmpdir variables value:
+-------------------+--------------+
| Variable_name | Value |
+-------------------+--------------+
| max_tmp_tables | 32 |
| slave_load_tmpdir | /data/tmp |
| tmp_table_size | 257949696 |
| tmpdir | /data/tmp |
+-------------------+--------------+
4 rows in set (0.00 sec)
Error was still there:
[root@bigfoot02 bigfoot]# /usr/local/mysql/bin/myisamchk -r others_ipstat
- recovering (with sort) MyISAM-table 'others_ipstat'
Data records: 0
- Fixing index 1
- Fixing index 2
- Fixing index 3
myisamchk: error: myisam_sort_buffer_size is too small
MyISAM-table 'others_cz' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
3.note Repair table in mysql client is equal to ‘myisamchk -r’ in shell env, the difference is that repair command use the value of variables relay on MySQL Server parameters， so tmpdir and sort_buffer_size should be set relevantly. myisamchk need extra specified, because myisamchk allocate memory does not relay on MySQL Server. read more from:http://dev.mysql.com/doc/refman/5.5/en/myisamchk-memory.html
[root@cz foot]# /usr/local/mysql/bin/myisamchk --sort_buffer_size=2G --key_buffer_size=512M --read_buffer_size=32M --write_buffer_size=32M --tmpdir=/data/tmp/ -r others_cz
- recovering (with sort) MyISAM-table 'others_cz'
Data records: 0
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
Data records: 491792780
Resource used durning the myisamchk repair:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7025 root 18 0 2108m 2.0g 1012 D 62.9 13.0 18:28.51 myisamchk
In addition to there maybe lossing records or mistake, you can check table frequently by use mysqlcheck.