List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 4 1999 7:08pm
Subject:Huge insert fails with table full
View as plain text  
>>>>> "Richard" == Richard Ellerbrock <richarde@stripped> writes:

Richard> I am currently writing a program to process sendmail log files using a
> combination of MySql 3.22.25 and php for the web interface. The tables that I am working
> with are pretty huge (about 300000 records) and my select statements all require set
> sql_big_tables = 1. 
Richard> I do have a problem with INSERT INTO table generating a table full message.
> This is using the exact same select statement that works just fine if I don't INSERT INTO
> another table. The offending code is below:

Richard> ***cut***

Richard> use maillog;

Richard> create table temp
Richard>    (email varchar(80), num mediumint unsigned, tot mediumint unsigned);

Richard> set sql_big_tables = 1;

Richard> insert into temp
Richard> select
Richard>    efrom,
Richard>    count(efrom) as num, sum(size) as tot
Richard> from
Richard>    email
Richard> where
Richard>    lower(efrom) rlike 'eskom.co.za'
Richard> group by
Richard>    efrom
Richard> order by
Richard>    tot desc
Richard> limit
Richard>    20;

Richard> select
Richard>    *
Richard> from
Richard>    temp
Richard> left join
Richard>    nds on temp.email=nds.email;

Richard> drop table temp;

Richard> ***cut***

Richard> Any ideas on a solution. I have tried to combine the two select statements,
> but joining the two tables causes the select process to go on forever. If I add another
> where clause limiting the search to only one day, then all is well. I have also tried
> adding --big-tables to the mysqld startup and that also did not help.

Hi!

Is temp.email and nds.email declared identical?

Try running 'EXPLAIN' on both SELECT queries.

Do you mean that the insert doesn't work ?   What happens if you
remove the ORDER BY part from the query?

Regards,
Monty

PS: In MySQL 3.23, mysqld will first create a HEAP table to hold the
    result and when the heap table is full it will convert it to a
    MyISAM table.
Thread
Huge insert fails with table fullRichard Ellerbrock4 Aug
  • Huge insert fails with table fullMichael Widenius4 Aug
Re: Huge insert fails with table fullRichard Ellerbrock5 Aug
Re: Huge insert fails with table fullMichael Widenius5 Aug