Linux – MySQL Table not repairing

databasedatabase-administrationlinuxmyisamMySQL

Table info:

Database name: user_motiva
Table name: wp_options.frm  wp_options.MYD  wp_options.MYI  wp_options.TMD

when I do a mysqlcheck -r –all-databases it gets hung on that table even if you let it sit all day. Even just a check gets hung at same place.

Is there anther way to fix/repair/recover that table?

Should I use myisamchk? I saw something like:

shell> myisamchk --recover City

You can't even access/view the database from phpMyAdmin or even "USE ;" in mysql without it just hanging.

My config on a 16GB ram box

 cat /etc/my.cnf
[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
skip-networking
max_connections = 500
max_user_connections = 20
key_buffer = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 64M
read_buffer_size = 12M
sort_buffer_size = 12M
read_rnd_buffer_size = 12M
table_cache = 2048
thread_cache_size = 16K
wait_timeout = 30
connect_timeout = 15
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
low_priority_updates=1
concurrent_insert=ALWAYS
log-error=/var/log/mysql/error.log
tmpdir=/home/mysqltmp
myisam_repair_threads=4
[mysqld_safe]
open_files_limit = 8192
log-error=/var/log/mysql/error.log

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

Is this because of a crashed table from doing killall -9 mysqld because it would not shutdown and restart?

EDIT:

root@server [/var/lib/mysql/user_motiva]# myisamchk -e *.MYI
Checking MyISAM file: wp_options.MYI
Data records:    1827   Deleted blocks:       3
myisamchk: warning: 3 clients are using or haven't closed the table properly
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check records and index references
MyISAM-table 'wp_options.MYI' is usable but should be fixed
root@server [/var/lib/mysql/user_motiva]# myisamchk --safe-recover wp_options.MYI
- recovering (with keycache) MyISAM-table 'wp_options.MYI'
Data records: 1827
myisamchk: error: Can't create new tempfile: 'wp_options.TMD'
MyISAM-table 'wp_options.MYI' 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
root@ns2 [/var/lib/mysql/user_motiva]# myisamchk -o -f wp_options.MYI
- recovering (with keycache) MyISAM-table 'wp_options.MYI'
Data records: 1827

Does this mean that it is now fixed? If so how do I move it back? (this was done on a different server)
Is there a way to maybe bring MySQL down on the main server and run a command to fix all the files?

Best Answer

mysqlcheck runs a number of actions: check, repair, analyze and optimize. You're currently jumping to "repair" (-r) but should really start with "check" just to see what's going on and to see if there's any response:

mysqlcheck --check --quick user_motiva wp_options

Add "-p" if a password is needed (eg, not in a config file).

If that passes, try it without the "--quick". Once you've identified the problem (if any) it should be easier to proceed.

By the way, "myisamchk" is another way to check tables. Major difference here is that it's used when the database isn't running. Which to use depends on whether or not you need to keep running for the sake of other data.

Related Topic