Getting around MySQL "Can't reopen table" error

Question

I'm currently busy implementing a filter of sorts for which I need to generate an INNER JOIN clausse for every "tag" to filter on.

The problem is that after a whole bunch of SQL, I have a table that contains all the information I need to make my selection, but I need it again for every generated INNER JOIN

This basically looks like:

SELECT
    *
FROM search
INNER JOIN search f1 ON f1.baseID = search.baseID AND f1.condition = condition1
INNER JOIN search f2 ON f2.baseID = search.baseID AND f2.condition = condition2
...
INNER JOIN search fN ON fN.baseID = search.baseID AND fN.condition = conditionN

This works but I would much prefer the "search" table to be temporary (it can be several orders of magnitude smaller if it isn't a normal table) but that gives me a very annoying error: Can't reopen table

Some research leads me to this bug report but the folks over at MySQL don't seem to care that such a basic feature (using a table more than once) does not work with temporary tables. I'm running into a lot of scalability problems with this issue.

Is there any viable workaround that does not require me to manage potentially lots of temporary but very real tables or make me maintain a huge table with all the data in it?

Kind regards, Kris

[additional]

The GROUP_CONCAT answer does not work in my situation because my conditions are multiple columns in specific order, it would make ORs out of what I need to be ANDs. However, It did help me solve an earlier problem so now the table, temp or not, is no longer required. We were just thinking too generic for our problem. The entire application of filters has now been brought back from around a minute to well under a quarter of a second.

I had the same problem using a temporary table twice in the same query using UNION. — Sebastián Grignoli, Oct 21 '10 at 17:11

score 150 · Answer 1 · edited Apr 13 '17 at 16:08

150

A simple solution is to duplicate the temporary table. Works well if the table is relatively small, which is often the case with temporary tables.

edited Apr 13 '17 at 16:08

Kevin Kalitowski

6,829
4
36
52

answered Jan 28 '13 at 10:27

Pete

1,501
2
9
2

9

Should actually be the chosen answer as this answers the problem, without going around. – dyesdyes Aug 25 '14 at 16:32
6

any advice on *how* would you duplicate the table? (I mean a way of copying not repeating the query) – Hernán Eche Mar 02 '16 at 12:40
22

Even if the temp table is large, mysql's cache should help you out. As far as copying from one temp table to another, a simple "CREATE TEMPORARY TABLE tmp2 SELECT * FROM tmp1" should do it. – AS7K May 10 '16 at 13:57
1

this should be the answer. it took me 1min to make the workaround, against 1h in trying the other selected answer – Sebastien H. Mar 03 '17 at 08:42
3

If you copy the temptable content, don't forget to create indexes as well, otwerwise your query may be quite slow. – gaborsch Apr 29 '17 at 19:52
If the temp table is created from a plain union (joint of 2 tables, no row calculations or special filters involved), does it worth to dupicate the temp table or better to use a view? – Gabriel Mar 08 '19 at 16:19
Anybody use this solution on relatively big temp table? Like > 10,000 records? Will it be very slow? – Ng Sek Long Aug 07 '19 at 07:01
2

@NgSekLong Yes. All the time. It obviously depends on your application for the query but I don't see "huge" performance issues until > 100,000. In one ETL process, I use this method with a 3.5mil table. That application's speed isn't as important though. – Tanner Clark Jan 27 '20 at 19:18
@TannerClark Yup me too, after posting that comment I been duplicating temp table for >100,000 records table with no problem observed. – Ng Sek Long Jan 28 '20 at 14:34

Bill Karwin · Answer 2 · 2008-12-07T06:08:48.977

53

Right, the MySQL docs say: "You cannot refer to a TEMPORARY table more than once in the same query."

Here's an alternative query that should find the same rows, although all the conditions of matching rows won't be in separate columns, they'll be in a comma-separated list.

SELECT f1.baseID, GROUP_CONCAT(f1.condition)
FROM search f1
WHERE f1.condition IN (<condition1>, <condition2>, ... <conditionN>)
GROUP BY f1.baseID
HAVING COUNT(*) = <N>;

edited Dec 07 '08 at 06:08

answered Dec 07 '08 at 06:03

Bill Karwin

538,548
86
673
828

2

This didn't actually solve my problem at hand, but it did enable me to simplify the problem that caused it, thus negating the need for the temptable. Thanks! – Kris Dec 08 '08 at 09:53

score 9 · Answer 3 · answered May 03 '14 at 06:30

I got around this by creating a permanent "temporary" table and suffixing the SPID (sorry, i'm from SQL Server land) to the table name, to make a unique table name. Then creating dynamic SQL statements to create the queries. If anything bad happens, the table will be dropped and recreated.

I'm hoping for a better option. C'mon, MySQL Devs. The 'bug'/'feature request' has been open since 2008! Seems like all the 'bugs' 've encountered are in the same boat.

select concat('ReviewLatency', CONNECTION_ID()) into @tablename;

#Drop "temporary" table if it exists
set @dsql=concat('drop table if exists ', @tablename, ';');
PREPARE QUERY1 FROM @dsql;
EXECUTE QUERY1;
DEALLOCATE PREPARE QUERY1;

#Due to MySQL bug not allowing multiple queries in DSQL, we have to break it up...
#Also due to MySQL bug, you cannot join a temporary table to itself,
#so we create a real table, but append the SPID to it for uniqueness.
set @dsql=concat('
create table ', @tablename, ' (
    `EventUID` int(11) not null,
    `EventTimestamp` datetime not null,
    `HasAudit` bit not null,
    `GroupName` varchar(255) not null,
    `UserID` int(11) not null,
    `EventAuditUID` int(11) null,
    `ReviewerName` varchar(255) null,
    index `tmp_', @tablename, '_EventUID` (`EventUID` asc),
    index `tmp_', @tablename, '_EventAuditUID` (`EventAuditUID` asc),
    index `tmp_', @tablename, '_EventUID_EventTimestamp` (`EventUID`, `EventTimestamp`)
) ENGINE=MEMORY;');
PREPARE QUERY2 FROM @dsql;
EXECUTE QUERY2;
DEALLOCATE PREPARE QUERY2;

#Insert into the "temporary" table
set @dsql=concat('
insert into ', @tablename, ' 
select e.EventUID, e.EventTimestamp, e.HasAudit, gn.GroupName, epi.UserID, eai.EventUID as `EventAuditUID`
    , concat(concat(concat(max(concat('' '', ui.UserPropertyValue)), '' (''), ut.UserName), '')'') as `ReviewerName`
from EventCore e
    inner join EventParticipantInformation epi on e.EventUID = epi.EventUID and epi.TypeClass=''FROM''
    inner join UserGroupRelation ugr on epi.UserID = ugr.UserID and e.EventTimestamp between ugr.EffectiveStartDate and ugr.EffectiveEndDate 
    inner join GroupNames gn on ugr.GroupID = gn.GroupID
    left outer join EventAuditInformation eai on e.EventUID = eai.EventUID
    left outer join UserTable ut on eai.UserID = ut.UserID
    left outer join UserInformation ui on eai.UserID = ui.UserID and ui.UserProperty=-10
    where e.EventTimestamp between @StartDate and @EndDate
        and e.SenderSID = @FirmID
    group by e.EventUID;');
PREPARE QUERY3 FROM @dsql;
EXECUTE QUERY3;
DEALLOCATE PREPARE QUERY3;

#Generate the actual query to return results. 
set @dsql=concat('
select rl1.GroupName as `Group`, coalesce(max(rl1.ReviewerName), '''') as `Reviewer(s)`, count(distinct rl1.EventUID) as `Total Events`
    , (count(distinct rl1.EventUID) - count(distinct rl1.EventAuditUID)) as `Unreviewed Events`
    , round(((count(distinct rl1.EventUID) - count(distinct rl1.EventAuditUID)) / count(distinct rl1.EventUID)) * 100, 1) as `% Unreviewed`
    , date_format(min(rl2.EventTimestamp), ''%W, %b %c %Y %r'') as `Oldest Unreviewed`
    , count(distinct rl3.EventUID) as `<=7 Days Unreviewed`
    , count(distinct rl4.EventUID) as `8-14 Days Unreviewed`
    , count(distinct rl5.EventUID) as `>14 Days Unreviewed`
from ', @tablename, ' rl1
left outer join ', @tablename, ' rl2 on rl1.EventUID = rl2.EventUID and rl2.EventAuditUID is null
left outer join ', @tablename, ' rl3 on rl1.EventUID = rl3.EventUID and rl3.EventAuditUID is null and rl1.EventTimestamp > DATE_SUB(NOW(), INTERVAL 7 DAY) 
left outer join ', @tablename, ' rl4 on rl1.EventUID = rl4.EventUID and rl4.EventAuditUID is null and rl1.EventTimestamp between DATE_SUB(NOW(), INTERVAL 7 DAY) and DATE_SUB(NOW(), INTERVAL 14 DAY)
left outer join ', @tablename, ' rl5 on rl1.EventUID = rl5.EventUID and rl5.EventAuditUID is null and rl1.EventTimestamp < DATE_SUB(NOW(), INTERVAL 14 DAY)
group by rl1.GroupName
order by ((count(distinct rl1.EventUID) - count(distinct rl1.EventAuditUID)) / count(distinct rl1.EventUID)) * 100 desc
;');
PREPARE QUERY4 FROM @dsql;
EXECUTE QUERY4;
DEALLOCATE PREPARE QUERY4;

#Drop "temporary" table
set @dsql = concat('drop table if exists ', @tablename, ';');
PREPARE QUERY5 FROM @dsql;
EXECUTE QUERY5;
DEALLOCATE PREPARE QUERY5;

Hopefully now that we have Oracle taking over the reigns, she can give MySQL a good push. — Pacerier, May 04 '15 at 07:15
A big _sigh_. July 2016, and this temp table bug is still not fixed. I will probably come up with some kind of sequence number concatenated with a permanent table name (I'm from Oracle land) to circumvent this issue. — TheWalkingData, Jul 13 '16 at 05:00
Hattrick sigh... It might never be fixed, seeing as its 2019 already. — Zimano, Nov 22 '19 at 13:50

krlmlr · Accepted Answer · 2020-07-05T16:23:34.550

5

If switching to MariaDB (a fork of MySQL) is feasible -- this annoyance is fixed there as of version 10.2.1: https://jira.mariadb.org/browse/MDEV-5535.

edited Jul 05 '20 at 16:23

answered May 25 '20 at 05:31

krlmlr

25,056
14
120
217

score 4 · Answer 5 · answered Dec 07 '08 at 07:55

4

Personally I'd just make it a permanent table. You might want to create a separate database for these tables (presumably they'll need unique names as lots of these queries could be done at once), also to allow permissions to be set sensibly (You can set permissions on databases; you can't set permissions on table wildcards).

Then you'd also need a cleanup job to remove old ones occasionally (MySQL conveniently remembers the time a table was created, so you could just use that to work out when a clean up was required)

answered Dec 07 '08 at 07:55

MarkR

62,604
14
116
151

9

Temporary tables has the extreme advantage that you can have multiple queries running simultaneously. This is not possible with permanent tables. – Pacerier May 04 '15 at 07:14
I think the permanent table "solution" is not a solution. It solves the problem for sure, but isn't practical. So many questions come up: How do I create more than one at the same time? How would you deal with the naming convention and overwriting same named tables? What is the process for deleting the permanent table? If you could elaborate on a feasible solution using permanent tables while answering these questions, I am all ears! – Tanner Clark Jan 27 '20 at 19:23

score 1 · Answer 6 · answered Dec 08 '18 at 07:27

1

You can get around it by either making a permanent table, which you will remove afterwards, or just make 2 separate temp tables with the same data

answered Dec 08 '18 at 07:27

Inc33

1,747
1
20
26

score 1 · Answer 7 · answered Jan 27 '20 at 19:32

1

Here are the MYSQL docs about this issue. I use duplicate temporary tables like some of the answers above, however, you may have a situation where a CTE is appropriate!

https://dev.mysql.com/doc/refman/8.0/en/temporary-table-problems.html

answered Jan 27 '20 at 19:32

Tanner Clark

631
1
8
19

The cte example helped me quite a bit. Thanks! – vdidxho Jul 06 '21 at 18:20

score 0 · Answer 8 · answered Jun 19 '09 at 17:44

0

I was able to change the query to a permanent table and this fixed it for me. ( changed the VLDB settings in MicroStrategy, temporary table type).

answered Jun 19 '09 at 17:44

Getting around MySQL "Can't reopen table" error

8 Answers8

Linked