Como se locomover MySQL “Não é possível reabrir tabela” erro

Atualmente, estou ocupado implementando um tipo de filtro para o qual preciso gerar uma cláusula INNER JOIN para cada “tag” a ser filtrada.

O problema é que depois de um monte de SQL, eu tenho uma tabela que contém todas as informações que preciso para fazer a minha seleção, mas eu preciso dela novamente para cada INNER JOIN gerado

Isso basicamente se parece com:

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 

Isso funciona, mas eu preferiria que a tabela de “pesquisa” fosse temporária (pode ser várias ordens de magnitude menor se não for uma tabela normal), mas isso me causa um erro muito chato: Can't reopen table

Algumas pesquisas me levam a este relatório de bug, mas o pessoal do MySQL não parece se importar que tal recurso básico (usando uma tabela mais de uma vez) não funcione com tabelas temporárias. Eu estou correndo em muitos problemas de escalabilidade com este problema.

Existe alguma solução viável que não requer que eu gerencie potencialmente muitas tabelas temporárias, mas muito reais, ou me faça manter uma tabela enorme com todos os dados contidos nela?

Atenciosamente, Kris

[adicional]

A resposta GROUP_CONCAT não funciona na minha situação porque minhas condições são múltiplas colunas em uma ordem específica, isso faria com que o OR fosse necessário para ser ANDs. No entanto, isso me ajudou a resolver um problema anterior, então agora a tabela, temporária ou não, não é mais necessária. Nós estávamos apenas pensando muito genérico para o nosso problema. Toda a aplicação de filtros foi recuperada de cerca de um minuto para bem menos de um quarto de segundo.

Certo, os documentos do MySQL dizem: “Você não pode se referir a uma tabela TEMPORARY mais de uma vez na mesma consulta.”

Aqui está uma consulta alternativa que deve encontrar as mesmas linhas, embora todas as condições das linhas correspondentes não estejam em colunas separadas, elas estarão em uma lista separada por vírgulas.

 SELECT f1.baseID, GROUP_CONCAT(f1.condition) FROM search f1 WHERE f1.condition IN (, , ... ) GROUP BY f1.baseID HAVING COUNT(*) = ; 

Uma solução simples é duplicar a tabela temporária. Funciona bem se a tabela for relativamente pequena, o que geralmente é o caso de tabelas temporárias.

Pessoalmente, eu apenas faria uma mesa permanente. Você pode querer criar um database separado para essas tabelas (presumivelmente, eles precisarão de nomes exclusivos, já que muitas dessas consultas podem ser feitas de uma só vez), também para permitir que as permissions sejam definidas de maneira sensata (você pode definir permissions em bancos de dados; t definir permissions nos curingas da tabela).

Então você também precisaria de um trabalho de limpeza para remover os antigos ocasionalmente (o MySQL convenientemente lembra o horário em que a tabela foi criada, então você poderia usá-la apenas quando a limpeza fosse necessária)

Eu contornei isso criando uma tabela “temporária” permanente e sufixando o SPID (desculpe, eu sou do SQL Server) ao nome da tabela, para criar um nome de tabela exclusivo. Em seguida, criar instruções SQL dinâmicas para criar as consultas. Se algo de ruim acontecer, a tabela será descartada e recriada.

Eu estou esperando por uma opção melhor. Vamos, MySQL Devs. A solicitação de recurso ‘bug’ / ‘está aberta desde 2008! Parece que todos os “bugs” encontrados estão no mesmo barco.

 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; 

Consegui alterar a consulta para uma tabela permanente e isso resolveu para mim. (alterou as configurações do VLDB no MicroStrategy, tipo de tabela temporária).