Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using a recursive stored procedure in MySQL to generate a temporary table called id_list, but I must use the results of that procedure in a follow up select query, so I can't DROP the temporary table within the procedure...

BEGIN;

/* generates the temporary table of ID's */
CALL fetch_inheritance_groups('abc123',0);

/* uses the results of the SPROC in the WHERE */
SELECT a.User_ID
FROM usr_relationships r 
INNER JOIN usr_accts a ON a.User_ID = r.User_ID 
WHERE r.Group_ID = 'abc123' OR r.Group_ID IN (SELECT * FROM id_list) 
GROUP BY r.User_ID;

COMMIT;

When calling the procedure, the first value is the top ID of the branch I want, and the second is the tier which the procedure uses during recursions. Prior to the recursive loop it checks if tier = 0 and if it is it runs:

DROP TEMPORARY TABLE IF EXISTS id_list;
CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;

So my question is: If I don't DROP the temporary MEMORY table at the end of the procedure, or within my transaction, how long will that table persist in memory? Is it automatically dropped once the session ends, or will it remain in memory as long as the connection is open?

*N.B. The obvious answer might be to drop the temp table prior to the commit statement, but lets assume for a moment that I can't do that.


EDIT: To be a little more precise, what if persistent connections are employed, will the table persist through multiple requests? So far it seems that it will and that we would need to explicitly remove the temp table to free up that resource.


UPDATE: Based on the advice from the commenters, I've found a way of adjusting my SPROC so that I can utilize the TEMP MEMORY table, but be able to explicitly DROP it at the end...

Rather than just calling the SPROC and using the remaining TEMP table to gather the results in the actual query, I've changed the CALL format to use a third OUT variable like so:

CALL fetch_inheritance_groups('abc123','0',@IDS);

... then within the SPROC, I added a second IF tier = 0 at the very end with the following:

IF tier = 0
    THEN
    SELECT GROUP_CONCAT(DISTINCT iid SEPARATOR ',') FROM id_list INTO inherited_set;
    DROP TEMPORARY TABLE IF EXISTS id_list;
END IF;

So the result of the SPROC is now a comma separated list of ID's that is compatible with FIND_IN_SET, and so the final query was modified so that:

WHERE r.Group_ID = 'abc123' OR r.Group_ID IN (SELECT * FROM id_list)

... is now ...

WHERE r.Group_ID = 'abc123' OR FIND_IN_SET(r.Group_ID,@IDS)

Voila! Thanks to the commentors for your input, and for giving me the reason I needed to try a little harder :)

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

What is funny about temporary tables in a stored procedure is not so much the transient existence of the table (which gets dropped upon the DB connection's termination), but the scope of the stored procedure.

Someone asked this question on StackOverflow : Scope of temp tables created in MySQL stored procedure. It has been over a year and nobody answered the question? Let me set the record straight. The fact is: The temp table exists inside and outside of the Stored Procedure, but you can do things with the temporary table only inside the scope of a running Stored Procedure.

According to the Book

kdsjx

Chapter 5 has a subheading Returning Result Sets to Another Stored Procedure.

It says in paragraph 2 on Page 117:

Unfortunately, the only way to pass a result set from one stored procedure to another is to pass the results via a temporary table. This is an awkward solution b, and -- because the temporary table has scope throughout the entire session -- it creates many of the same maintainability issues raised by the use of global variables. but if one stored program needs to supply another stored program with results, then a temporary table can be the best solution.

Looking back at the StackOverflow question, I can see someone called the Stored Procedure from the mysql client. Since the mysql client is not a Stored Procedure, the results cannot be manipulated the mysql client level via DML other than doing a SELECT to see the results. Since you calling a recursive stored procedure, you can rest assured the temp table is fully accessible for the duration of the DB Connection.

I hope this answers your question.

UPDATE 2014-01-31 11:26 EST

In your last comment, you said

If we employ persistent connections, will the MEMORY table persist through multiple REQUESTS, and it seems it will, so for performance sake, I'm assuming that using this method will *REQUIRE us to explicitly DROP the temporary MEMORY table. Do I assume correctly?

Yes and No. I say Yes because it is one way to do it. I say no because another way to do it is:

CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;
TRUNCATE TABLE id_list;

Whichever way you choose, the operation is still the same since TRUNCATE TABLE drops and recreates the table. This will not harm other DB Connections since each Connection has its own id_list table.

share|improve this answer
    
VERY much appreciated Rolando! I posted the same question on SO (stackoverflow.com/questions/21483448/…) just in case it got more eyes on it, and I got similar, albeit less informative, answers. I posed a follow-up: If we employ persistent connections, will the MEMORY table persist through multiple REQUESTS, and it seems it will, so for performance sake, I'm assuming that using this method will *REQUIRE us to explicitly DROP the temporary MEMORY table. Do I assume correctly? –  oucil Jan 31 at 16:20
    
With relation to your UPDATE, I guess I'm more concerned with leaving a resource in place that is no longer necessary until that query is run again, and I think it's becoming more obvious that I should explicitly remove it regardless of whether I don't need to. –  oucil Jan 31 at 16:29
    
Thank you for accepting !!! –  RolandoMySQLDBA Jan 31 at 16:36
add comment

In most DBMSs temporary tables survive until the end of the current connection unless otherwise specified or unless there is an explicit transaction rollback (in some systems a rollback may only affect the content of the table, leaving the object itself around to be repopulated if needed). The table will not (by default) be visible to other connections no matter how long the connection that creates it lasts.

A quick scan on Google seems to indicate this is how mySQL operates.
(http://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm states "by default, all the temporary tables are deleted by MySQL when your database connection gets terminatedBy default, all the temporary tables are deleted by MySQL when your database connection gets terminated")

There are often ways to alter these behaviours though. For instance in MS SQL Server you can create a temporary table that is visible to all connections instead of just the current one by giving it a name starting ##.

I always drop temporary tables as soon as they are no longer needed to avoid possible confusion. I have been bitten before where connection pooling resulted in temporary table creation causing errors because a temporary table of the same name had been created but not destroyed in a previous action that used the current connection.

share|improve this answer
    
I agree that I should find a way to explicitly drop the table, but I get around the issue you finished with by using a DROP prior to recreating within the initial tier's IF. Thanks for your input! –  oucil Jan 31 at 16:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.