CODEWITHSUNDEEP
mysqltemp-tables
TravisVOX
TravisVOX

Reputation: 21631

Calling stored procedures on temporary tables

Is it possible to execute stored procedures on temporary tables in MySQL? I'm trying to create a system for data import that could, theoretically, run concurrently for different users. If one user is importing files into a temporary table while another user is doing the same thing, is it possible for the same shared procedure to be called by both users since the tables referenced in the procedure will match the temporary tables?

The workflow for an individual user would look like this...

  1. Load data into temporary table newdata
  2. Stored procedure is called where munging and updates are done to table newdata
  3. Stored procedure moves data from newdata to the live/permanent tables.

...while another user could, possibly, be doing the same thing.

Upvotes: 1

Views: 438

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

Yes, you can reference temp tables in a stored procedure:

mysql> create procedure p() select * from t;
Query OK, 0 rows affected (0.03 sec)

mysql> create temporary table t as select 123 union select 456;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> call p;
+-----+
| 123 |
+-----+
| 123 |
| 456 |
+-----+

(Tested on MySQL 5.6.31)

However, most experienced MySQL users try to avoid doing what you're planning, if they use replication. The reason is that when a slave restarts for any reason, it drops any temp tables. So any subsequent UPDATE and INSERT...SELECT referencing the temp table get an error because the temp table no longer exists. An error in the replication stream stops replication.

It might seem like this should be an uncommon occurrence for a slave to restart suddenly, but if your app creates temp tables frequently, there's a high chance for there to be a temp table going at the moment a slave restarts.

The best use of temp tables is to fill them with temp data in one statement, and then use the temp table only by SELECT queries (which are not replicated).

Upvotes: 1

Related Questions