MySQL thread states when changing an in-memory temporary table to disk-based In High Performance MySQL (3rd edition) they state that:


  You can't tell whether a table was created in memory and then converted to on-disk or just created on-disk to begin with...


What I was wondering though is -

At the point where (the lower of) tmp_table_size or max_heap_table_size is exceeded will a new query (thread_id) be created that will be the same query but with thread state of Copying to tmp table on disk instead of Copying to tmp table?

During some recent debugging we noticed a second query get created right around the 300 second mark but both were of thread state Copying to tmp table. But occasionally we'd see the second query with thread state Copying to tmp table on disk.

The query is the result of a report that only a particular role can request and we instructed those people not to request the report so we are fairly confident that it is not user-initiated. 

We also looked at timeouts in MySQL and our application but nothing seems to align with the 300 seconds (there is a MySQL delayed_insert_timeout that defaults to 300 seconds but we changed that value and the second query/thread still was created at 300 seconds).
At the point where (the lower of) tmp_table_size or max_heap_table_size is exceeded will a new query (thread_id) be created that will be the same query but with thread state of Copying to tmp table on disk instead of Copying to tmp table?


This will never happen.

The "same" query can't ever create a new thread id.  Nothing happening in a client thread will create a new thread id.  If it looks like you are seeing that, then it has to be something other than that... something else is connecting and running that query again.

Perhaps the MySQL event scheduler?  Perhaps a timeout at some layer in the application framework?  It seems all but impossible that this is anything other than something (or someone) sending the query again on a new connection.

You should be able to confirm this with the general query log, but it isn't spontaneously MySQL doing this.

Also, going for the quote into its full context, from High Performance MySQL:


  You can't tell whether a table was created in memory and then converted to on-disk or just created on-disk to begin with...


The point is that you can't tell from SHOW STATUS counters whether temporary tables were switched from memory to disk mid-query because of growth in the result-set or initially created as disk-based temporary tables because of some limitation of in-memory tables (like the lack of BLOB support) from looking at the counters.  You can tell when a table is being converted from one to the other because it should say "converting HEAP to MyISAM" and obviously, as you have seen, the processlist does indicate which type of table an implicit temporary table currently is. 

But it's inconceivable that you have anything other than another query being sent to the MySQL server from somewhere, if you're seeing what looks like a query forking into or spilling over into another client thread.