How do I restore my MySQL so it can work again?

Question

Ok so this starts the other day. I tried creating an index on a large table (7million rows) and it hung. Well it went into "Repair by keycache"... which I found out wasn't so great.

Well the process got interrupted. The ALTER failed and no index was made. On further attempts to do it something happened (I don't know what or how I caused it), but the entire table became corrupted and went into "Incorrect key file for table"

So I started repairs (nothing worked) - was taking hours and bailing. So I tried restoring from a backup... this didn't go too well.

So I tried restoring from another server's .MYI and .MYD files. This didn't work either.

So I ditched the table and recreated it empty (the data wasn't an issue). All systems go, everything's running smooth. But every time I try to do an insert on the recreated table MySQL crashes. Then the next query (usually my attempt to see what's going on at the shell with SHOW PROCESSLIST\G;) boots every connection from the server and I get errors like these:

*************************** 1. row ***************************
     Id: 11
   User: debian-sys-maint
   Host: localhost
     db: NULL
Command: Query
   Time: 1
  State: Repair by sorting
   Info: products

*************************** 3. row ***************************
     Id: 71
   User: debian-sys-maint
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: System lock
   Info: select count(*) into @discard from `creloaded`.`products_options_values_to_products_options`


ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR: 
Can't connect to the server

And so the entire server becomes unusable... until I stop the server and delete the products table files.

So I tried recreating the entire schema containing this offending table. Gave it a different name. The table name is still the same though: products

Same problem as above as soon as I try to insert any data into it. So I'm guessing something a little more drastic is happening or there's a deeper reference to this table and/or it's indexes somewhere that MySQL feels a desperate need to repair!

Can anyone shed any light on this and perhaps some assistance?

I'm running Ubuntu Server 10.04 and MySQL 5.1.41 running on a 32-bit machine with 2GB RAM

Answer 1

I managed to rectify the problem... although I don't really know what the cause of it is so it may happen again of course.

So basically I stopped MySQL and did a myisamchk on the products table (in it's new schema) by:

1. sudo su -ing
2. cd /var/lib/mysql/{scema_name}/
3. myisamchk -o -f products

I had tried this before, but it was going to take days with a full table. But because the table now had no records in it, it was done in a few seconds and this seemed to clear whatever flag MySQL sets for crashed tables.

So it has now stopped trying to repair it every time the server boots up and I have successfully written nearly 5 million rows back to the table without a hitch.

The Lesson?

Back up your table data, empty the table, do a myisamchk -o on the empty table and then re-INSERT your data

Interestingly I tried to do a myisamchk -r but I kept getting a Segmentation Fault.