Temporary table is full

The table '/mnt/disks/data/tmp/#sql58de_a0f6_7' is full

The query is quite complex/big. Lots of joins in joins and unions, sorting, grouping, ...
1 out of 10 the result is returned. Otherwise it results in the above error.

CPU and memory increase but never max out while running the query.
There is plenty of disk space (100Gb free).

I have fiddled with:

innodb_log_file_size
innodb_log_buffer_size
innodb_temp_data_file_path
innodb_data_file_path
innodb_data_home_dir

max_heap_table_size
tmp_table_size

table_open_cache
table_definition_cache
thread_cache_size
sort_buffer_size

tmpdir and slave_load_tmpdir are set to /mnt/disks/data/tmp which has plenty of space and nevers runs out of space when running the query. innodb_tmpdir is set to NULL which means it falls back to tmpdir?

Some settings I've changed because of things I read about this issue. Others after mysqltuner advice.
But I could not let the query succeed consistently.

So: there is something running out of space. But what? And where?

• Version: Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)

• Storage engine: InnoDB

See the bug link in @Yoseph's answer above (although the temptable_use_mmap variable he mentions isn't the solution).

If you are on Mysql 8.0.2x releases below 8.0.27 and experiencing this issue, then the temporary fix for this is:

set global internal_tmp_mem_storage_engine=MEMORY

This switches the implementation from the new TempTable back to Memory which is what was used in the 5.7 series.

The new TempTable implementation is a great change but has been very buggy. The sheer number of bugs being fixed every release on the 8.0.x series in general is pretty disturbing.

StackExchange Database Administrators Q#287030, answer score: 7