Fun MySQL fact of the day: really temporary files

Before finishing the last 2 Fun MySQL Facts Of The Day about sorting, we're going to take a quick detour on what I considered a really fun fact about filesort. Or, well, more specifically, about MySQL's handling of temporary files (of type IO_CACHE).

Yesterday, when I'd mentioned I had placed "some breakpoints in some non-obvious places" to help understand how filesort was working, the breakpoints where on real_open_cached_file in mysys/mf_cache.c (mysys/mf_cache.cc in MySQL 8.0), which maps an IO_CACHE struct to an on-disk temporary file. For context, the filesort function creates 3 temporary files when sorting more than sort_buffer_size bytes worth of rows. If you step into real_open_cached_file, the next meaningful call is to mysql_file_create_temp to create a temporary file starting with MY in tmpdir (for example, /var/lib/mysql/tmp/MY3w3t0r). After this call, you can see the file on the disk by running ls or even tail it.

But then if you're not careful you may just continue execution like me, only to find out the temporary file vanishes immediately. It was there, and now it's not. Even though it should still be present. Even though it's still in use. But then again, it's not surprising at all when you notice that the next line of code after mysql_file_create_temp is cache_remove_open_tmp, which deletes the file while leaving the file descriptor open by using unlink(2).

This is pretty fun: MySQL is able to read/write the file using the file descriptor it has retained, but once the file descriptor is closed, the file ceases to exist at all. Why might you want this behaviour? Well, if MySQL crashes, for example, these temporary files don't need to be cleaned-up: they just become free space. All at the cost of me thinking I've lost my mind. Fun.