MySQL "Ca n't reopen table"오류 해결

MySQL "Ca n'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 대답은 내 상황에서 작동하지 않으며 AND가 필요한 것에서 OR을 만들 것입니다. 그러나 이전 문제를 해결하는 데 도움이되었으므로 이제 temp 여부에 관계없이 테이블이 더 이상 필요하지 않습니다. 우리는 우리의 문제에 대해 너무 일반적이라고 생각했습니다. 이제 필터의 전체 적용이 약 1 분에서 1/4 초 미만으로 돌아 왔습니다.

---

맞습니다. MySQL 문서 에서는 " TEMPORARY같은 쿼리에서 테이블을 두 번 이상 참조 할 수 없습니다."라고 말합니다 .

다음은 동일한 행을 찾아야하는 대체 쿼리입니다. 일치하는 행의 모든 ​​조건이 별도의 열에있는 것은 아니지만 쉼표로 구분 된 목록에 있습니다.

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

---

간단한 해결책은 임시 테이블을 복제하는 것입니다. 테이블이 비교적 작은 경우 잘 작동하며, 임시 테이블의 경우가 많습니다.

---

이 문제를 해결하려면 영구 "임시"테이블을 만들고 테이블 이름에 SPID (죄송합니다. 저는 SQL Server 영역에서 왔습니다)를 접미사로 붙여서 고유 한 테이블 이름을 만듭니다. 그런 다음 동적 SQL 문을 만들어 쿼리를 만듭니다. 문제가 발생하면 테이블이 삭제되고 다시 생성됩니다.

더 나은 옵션을 원합니다. 어서, MySQL Devs. '버그'/ '기능 요청'이 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;

---

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

---

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

참고URL : https://stackoverflow.com/questions/343402/getting-around-mysql-cant-reopen-table-error