Incorrect key file for table ‘/tmp/mysqltmp/#sql_XXXX_0.MYI’; try to repair it

Today, I received bunch of “Incorrect key file for table” error and my sql server goes down. I have checked the MySQL logs and I found some /tmp partition issue. I have resolve this issue. If you are getting error Incorrect key file for table in mysql then here is the solution for you.

Error : Incorrect key file for table ‘/tmp/mysqltmp/#sql_XXXX_0.MYI’; try to repair it

Cause :
This is indicating the problem is with a temporary table.
This type of error generally means your host’s SQL server’s temporary folder is misconfigured, the SQL server’s harddrive is full, or that your account’s filespace quota is too low.

Solution :
It is a problem with the database server, so contact your host.
Or
If you have root access then change the “tmp” Directory location.

root@ykl [~]# cat /etc/my.cnf
#tmp directory
tmpdir=/var/mysqltmp
Enjoy 🙂