I would like to share this story based on a true event about the temporary files behavior in MySQL.

MONyog reports this error to my already full mailbox several times a day :

1 – Catch the query (if you can) !

I don’t have access to the client logs but I would like to know which query is involved in this error.

Let me explain how I can retrieve informations about this query with MONyog and a very simple shell loop :

•  Enable the query sniffer in MONyog (based on processlist) : Edit server -> Advanced settings -> Sniffer settings

…

Even when the output of EXPLAIN doesn’t show “using temporary”, a temporary file may still be used in certain cases.

That’s not to say the query needs the temporary file to actually resolve the query (like what you’d see from the need for a derived table). But rather, the temporary file I’m speaking of is due to binary logging.

In particular, you can see this easily if using InnoDB, (most commonly) row-based binary logging, and you issue a large transaction, say a large UPDATE (large meaning something larger than the size of binlog_cache_size). In this case, you’ll notice a temporary file being …