Temporary table is full

Question

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

Answer 1

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.

Answer 2

When an InnoDB table is full, it has nothing to do with the OS Diskspace.

InnoDB views a table as being full in terms of the storage engine itself.

There are two circumstances under which this can happen

innodb_file_per_table off

I once had a client that had 2TB ibdata1/ibdata2. The client was unable to write any more transactions although ibdata file had 106GB of fragmentation. The solution was to give it more space.

innodb_file_per_table on

When InnoDB tables are stored .ibd files, the table can become full when the undo log gets full

See my old posts going back 10 years on this topic

• Feb 22, 2018 : The table is full - MariaDB
• Jun 16, 2014 : MySQL Index creation failing on table is full
• Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
• May 31, 2013 : `ERROR 1114 (HY000) the table ... is full` with innodb_file_per_table set to autoextend
• Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

IMHO this should fully explain a full InnoDB table

Answer 3

Firstly, identify which query is using such a large amount of space. What is the size of the dataset which is returned from that query?

Try to optimize the query causing the bottleneck, i.e. using 100GB. Check the explain plan & your indexing. Because this might cause an OUT OF MEMORY issue.

You can change the paths of the parameters below to specific partitions which have sufficient space. These are related to temporary table creation.

innodb_tmpdir
slave_load_tmpdir
tmpdir

Answer 4

This sounds a lot like GROUP BY will throw table is full when temptable memory allocation exceed limit (MySQL Bugs):

Description: Running a query that is using a temporary table for GROUP BY will throw "the table is XXXXX full" .

mysql> select count(*) cnt, pad, c from sbtest1 group by pad order by cnt desc  limit 0, 10;  

ERROR 1114 (HY000): The table '/data/mysql5858/tmp/#sql639b7_13_4' is ful

Is there any chance that you have disabled temptable_use_mmap?

Answer 5

You have to change tmpdir and innodb_tmpdir options to point some other directory instead of tmp. Usually that partition is relatively small and can be exhaused easily on a huge joins.