> set SQL_BIG_TABLES = 1;
You also will have to keep in mind that now that a filesystem is being
used for the MySQL work you'll need the room to do it. In some cases the
default MySQL install uses '/var/tmp' for it's chalkboard which in some OS
installations, referring to UNIX of course, this is typically set too
small for some larger SELECT requirements.
Setting this tmp work to be done in a separate partition on a separate
drive is the best (IMHO) but may be impossible in most situations. I
personally avoid /tmp, unless it's mounted on a separate filesystem, due
to the filling up of your / partition.
You can find your tmpdir setting by typing in a MySQL client connected to
a running server:
show variables <-- Look for the tmpdir variable.
You can then change the tmpdir setting in a number of ways all found in
the manual. Here are a few considerations from a local filesystem
installation of the MySQL-3.23.32 Docs:
manual.html#Temporary_files
manual.html#Command-line_options <-- Look for -t, --tmpdir=path
manual.html#Programs <-- Discusses using environment variable TMPDIR
You can also create an entry in your my.cnf (Version get's important
here.) to point to a new tmpdir:
tmpdir = /NEW_PATH/
On a different note, you might want to check your SELECT to see how well
it's actually working by using the EXPLAIN command. In a MySQL client
connected to a running MySQL server type:
EXPLAIN SELECT [Rest of your statement.]
This should give you a good idea as to why your SELECT is requiring more
than what is `normal'.
Finally, yes long winded, I'm assuming your not using 3.23.x since it has
now changed to automatically creating disk-based temporary tables if it
needs to. (Still a filesystem size requirement though.) You might want to
upgrade depending on your situation.
On Fri, 16 Feb 2001, Fred van Engen wrote:
> On Fri, Feb 16, 2001 at 09:55:32AM +0100, Chappuis J.-Vincent wrote:
> > Hi,
> > I have this error when I execute a query : ERROR 1114: The table 'SQLb07dc_0' is
>full
> > I can execute other queries without problems but when I want to execute the query :
> >
> > there is always the same error message.
> > How can I do to reset the table 'SQLb07dc_0' or to reset the sql buffer ?
> >
>
> I've seen this when MySQL uses an internal table for temporary data.
> There is a limit on the size of these tables. You can force MySQL to
> use a file in its temporary directory to store the results:
>
> set SQL_BIG_TABLES = 1;
>
> An alternative is to do a SELECT SQL_BIG_RESULT ... instead of
> SELECT ... . Note that SELECT SQL_BIG_RESULT doesn't work in 3.22.x,
> though it is accepted by the parser. You don't mention which version
> you use. 'set SQL_BIG_TABLES' works fine in 3.22.x.
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
