On a machine at home, I have a query that I use which has been producing the following error:

ERROR 126 (HY000) at line ...

I've searched high and low for a solution for this problem and have tried remedies including repairing the involved tables.

After a day of looking for solutions under the assumption that there was something wrong with one of my tables, I came to find out that my /tmp directory used by MySQL was running out of space when running this large and inefficient query.

The remedy in this situation was to change the directory that MySQL uses to store temporary data.

In Ubuntu, and probably Debian, the place to change the path that MySQL uses as a temporary directory is the /etc/mysql/my.cnf file.

So, I edited the file as root and changed

tmpdir          = /tmp

to

tmpdir          = /media/a-drive-with-more-space/tmp

After saving the file, MySQL was restarted with a:

sudo /etc/init.d/mysql restart

And then my clunky resource hogging query was able to run without producing error messages.