A TEMPORARY table is a table that is bound to a connection and is dropped when that connection is closed. It seems like this would just be a normal table with some meta data attached to it indicating the connection it is bound to. I suppose for some reason it is more complicated than that… seems unnecessarily so…

Anyways, the limitation is that you cannot refer to a given temporary table more than once in a single query.  If you do you will get a “Can’t reopen temporary table” error. So, for instance you cannot insert into a temporary table some data that you select from it in in the same query. This limitation has bit me several times.

The bug is here: http://bugs.mysql.com/bug.php?id=10327

I really just wanted to draw attention to this because its been open for over 3 years!

Because of this limitation I have to use a normal table in a few of my stored procedures which means that I have to restrict them from running at the same time due to possible collisions across connections. If I could use a temporary table, I would be so happy.

I thought about using real tables with the connectionId appended to the end but I have about 20 stored procedures with most of them over 100 lines long. Doing complex prepared statements in SQL looks TERRIBLE and is really difficult to read and subsequently update later.

I eneded up just restricting these stored procedures to executing 1 at a time. This worked out fine for my purposes although occasionally I will have “temp” tables that don’t get properly dropped. This can happen if an admin restarts the application tier and the stored procedure doesn’t get a chance to clean up after itself. For those interested in cleanup, check out how Xaprb did his automated cleanup.