View MySQL Temporary Table - Not in session

mysql temp-tables
Drahcir picture Drahcir · Nov 29, 2011 · Viewed 7k times · Source

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

James C picture James C · Nov 29, 2011

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.

Related questions

Create a temporary table in a SELECT statement without a separate CREATE TABLE

Is it possible to create a temporary (session only) table from a select statement without using a create table statement and specifying each column type? I know derived tables are capable of this, but those are super-temporary (statement-only) and I …

Read More
How can I simulate an array variable in MySQL?

It appears that MySQL doesn't have array variables. What should I use instead? There seem to be two alternatives suggested: A set-type scalar and temporary tables. The question I linked to suggests the former. But is it good practice to …

Read More
Create a temporary table in MySQL with an index from a select

I have a stored function where I use temporary tables. For performance reasons, I need an index in that table. Unfortunately, I cannot use ALTER TABLE because this causes an implicit commit. Therefore I'm looking for the syntax to add …

Read More