Re: File full error in simple select statement
Posted by: Peter Brawley
Date: May 11, 2021 11:41PM

Innodb_temp_data_file_path is set to ibtmp1:12M:autoextend

Increasing heap table size to 15G does not prevent the error.

https://stackoverflow.com/questions/19308456/mysql-user-created-temporary-table-is-full says setting row_format=dynamic can help. It does not prevent the error.

The issue is reported at https://bugs.mysql.com/bug.php?id=99100. Recommended workaround is ...

set session internal_tmp_mem_storage_engine=Memory;

... which does prevent the error. The bug page does not report that the bug has been fixed in 8.0.25 (which looks like a hurried release to fix a more urgent problem).

The main issue in the query that prevents optimising this problem away is the OR join ...

  join dr02 d2 on (w.weekend >= d2.date and d2.t1 <> "g") 
               or (w.weekend >= d2.weekend and d2.t1 = "g")

Here, running on an SSD, it took nearly 5 mins to return a result. Adding covering indexes does not help. Replacing the OR join with unioned joins slows it further. Probably you'll need to build and perhaps maintain an intermediary result table for this query, or adjust the DDL to eliminate the need for an OR join.

Options: ReplyQuote


Subject
Written By
Posted
Re: File full error in simple select statement
May 11, 2021 11:41PM


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.