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 ''
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;

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.


Is and 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?


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.
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