Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
Just to let you know, the debian-sys-maint process (PID:11 above) has been "Repair by sorting" products for nearly 17 hours now - bearing in mind there's nothing in this table... –  Simon Sep 24 '10 at 8:09
    
Now it says it's been running for almost 2 straight days... so something interrupted it along the line and it started again –  Simon Sep 27 '10 at 14:15
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.