避开 MySQL“ Can’t reopen table”错误

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

问题是，在执行了一系列 SQL 之后，我有了一个包含所有我需要进行选择的信息的表，但是对于每个生成的 INNER JOIN，我还需要它

这基本上看起来像:

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

这样可以，但我更希望“搜索”表是临时的(如果它不是普通的表，它可以小几个数量级) ，但这给了我一个非常恼人的错误: Can't reopen table

一些研究让我想到了 这个漏洞报告，但 MySQL 的人似乎并不关心这样一个基本特性(多次使用一个表)是否适用于临时表。在这个问题上，我遇到了很多可伸缩性问题。

是否有任何可行的解决方案，不需要我管理潜在的大量临时但非常真实的表，或者让我维护一个包含所有数据的巨大表？

问候你，克里斯

[附加资料]

GROUP _ CONCAT 答案在我的情况下不起作用，因为我的条件是按特定顺序排列的多个列，它将使 ORs 超出我需要的 AND。然而，它确实帮助我解决了一个早期的问题，所以现在表，临时与否，不再需要。我们只是觉得我们的问题太一般了。整个过滤器的应用现在已经从大约一分钟恢复到四分之一秒以下。

95041 次浏览
小开

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

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)

小开

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

小开

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.

小开

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

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

小开

enter image description here

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

小开
最佳答案

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.


提交答案