Comment contourner l’erreur MySQL «Impossible de rouvrir la table»

Je suis actuellement en train d’implémenter un filtre pour lequel je dois générer une clausse INNER JOIN pour chaque “tag” à filtrer.

Le problème est que après tout un tas de SQL, j’ai une table qui contient toutes les informations dont j’ai besoin pour faire ma sélection, mais j’en ai encore besoin pour chaque INNER JOIN généré.

Cela ressemble essentiellement à:

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 

Cela fonctionne mais je préférerais que la table “search” soit temporaire (elle peut être plusieurs fois plus petite si ce n’est pas une table normale) mais cela me donne une erreur très agaçante: Can't reopen table

Certaines recherches m’amènent à ce rapport de bogue, mais les gens de MySQL ne semblent pas se soucier qu’une telle fonctionnalité de base (en utilisant plusieurs fois une table) ne fonctionne pas avec des tables temporaires. Je rencontre beaucoup de problèmes d’évolutivité avec ce problème.

Existe-t-il une solution de contournement viable qui ne nécessite pas de gérer un grand nombre de tables temporaires mais très réelles, ou de créer une table énorme contenant toutes les données?

Cordialement, Kris

[Additionnel]

La réponse GROUP_CONCAT ne fonctionne pas dans ma situation car mes conditions sont des colonnes multiples dans un ordre spécifique, cela ferait des OR de ce que je dois être des AND. Cependant, cela m’a aidé à résoudre un problème antérieur, de sorte que la table, temporaire ou non, n’est plus requirejse. Nous pensions simplement trop générique à notre problème. L’application intégrale des filtres a maintenant été ramenée d’une minute à moins d’un quart de seconde.

    A droite, les documents MySQL disent: “Vous ne pouvez pas vous référer à une table TEMPORARY plus d’une fois dans la même requête.”

    Voici une autre requête qui devrait rechercher les mêmes lignes, bien que toutes les conditions des lignes correspondantes ne soient pas dans des colonnes séparées, elles figureront dans une liste séparée par des virgules.

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

    Une solution simple consiste à dupliquer la table temporaire. Fonctionne bien si la table est relativement petite, ce qui est souvent le cas avec les tables temporaires.

    Personnellement, je ferais une table permanente. Vous voudrez peut-être créer une firebase database distincte pour ces tables (elles nécessiteront probablement des noms uniques car beaucoup de ces requêtes pourraient être effectuées en même temps), pour permettre également de définir les permissions de manière judicieuse. t définir des permissions sur les jokers de table).

    Ensuite, vous avez également besoin d’un travail de nettoyage pour supprimer les anciens de temps en temps (MySQL se souvient du moment où une table a été créée, vous pouvez donc vous en servir pour déterminer quand un nettoyage est nécessaire).

    J’ai contourné ce problème en créant une table “temporaire” permanente et en ajoutant le nom SPID (désolé, je viens de SQL Server land) au nom de la table, pour créer un nom de table unique. Ensuite, créer des instructions SQL dynamics pour créer les requêtes. Si quelque chose de mauvais arrive, la table sera supprimée et recréée.

    J’espère une meilleure option. Allez, MySQL Devs. Le ‘bug’ / ‘demande de fonctionnalité’ est ouvert depuis 2008! On dirait que tous les «bugs» rencontrés se trouvent dans le même bateau.

     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; 

    J’ai été en mesure de changer la requête en une table permanente et cela a résolu le problème pour moi. (modification des parameters VLDB dans MicroStrategy, type de tableau temporaire).