Re: File full error in simple select statement

[Peter Brawley]

Oy, reverting would be a mistake. It's something about that system, or WB/SqlYog, or a setting.

Does the query throw this error in the mysql client program, or in mysql shell? If so, it's a WB/Yog problem.

If not, keep increasing max_heap_table_size and tmp_table_size in increments, and monitor the rate of increase of the Created_tmp_disk_tables reported by SHOW GLOBAL STATUS, compared to Created_tmp_tables (temp tables that did not use disk). As the allowed tmp table size is able to hold a greater percentage of temp tables created, you should start to see the ratio of on-disk temp tables to in-memory temp tables decrease.

If that fails, does the subquery ...

  select w.weekend, d2.code as family, d2.amount
  from weekendings w  
  join dr02        d2 on (w.weekend >= d2.date and d2.t1 <> "g") 
                      or (w.weekend >= d2.weekend and d2.t1 = "g")
  where d2.amount is not null;

also throw the error?

If not, does this query throw it?

with cte as (
  select w.weekend, d2.code as family, d2.amount
  from weekendings w  
  join dr02        d2 on (w.weekend >= d2.date and d2.t1 <> "g") 
                      or (w.weekend >= d2.weekend and d2.t1 = "g")
  where d2.amount is not null
)
select weekend, code, sum(amount) as owing
group by weekend, code
order by weekend, code

If so, does this?

Create table tmp
  select w.weekend, d2.code as family, d2.amount
  from weekendings w  
  join dr02        d2 on (w.weekend >= d2.date and d2.t1 <> "g") 
                      or (w.weekend >= d2.weekend and d2.t1 = "g")
  where d2.amount is not null;
select weekend, code, sum(amount) as owing
from tmp
group by weekend, code
order by weekend, code

[/code]

If that also induces the error, let's see my.ini in full.