MySQL Table not repairing

Question

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?

Solution 1

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.

Solution 2

Does this mean that it is now fixed?

No, it does not. Your pasted output clearly states

MyISAM-table 'wp_options.MYI' is not fixed because of errors

And the reason for that seems to be

myisamchk: error: Can't create new tempfile: 'wp_options.TMD'

You could check if the user you are executing myisamchk with has the necessary permissions to create files in the data directory, if the file is not already present with "wrong" permissions and if files can be created at all on the filesystem (i.e. it is not mounted read-only, has errors or is full).

Note that you are repairing the .MYI files which only contain index information (copies of indexed database columns stored sorted in a given order to speed up searches). So if it is the index file (.MYI) which is causing the problem while repairing / mounting the database, consider simply removing it from the data directory, starting the MySQL daemon and running REPAIR TABLE wp_options to rebuild the index information from the data in the data file.

If the data file itself (.MYD) is affected by the corruption, you should run myisamchk on the .MYD file without using the -e option first as the myisamchk docs explicitly state "[not to] use this option unless you are desperate."

Solution 3

I got into exactly the same problem, when running mysqlrepair database.

The problem 1 was: wrong groupid in /etc/passwd file for user mysql. While it was different from groupid of group mysql in file /etc/group Please check and correct if needed before continuing to next step.

Problem 2 was: during the repair run, the files *.TMD are created for each database table in usually /var/lib/mysql/database directory. This is fixed by running:

rm /var/lib/mysql/*/*.TMD

and then sucessfully run:

mysqlrepair -p database

where -p for supplied password. Please also add -uusername if needed.

Comments

[Giacomo1968]

What about doing a mysqldump and then drop the database, recreate it & reimport it?

[Tiffany Walker]

Did not try that but you can't even access/view the database from phpMyAdmin or even "USE <DATABASE>;" in mysql without it just hanging. It also looks like CPanel services lockup too because of that.

[Tiffany Walker]

Tried doing a check and it still got hung

[Qwerty-Space]

OK, that's seriously damaged so you could try something a bit more extreme. With DB offline, copy the file .FRM and .MYD files (not the .MYI) to either another server of the same MySQL version or to a new name. Make certain the file owners/permissions match (with "ls -l"). The server will hopefully be able to build a new MYI file if the data isn't too badly corrupted. Check this new table as discussed earlier.

[Qwerty-Space]

Should have suggested that you delete the TMD but hadn't noticed it earlier. May as well try to rebuild the MYI anyway. Also, if the files are too big to copy and you don't mind living dangerously, could always delete the TMD, retry, then delete the MYI (DB offline while deleting the files). I wouldn't do this but it's possible.