Rudi Ahlers
2009-Nov-05 22:29 UTC
[CentOS] MySQL error 28, can't write temp files - how to debug? [SOLVED]
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. > > -- > "No animals were harmed in the recording of this episode. We tried but that > damn monkey was just too fast." > _______________________________________________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 -- Kind Regards Rudi Ahlers CEO, SoftDux Hosting Web: http://www.SoftDux.com Office: 087 805 9573 Cell: 082 554 7532
Les Mikesell
2009-Nov-05 22:44 UTC
[CentOS] MySQL error 28, can't write temp files - how to debug? [SOLVED]
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 solvedI'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