[CentOS] MySQL error 28, can't write temp files - how to debug? [SOLVED]

[Les Mikesell]

Rudi Ahlers wrote:
> On Thu, Nov 5, 2009 at 11:47 PM, Alan Hodgson <ahodgson at simkin.ca> wrote:
>> On Thursday 05 November 2009, Rudi Ahlers <Rudi at softdux.com> wrote:
>>> According to google search, errorcode 28 means the HDD is full. But it
>>> isn't:
>>>
>>>
>>> root at vps:[~]$ df -h
>>> Filesystem            Size  Used Avail Use% Mounted on
>>> /dev/sda1              84G   18G   62G  23% /
>>> none                  640M     0  640M   0% /dev/shm
>>> /usr/tmpDSK           485M   11M  449M   3% /tmp
>>>
>>>
>>> What else could cause this kind of problem?
>> You only have 449MB free on /tmp. It could easily fill that up during the
>> query, and then delete the file before you run df again. Run it while the
>> query is executing, I bet you see /tmp filling up.
>>

> Thanx, that seems to have solved the problem. I didn't think of
> checking to see if the tmp folder got full during the SQL statement
> execution. So, by increasing it to 1GB, the problem is solved

I've seen mysql do dumb stuff like building a huge temp table in a 3-way 
join before evaluating any of the WHEN clause that would eliminate most 
of it, but does it really have to make a copy of a single table to pick 
a random chunk?

-- 
   Les Mikesell
    lesmikesell at gmail.com