1

After I tried to index a column in a huge table (40G) and ~5million rows, I can no longer connect to the datatbase.

Indexing failed with this error

Later, I found out I ran out of disk space while mysql was stopped. And, now I can not get mysql run again. I tried almost everything on SO except where purging mysql/reinstalling was mentioned. Is there anything I can do to get my data back or run mysql again? Or, I could extract my tables from ibdata1?

3

There could be about 3 things going on here...

  1. You ran out of disk space. Can you scrounge some space, even a little?

  2. You cannot connect because of a socket problem. Remove the socket and try again.

  3. The attempt to add an index could have been performed in a couple of different ways. Was the table MyISAM? Or InnoDB? If InnoDB, was innodb_file_per_table ON?

In any case, look for #sql... files they were the tmp tables being built before "renaming" them into place. They are useless no; remove them.

If file_per_table was off, then probably ibdata1 got a lot larger in this failed operation. This will call more a messier repair.

Since you have an old version of MySQL, I won't point out ALTER TABLE .. ALGORITHM=INPLACE, ADD INDEX ..;

  • - I resized my instance. Now I have unused 20G+. - I'll try removing sockets and reconnect. - The table was InnoDB. Unfortunately, innodb_file_per_table was off. Thanks, I will try your suggestions in evening. – Rajender Joshi Sep 14 '16 at 2:49
  • @RajenderJoshi if Rick's solution worked can you mark as chosen? – Evan Carroll Jul 13 '18 at 15:13

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.