Heston,
What happens if you remove the "Order By" clause from the query?
Does the file size drop? Also why are you using "LIMIT 1, 699"? The "1"
skips the first row because that is the offset. If you just want the first
700 rows then use "Limit 700"
Mike
At 06:29 AM 10/22/2008, you 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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
