4

I'm getting this error when running a query:

[ERROR] [MY-013132] [Server] The table '/tmp/#sql1127b_9_0' is full!

I go into the /tmp directory but I can't see the table, I'm presuming it gets deleted when the query has finished running.

So I go into the directory when the query is running (the query takes a few minutes to run - against 40 million records). I don't see the table. I refresh the directory, I still don't see it. How can I see it? I want to see it and establish its location so I can figure out why it's filling up when I have lots of free diskspace.

I'm using MySQL 8.0.23 - I previously used MySQL 5.7.33 on the same machine with the same database and same query, never got a problem.

This is my diskspace:

Filesystem      Size  Used Avail Use% Mounted on
tmpfs           3.2G  3.7M  3.2G   1% /run
/dev/sdc3       215G   38G  167G  19% /
tmpfs            16G     0   16G   0% /dev/shm
tmpfs           5.0M  4.0K  5.0M   1% /run/lock
tmpfs           4.0M     0  4.0M   0% /sys/fs/cgroup
/dev/sdb6       107G   81G   21G  80% /media/Kingston_SSD_120GB
/dev/sda        459G  335G  101G  77% /media/Hitachi
/dev/sdc2        33M  7.8M   25M  24% /boot/efi
tmpfs           3.2G  120K  3.2G   1% /run/user/1001

I didn't set any diskspace for /tmp - as can be seen, I have 167GB free space on /.

Ben
  • 16,275
  • 9
  • 45
  • 63
Charlie Coder
  • 43
  • 1
  • 5

2 Answers2

2

MySQL uses a trick that has been part of POSIX systems forever. It opens the temp file, and immediately unlinks it. Therefore it's not viewable in any directory listing. But POSIX systems like UNIX and Linux shouldn't actually remove an unlinked file while a process has an open file handle to it. So once the query using the temp table finishes, it will close the file handle, and then the OS will automatically remove the file and free the storage it was using.

This is generally better than requiring the server code remember to remove the tempfile when it's done with it. It also accounts for like the thread terminating, or mysqld crashing. At least it won't leave stale temp files littering your filesystem.

You can view the size of unlinked files with lsof -s. I'll leave it to you to look up examples of how to use that command (Google is your friend here).

It's thinly possible that a temp file uses up your 167GB of free space.

Or it could be that the temp file only uses 8GB, but you may have 20 threads doing the same query at the same time. I have seen that happen once.

But it's probably more likely that you have a value of tmp_table_size that is constraining the size of the temp table.

If you hit the limit, you can raise that configuration option, either as a session variable when you need it, or globally in the my.cnf.

But I would first try to optimize the query. Why does it need to create such large temp tables? Could it be optimized to examine fewer rows, or perhaps avoid creating a temp table altogether?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Wonderful information, Bill, thank you! That certainly explains the invisible file! I'll have a go at your suggestions and get back to you. I'm not an expert on MySQL, so the query might not be as good as it can be, and it's querying 40 million records/rows, from a PHP script that holds a number of (big) values in variables, all hand-knitted by myself! Just a bit puzzled why it worked perfectly before with the old MySQL. – Charlie Coder Feb 12 '21 at 16:35
  • Each version of MySQL makes changes to its optimizer code, and there are bound to be edge case queries that don't use indexes in the new version even if they did use an index in the older version. You may need to re-analyze query optimization when you upgrade by a major version. – Bill Karwin Feb 12 '21 at 16:41
  • Thanks again, Bill. I have accepted this answer as it answers my original question about the whereabouts of the temp file. I increased tmp_table_size and max_heap_table_size to a value greater than the available size on the disk, after trying other increases without success. Still getting the table full error, but you have given me good pointers. – Charlie Coder Feb 12 '21 at 17:31
  • You should not make max_heap_table_size larger than the amount of RAM you want it to use. – Bill Karwin Feb 12 '21 at 17:51
0

from the official documentation:

On Unix, MySQL uses the value of the TMPDIR environment variable as the path name of the directory in which to store temporary files. If TMPDIR is not set, MySQL uses the system default, which is usually /tmp, /var/tmp, or /usr/tmp.

so if you didnt configure TMPDIR, then your file is needed to be under /tmp, /var/tmp, or /usr/tmp

IF you are getting this error instantly (within a second or two), I suspect that this is a permission issue. But otherwise, It looks like the temporary table really filling the disk.

EDIT: try looking for the file while executing the query. Because The file is getting deleted after the SQL error

Also this post can help you

How can I limit the size of temporary tables?

Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72
  • Thanks again Derviş. I ran mysql> SHOW VARIABLES LIKE 'tmpdir'; Got: | tmpdir | /tmp which is in agreement with the error I got. I tried your suggestion of looking in other directories but there was nothing there. MySQL is telling me the temp directory is /tmp - the error is telling me the temp directory is /tmp yet I can't find the table file in there. I will look into the size of temp tables but everything worked perfectly well on MySQL 5 - only getting this error since upgrading to MySQL 8, so reluctant to do extra work on something that worked perfectly well before. – Charlie Coder Feb 12 '21 at 14:04
  • Also tried searching the whole filesystem for '#sql' during the few minutes while the query was running with find / -name '#sql*' got nothing. – Charlie Coder Feb 12 '21 at 14:10