2

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
8
  • 100Gb free in /mnt/disks/data/tmp? Check with du
    – Rick James
    Mar 15, 2021 at 17:53
  • 1
    A typical cause is accidentally doing a "cross join" by forgetting to have a suitable ON clause when JOINing. We must see the query and SHOW CREATE TABLE.
    – Rick James
    Mar 15, 2021 at 17:54
  • Please provide the output of df -h on your system. Also, please provide SHOW CREATE TABLE\G for all of the query's tables and also the query SQL itself. Mar 15, 2021 at 19:07
  • I have a very important questions for you: When you got The table '/mnt/disks/data/tmp/#sql58de_a0f6_7' is full, was the disk full also ??? Mar 15, 2021 at 19:54
  • Another very important question : Is the storage engine of the temp table MyISAM or InnoDB ??? . The file extension will either be .ibd or .MYD Mar 15, 2021 at 20:00

5 Answers 5

4

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.

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

IMHO this should fully explain a full InnoDB table

1

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
4
  • 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, right?
    – pierot
    Mar 15, 2021 at 14:46
  • I know the query is heavy. I'm trying to figure out why it returns this error, and what can be done. Because I don't see, system-wise, any clues as to why it returns .. is full.
    – pierot
    Mar 15, 2021 at 14:48
  • @JYOTIRAJAI - I hope the edits are in line with what you had in mind - please feel free to reverse! Mar 15, 2021 at 19:08
  • @Vérace Yes they are in line. Mar 16, 2021 at 4:24
1

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?

3
  • No idea why you got downvoted. His issue is being caused by this bug. Mysql's 8.0 TempTable functionality is a work in progress and we're all the guinea pigs. They are claiming it's fixed in 8.0.27 but I don't believe it. Dec 6, 2021 at 16:42
  • @MatthewLenz I am not seeing any downvotes on this post. It is showing that this was the only activity. We have been testing 8.0.27 since the 12 Nov 2021 and we have not seen this issue since then, so it does seem like this particular issue is now fixed in 8.0.27.
    – Yoseph
    Dec 8, 2021 at 0:10
  • My reddit mentality. I was thinking comments start at 1 point :). That's great news about .27. Unfortunately we run Percona's mysql distribution and they always lag behind awhile due to all the additional features they have to port. Dec 9, 2021 at 13:10
0

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.

1
  • tmpdir is 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, right?
    – pierot
    Mar 15, 2021 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.