[MySQL 8.0] The table '/tmp/#sql1_f519f_7' is full

Hi there! I’ve updated my Percona MySQL Server from 8.0.22-13 to 8.0.23-14 and faced with a problem.

I have a big complicated query (with UNION, GROUP BY, ORDER BY and a lot of JOINs). With the previous version that query worked just fine, but after update it fails everytime:
(HY000/1114): The table '/tmp/#sql1_f519f_7' is full

At this point you might think “there’s just not enough disk space”, but it’s wrong - 481Gb is available. And I’ve tested my DB on 3 different hosts. So the problem definitely not in disk space.

Basically, I found the reason why the query doesn’t work with a new version, it’s because of new MySQL option - temptable_max_mmap.
As I understand my query requires to allocate more than temptable_max_ram memory (there’s a default value - 1Gb), after that MySQL checks temptable_use_mmap option and if it’s enabled then allocating space from disk in the form memory-mapped temporary files. And it was working well for me with the prev. version. But MySQL 8.0.23 brought a new option temptable_max_mmap (default value is 1Gb too). As I understand now this option sets the limit for memory-mapped temp files and my query started reach it. In my theory after that MySQL tries to use InnoDB on-disk internal temporary tables and in this step just fails.
So the new MySQL version just showed me a real problem: my instance can’t use InnoDB on-disk internal temporary tables.

There I’ve found that temptable_use_mmap is deprecated and will be removed in a future version of MySQL. That means I need to figure out why my instance can’t write any data to InnoDB on-disk internal temporary tables.

I’ve tried a few configs (with Docker and without), in both cases tmpdir is /tmp. I’ve checked that this path is writable for MySQL.
What else I can do?

Will be glad for any help.

1 Like

Hi asamofal,

New MySQL 8 temporary engine TempTable have shown various bugs and regressions in the early versions of MySQL 8.

What you can try doing is setting:
set global internal_tmp_mem_storage_engine=Memory;

and stop using TempTable in favor of previous memory storage engine used by MySQL before MySQL 8.

There are various bug reports that suggest doing this change such as:
https://bugs.mysql.com/bug.php?id=98782
https://bugs.mysql.com/bug.php?id=98739
https://bugs.mysql.com/bug.php?id=99593
https://bugs.mysql.com/bug.php?id=99100

Specifically the latter one resembles the issue you are currently facing. Give it a try and let us know if that works for you as a workaround while TempTable gets to a more stable version.

Regards

2 Likes

Okay, I’ve got it.
Changing internal_tmp_mem_storage_engine solved the problem. So yes, seems like it’s just a bug with TempTable engine. Will stay with Memory engine for now and will follow the changelogs :slight_smile:

CTutte, thanks for your attention.

1 Like