Temporary table error

Question

Hello Experts,

We are trying to implement a business logic in aurora mysql procedure. In this, here are many occasions in which we have to park some intermediate result set fetched from transaction tables and use those result set in multiple queries in the procedure. And this procedure can be called simultaneously from multiple users/sessions. So we were planning to use Temporary table to populate/hold the intermediate results onetime which would be optimal and then use it throughout the procedure whenever required, but we are getting error "****Can't Reopen table****" while using the temporary table more than once in subsequent queries once it's being populated. Want to understand from experts, how to get rid of such scenario?

Saw few blogs, which suggests using permanent table to avoid this error, but that is seems not feasible solution as we need to call the same procedure from multiple sessions so we may need to end up creating multiple tables then for each session/user which is not good.

Also thinking of using CTE/"common table expression" or using subqueries, but that means the same CTE/Subqueries needs to be used many times throughout the procedure which would be performance hit as that means the main transaction table will be hit those many times.

Can you please guide, what is the optimal way to handle this scenario?

I tried replicating the scenario, so below is the sample code which is failing with same error (can't reopen table) for both the "Update" and "Insert" statement in the procedure.

```
CREATE TABLE employees (
   ID INTEGER,
   Name VARCHAR(100)
);

insert into employees values(1, 'A');
insert into employees values(2, 'B');
insert into employees values(3, 'C');
insert into employees values(1, 'A');
insert into employees values(2, 'B' );

select * from employees;

CREATE TABLE employees_backup (
   ID INTEGER ,
   Name VARCHAR(100)
);

CREATE PROCEDURE ExampleProc(OUT v_Proc_status INT, OUT vResult VARCHAR(4000) )
BEGIN
       DECLARE vID INTEGER;
       DECLARE vName VARCHAR(20);
	  
	CREATE temporary TABLE IF NOT EXISTS employees_backup1  as  (select * from employees);

Insert INTO employees_backup
   SELECt eb1.* 
   from employees_backup1 eb1 , employees_backup1 eb2
    where eb1.id= eb2.id;

Update employees_backup eb, employees_backup1 eb1 ,  employees_backup1 eb2
    set eb.name= eb1.name
      where eb.id= eb1.id
     and eb1.is= eb2.id;

select count(*) into vResult from    employees_backup1;

SET v_Proc_status=1;
  END;
```

Answer

Hi there.

I find the use of a temporary table in this manner as an anti-pattern. You would use a temporary table, "temporary", meaning, you create it, use it, and dispose it. What I mean by all this is that the way this temporary table is being used in the procedure is no different than using a permanent table.

I'm not sure what do you mean when you say:

> Saw few blogs, which suggests using permanent table to avoid this error, but that is seems not feasible solution as we need to call the same procedure from multiple sessions so we may need to end up creating multiple tables then for each session/user which is not good.

Why is no good? You wouldn't create a table multiple times, just create it once (but not inside the the procedure), and then use it in your procedure.

Also, what is the exact error you are getting? the full message, line number, etc. I'm not clear where in the code is the error thrown.

Thanks.

Answer

Thank you @Jose Guay

Below is simple test case of how this error getting reproduced.

But the key issue is, when wanted to use this in a broader perspective i.e., populate this temporary table once and utilize the results in multiple queries subsequently, it doesn't work if referring to it twice.

And as you rightly said, we should create temporary table outside the procedure and just use it and truncate it after the work in the session. So how should we handle a requirement in which similar procedure/business logic is getting called simultaneously from multiple session/users. as because creating multiple tables each time with different name is not a good idea. And at the same time calling the same business logic using subquery or CTE in the queries multiple times in the procedure (without storing this intermediate result set in temporary/permanent table) means performance hit. Correct me if wrong.

Considering this situation what would be the optimal suggested way to handle such type of business logic in the procedure?

CREATE TABLE employees (
   ID INTEGER,
   Name VARCHAR(100)
);

insert into employees values(1, 'A');
insert into employees values(2, 'B');

CREATE temporary TABLE IF NOT EXISTS employees_backup1  as  (select * from employees);

****Below statement works fine without any error****

select * from employees eb1, employees_backup1 eb2
where eb1.id= eb2.id;

ID	Name	ID	Name
1	A	1	A
2	B	2	B

****Below statement errors out****
 
select * from employees_backup1 eb1, employees_backup1 eb2
where eb1.id= eb2.id;

Can't reopen table: 'eb1'

Temporary table error

Relevant content