View MySQL Temporary Table - Not in session

Question

I currently have a script running and didn't think it would take so long to run, the script is modifying a temporary table.

I know that temporary tables only exist for the current session, but is there anyway to see the data they hold from outside the session?

Reason is that I want to know how long my script is going to keep running for, if I could see the temporary data then I'd be able to figure it out.

Answer 1

There's no easy way of doing this I'm afraid.

Temporary tables will be stored in your mysqld's designated temp directory (usually /tmp) and you'll see a set of tables something like:

-rw-rw---- 1 mysql mysql     8724 Nov 29 18:09 #sqldba_5fa70c_12f1.frm
-rw-rw---- 1 mysql mysql   188408 Nov 29 18:09 #sqldba_5fa70c_12f1.MYD
-rw-rw---- 1 mysql mysql     1024 Nov 29 18:09 #sqldba_5fa70c_12f1.MYI

That's a normal set of MyISAM tables defining (in order above) structure, data and index.

This is horribly hacky but I suspect you could copy these tables out into say your test schema, run a repair on the table and then view it's contents.

If you can gauge the process by the size of the temp table then that could well be a simpler way of analysing what's going on.

Answer 2

Is not possible, you can (however),
log milestone (message, % of completion, number rows processed)
into a temporary file, and use the tail -f log_file to monitor