Getting around MySQL "Can't reopen table" error

Question

Hi folks,

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.

Answer 1

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>;

Answer 2

How huge is huge? Where does the data come from to put into the temp table? Is it a bunch of semi-huge tables? Or a huge bunch of smaller unrelated tables that take a huge bunch of plumbing to get your temp table from? What would be the difference between the huge table and the temp table?

Is this something that might be invoked by several people simultaneously, so a real table would cause collisions?

I've generally found that MySQL is happier if you stick to their mainstream objects, and you're doing something fairly tricky already with all the self-joins. Unless "huge" means many many gigabytes, I'd suggest you try querying from the primary sources. Or else create a permanent table and use it temporarily.

In "fN", N = ?

Answer 3

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)

Answer 4

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).