Re: File full error in simple select statement
Posted by: Peter Brawley
Date: April 27, 2021 10:24AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Re: File full error in simple select statement
April 27, 2021 10:24AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.