MySQL incorrect key file for tmp table when making multiple joins

Sometimes when this error happens with temp tables:

#126 - Incorrect key file for table '/tmp/#sql_64d_0.MYI'; try to repair it

It can be because the /tmp folder is running out of space. On some Linux installations, /tmp is in its own partition and does not have much space – big MySQL queries will fill it up.

You can use df -h to check whether \tmp is in its own partition, and how much space is allocated to it.

If it is in its own partition and short of space, you can either:

(a) modify /tmp so that its parition has more space (either by reallocating or moving it to the main partition – e.g. see here)
(b) changing MySql config so that it uses a different temp folder on a different partition, e.g. /var/tmp

Leave a Comment