Contourner l'erreur "Can't reopen table" de MySQL

Question

Je suis actuellement occupé à mettre en œuvre une sorte de filtre pour lequel je dois générer une clause INNER JOIN pour chaque "tag" à filtrer.

Le problème est qu'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 à nouveau besoin pour chaque INNER JOIN généré.

En gros, ça ressemble à ça :

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 de loin que la table de "recherche" soit temporaire (elle peut être plus petite de plusieurs ordres de grandeur si elle n'est pas une table normale) mais cela me donne une erreur très ennuyeuse : Can't reopen table

Quelques recherches m'amènent à ce rapport de bogue mais les gens de MySQL ne semblent pas se soucier du fait qu'une fonctionnalité aussi basique (utiliser une table plus d'une fois) ne fonctionne pas avec les tables temporaires. Je rencontre de nombreux problèmes d'évolutivité avec cette question.

Existe-t-il une solution de rechange viable qui ne m'oblige pas à gérer des tas de tables temporaires mais bien réelles ou à maintenir une énorme table avec toutes les données qu'elle contient ?

Cordialement, Kris

[supplémentaire]

La réponse GROUP_CONCAT ne fonctionne pas dans mon cas, car mes conditions sont des colonnes multiples dans un ordre spécifique, ce qui ferait des OU à partir de ce dont j'ai besoin pour être des ET. Cependant, elle m'a aidé à résoudre un problème antérieur, de sorte que la table, temporaire ou non, n'est plus nécessaire. Nous avions simplement une vision trop générique pour notre problème. L'ensemble de l'application des filtres a maintenant été ramené d'environ une minute à bien moins d'un quart de seconde.

Answer 1

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

Answer 2

Bien, le MySQL docs dites : "Vous ne pouvez pas vous référer à un TEMPORARY plus d'une fois dans la même requête".

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

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

Answer 3

J'ai contourné ce problème en créant une table permanente "temporaire" et en ajoutant le suffixe SPID (désolé, je viens du monde de SQL Server) au nom de la table, pour obtenir un nom de table unique. Ensuite, j'ai créé des instructions SQL dynamiques pour créer les requêtes. Si un problème survient, la table sera supprimée et recréée.

J'espère une meilleure option. Allez, les développeurs de MySQL. Le 'bug'/'feature request' est ouvert depuis 2008 ! Il semble que tous les 'bugs' que j'ai rencontrés sont 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;

Answer 4

Si vous pouvez passer à MariaDB (un dérivé de MySQL), ce problème est résolu à partir de la version 10.2.1 : https://jira.mariadb.org/browse/MDEV-5535 .

Answer 5

Personnellement, j'en ferais une table permanente. Vous pourriez vouloir créer une base de données distincte pour ces tables (elles auront probablement besoin de noms uniques car beaucoup de ces requêtes pourraient être effectuées en même temps), également pour permettre aux autorisations d'être définies de manière raisonnable (vous pouvez définir des autorisations sur les bases de données ; vous ne pouvez pas définir des autorisations sur les jokers de table).

Ensuite, vous aurez également besoin d'un travail de nettoyage pour supprimer les anciennes tables de temps en temps (MySQL se souvient de l'heure de création d'une table, vous pouvez donc l'utiliser pour déterminer quand un nettoyage est nécessaire).