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)
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

  • 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...
    – simonhamp
    Sep 24, 2010 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
    – simonhamp
    Sep 27, 2010 at 14:15

1 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.

  • 1
    just a note to anyone googling this, I got a Segmentation Fault as well when running myisamchk because I had ran out of hard drive space for the temp files. I resized my instance, tried again and it worked. Oct 10, 2016 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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