you can make mysql to use memory for creating temp files rather than disk set sort_buffer_size=1024*1024*100; and try your query.
Also the size of the columns is based on the db engine ur using, i dont remember the exact number, i will denfinitly find it in mysql docs. regards anandkl On 10/22/08, Heston James - Cold Beans <[EMAIL PROTECTED]> wrote: > > Hi Rob, > > Thanks for getting back to me on this. You were right on the money with the > imposed quota on that directory, I tried copying some large files across to > the folder and found it limited to 8Mb for some reason, I'll look into > removing that limit. > > One thing which is puzzling me at the moment is why its producing such > large > record sets from my query. Even when limiting the query to 700 records it > still exceeds this 8Mb limit. > > The query is quite basic and is only returning very simple strings and > integers, no more than maybe 8 chars long, like so: > > SELECT bluetooth_session.bluetooth_session_id AS > bluetooth_session_bluetooth_session_id, > bluetooth_session.result AS bluetooth_session_result, > bluetooth_session.address AS bluetooth_session_address, > bluetooth_session.message_id AS bluetooth_session_message_id, > bluetooth_session.campaign_id AS bluetooth_session_campaign_id, > bluetooth_session.created AS bluetooth_session_created, > bluetooth_session.modified AS bluetooth_session_modified > FROM bluetooth_session > WHERE bluetooth_session.created > %s > ORDER BY bluetooth_session.created > LIMIT 1, 699 > > bluetooth_session.result and bluetooth_session.address are both 8 character > varchars and the rest are integers and dates. > > Would you really expect a record set from this query to be so large? Is > there any way to make it smaller and more efficient? > > Cheers Rob, > > Heston > > -----Original Message----- > From: Rob Wultsch [mailto:[EMAIL PROTECTED] > Sent: 21 October 2008 16:49 > To: Heston James - Cold Beans > Cc: mysql@lists.mysql.com > Subject: Re: Error Code 28 > > On Tue, Oct 21, 2008 at 8:26 AM, Heston James - Cold Beans < > [EMAIL PROTECTED]> wrote: > > > Afternoon all, > > > > > > > > I have a SELECT query which is returning the following error: > > > > > > > > (InternalError) (3, "Error writing file '/tmp/MYqlGnfn' (Errcode: 28)") > > > > > > > > After doing a little searching on google all signs seem to point to a > lack > > of disk space to be able to store the query results. > > > > > > > > However, I have several hundred MB left on the storage device and the > > database itself, in its entirety is only around 19Mb in size so it sees > > very > > strange to be causing that. Here is a quick output from 'fd -h' which > > displays the space on my storage device. > > > > > > > > Filesystem Size Used Avail Use% Mounted on > > > > rootfs 973M 595M 330M 65% / > > > > udev 10M 20K 10M 1% /dev > > > > /dev/disk/by-label/ROOT_FS > > > > 973M 595M 330M 65% / > > > > /dev/disk/by-label/ROOT_FS > > > > 973M 595M 330M 65% /dev/.static/dev > > > > tmpfs 126M 0 126M 0% /lib/init/rw > > > > tmpfs 126M 0 126M 0% /dev/shm > > > > tmpfs 8.0M 0 8.0M 0% /rw/tmp > > > > > > > > Can anyone offer any suggestions as to what might be causing this issue > and > > anything I can do to correct this? I'd really appreciate some help. I'm > > running MySQL 5 on a Debian based system. > > > > > > > > If you need any more information what so ever, please let me know. > > > > > > > > Cheers in advance, > > > > > > > > Heston > > > > > 1. Just because your data set is small does not mean that mysql will not > create a larger temporary file to store a temporary table. > 2. If I had to guess I would say some sort of quota is in effect, also > possibly for /tmp/ in particular. > > -- > Rob Wultsch > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
