Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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

Should I use myisamchk? I saw something like:

shell> myisamchk --recover City

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

and could this have happened 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?

share|improve this question

2 Answers 2

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

Absolutely. If you kill -9 mysqld and you're using MyISAM tables, you're almost guaranteed to crash some tables unless the server is really, really, really not busy... although the definition of a "crashed" table isn't as catastrophic as it sounds in all cases... it only means, at the simplest level, that the table is not in the state that it should have been in had the server closed it properly.

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

Have you been through the steps covered in How to Repair MyISAM Tables?

Also, consider these things, which may give you some ideas that could get you on a faster path to recovery if they are not familiar concepts:

  • With MyISAM tables, everything is in the files. You could literally take your wp_options.* files (assuming for the sake of discussion that they were not crashed), copy them into a database directory on another MySQL server, SHOW TABLES and you would find that your table is visible and available on that other server. For future recovery, if you had a slave server, you could shut it down and copy this table's files directly from there back to the master and be back online. (This is only true for MyISAM and ARCHIVE, not InnoDB, but by contrast, InnoDB tables are extremely immune from this sort of corruption due to a hard/forced shutdown).
  • The myisamchk utility is not technically a part of the actual MySQL Server itself -- it is a standalone utility built from many of the same underlying libraries, but can be used on a different computer, even without MySQL server installed. The myisamchk utility has various capabilities and levels of aggression.
  • You could, therefore, stop MySQL, copy the table files to a different machine -- one with a nice speedy SSD, for example, could make a big difference in recovery time -- and repair the files with myisamchk, then (with the MySQL server stopped again) copy the table files back. If you have other things running on your MySQL server, you could bring those other things back online while not bogging the server down with this repair.

By contrast, mysqlcheck is nothing but a simple program that connects to your MySQL server using the client libraries and...

...uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user.

...so while the two utilities use many of the same methods to repair the tables, they are also fundamentally very different.

share|improve this answer

See http://dev.mysql.com/doc/refman/5.5/en/myisamchk-memory.html for various tunables related to the amount of memory allocated to myisam check. Most notable is the myisam_sort_buffer_size param. The more memory you can throw at this the better. If you can give this one or more GB this will help speed things along.

mysqlcheck is designed to be running while mysqld is actually running. myisamcheck will work directly against the .MY[ID] files on the file system. This requires the mysqld first be shut down.

If you can take down the entire mysqld temporarily that will help free up additional memory you can feed to myisamcheck.

Assuming this is a dedicated mysql server with out any other services running I'd give myisamcheck 4 gigs of ram for the myisam_sort_buffer_size.

Running it with --force --recover will start rebuilding the table right away instead of just reporting it finds a problem with the broken table. If you want more screen action as to the progress it's making you can run with -v, -vv or -vvv to ramp up the verbosity of the progress.

share|improve this answer
    
Should I just make them massive big numbers? Should I add an entry for mysqlcheck too? –  Tiffany Walker Mar 5 '13 at 23:48
    
More details added in an edit... –  atxdba Mar 6 '13 at 0:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.