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.
