MySQL error: Error writing file '/tmp/MYrZo6Qh' (Errcode: 28)

bruxelles2010 · 11-15-2010, 04:25 PM

No, the disk is not full and there are only a few files in tmp directory, but some MySQL queries return this error:

Error writing file '/tmp/MYrZo6Qh' (Errcode: 28)

Something strange:

PHP Code:



select from table where c1='1' and c2='1' and c3='1'

returns Error 28 but

PHP Code:



select from table where c1='1' and c2='1' and c3='0'

works fine.

Any ideas?

Thanks.

paulsm4 · 11-15-2010, 04:32 PM

Q: Can you reproduce this, or was it just a "one time thing"?

Q: Did you do a "df" to see if ANY of your filesystems were full (or nearly full)?

Q: What's the exact SQL? Perhaps when you "obfuscated" your query, you left off something significant?

bruxelles2010 · 11-15-2010, 05:01 PM

paulsm, thank you for your message.

This was not a one time thing. The first query works and the second not. I was using this query for years. Now the problem appeared in a new server installation. It's MySQL 5.0.51a

PHP Code:



Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/md0             960392136   7477420 904129536   1% /
tmpfs                  1815680         0   1815680   0% /lib/init/rw
udev                     10240     10240         0 100% /dev
tmpfs                  1815680         4   1815676   1% /dev/shm
/dev/tmpMnt               9263        89      8696   2% /tmp

paulsm4 · 11-15-2010, 05:37 PM

Methinks I see the problem

Quote:

/dev/tmpMnt 9263 89 8696 2% /tmp

Q: Why don't you get rid of "/dev/tmpMnt" altogether, and carve "/tmp" out of your terabyte /dev/md0 drive?

But as it is now, your first "sort" that weighs in at more than 8MB will crash your query

paulsm4 · 11-15-2010, 05:56 PM

Q: Did moving /tmp to a larger (++100MB or, better, your TB root partition) help?

Inquiring minds want to know

bruxelles2010 · 11-16-2010, 04:57 AM

Paulsm4, thank you for your replies.

First of all, today my server crashed 2 times.

I reinstalled AIDE and MySQL errors are gone now and df shows the following:

Quote:

Filesystem 1K-blocks Used Available Use% Mounted on
/dev/md0 960392136 8039628 903567328 1% /
tmpfs 1815680 0 1815680 0% /lib/init/rw
udev 10240 692 9548 7% /dev
tmpfs 1815680 4 1815676 1% /dev/shm

No crashes so far.

paulsm4 · 11-17-2010, 02:16 PM

Hi -

Code:

# OLD:
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/md0             960392136   7477420 904129536   1% /
tmpfs                  1815680         0   1815680   0% /lib/init/rw
udev                     10240     10240         0 100% /dev
tmpfs                  1815680         4   1815676   1% /dev/shm
/dev/tmpMnt               9263        89      8696   2% /tmp

Code:

# NEW:
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/md0             960392136   8039628 903567328 1%   /
tmpfs                  1815680         0   1815680 0%   /lib/init/rw
udev                     10240       692      9548 7%   /dev
tmpfs                  1815680         4   1815676 1%   /dev/shm

So I'm guessing maybe you followed my suggested about moving "/tmp" someplace where it has a bit more elbow room

?

PS:
Any idea what might have been the problem with "/dev"? Perhaps something inadvertantly writing real data files (instead of simply using the links to kernel driver special files, which is pretty much all that should be in "/dev")?