绕过 MySQL“无法重新打开表”错误

Getting around MySQL "Can't reopen table" error

提问by Kris

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.

我目前正忙于实现一种过滤器，我需要为每个要过滤的“标签”生成一个 INNER JOIN 子句。

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

问题是，在执行了一大堆 SQL 之后，我有一个表，其中包含进行选择所需的所有信息，但是对于每个生成的 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

这有效，但我更希望“搜索”表是临时表（如果它不是普通表，它可能会小几个数量级）但这给了我一个非常烦人的错误： Can't reopen table

Some research leads me to this bug reportbut 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.

一些研究让我找到了这个错误报告，但 MySQL 的人似乎并不关心这样一个基本特性（多次使用一个表）不适用于临时表。我在这个问题上遇到了很多可扩展性问题。

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.

GROUP_CONCAT 答案在我的情况下不起作用，因为我的条件是按特定顺序排列的多列，它会使 OR 脱离我需要的 AND。但是，它确实帮助我解决了一个较早的问题，因此现在不再需要表，无论是否为临时表。我们只是认为我们的问题太笼统了。过滤器的整个应用现在已经从大约一分钟恢复到不到四分之一秒。

采纳答案by krlmlr

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

如果切换到 MariaDB 是可行的——这个烦恼在 10.2.1 版中得到解决：https: //jira.mariadb.org/browse/MDEV-5535。

回答by Pete

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.

一个简单的解决方案是复制临时表。如果表相对较小，则效果很好，临时表通常就是这种情况。

回答by Bill Karwin

Right, the MySQL docssay: "You cannot refer to a TEMPORARYtable more than once in the same query."

是的，MySQL文档说：“您不能TEMPORARY在同一个查询中多次引用一个表。”

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

回答by beeks

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.

我通过创建一个永久的“临时”表并将 SPID（对不起，我来自 SQL Server 领域）后缀到表名来解决这个问题，以创建一个唯一的表名。然后创建动态 SQL 语句来创建查询。如果发生任何不好的事情，该表将被删除并重新创建。

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.

我希望有更好的选择。来吧，MySQL 开发人员。“错误”/“功能请求”自 2008 年以来一直开放！似乎遇到的所有“错误”都在同一条船上。

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;

回答by MarkR

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

就我个人而言，我只想把它做成一张永久的桌子。您可能希望为这些表创建一个单独的数据库（大概它们需要唯一的名称，因为可以一次完成许多查询），还允许合理地设置权限（您可以对数据库设置权限；您可以” t 设置表通配符的权限）。

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)

然后你还需要一个清理工作来偶尔删除旧的（MySQL 方便地记住一个表的创建时间，所以你可以用它来计算何时需要清理）

回答by krlmlr

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

我能够将查询更改为永久表，这为我修复了它。（更改了 MicroStrategy 中的 VLDB 设置，临时表类型）。

回答by Inc33

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

您可以通过制作一个永久表来解决它，您将在之后删除该表，或者只制作 2 个具有相同数据的单独临时表

回答by Tanner Clark

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!

这是有关此问题的 MYSQL 文档。我使用重复的临时表，如上面的一些答案，但是，您可能会遇到适合 CTE 的情况！

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

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