CODEWITHSUNDEEP
mysqltemp-tables
Drahcir
Drahcir

Reputation: 11972

View MySQL Temporary Table - Not in session

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.

Upvotes: 8

Views: 7793

Answers (3)

ggiroux
ggiroux

Reputation: 6724

In MySQL 5.7 a new INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO has been added: http://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-temp-table-info.html.

Otherwise Percona XtraDB has had this feature for a little while: http://venublog.com/2010/02/03/show-temporary-tables/

Upvotes: 0

ajreal
ajreal

Reputation: 47321

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

Upvotes: 1

James C
James C

Reputation: 14149

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.

Upvotes: 8

Related Questions