Monday, September 27, 2010

ERROR 126 (HY000): Incorrect key file for table

A very interesting incident happened with me last week, all of a sudden mysql error logs flooded with "ERROR 126 (HY000): Incorrect key file for table" error,  and  in error log files.
I found new heavy queries added in recent release were creating this problem.
Those queries were also failing on mysql shell after that error.

Reason: queries using DISTINCT, COUNT, ORDER BY doing lots of sorting take huge temporary disk space to get final results, whenever temporary disk size is not enough to store temporarily created mysql data start giving these errors or insufficient space in /tmp directory.

Solution : Change the location of /tmp directory to other having more space and sufficient to store temporary tables while executing queries.

Use tmpdir='path' in my.cnf to change tmp directory path.

No comments:

Post a Comment