Oggi mentre aggiornavo Roundcube alla versione corrente sono incappato in un problema che in teoria non di dovrebbe mai verificare in un db: tuple duplicate.

L’errore è venuto fuori eseguendo questa query:
ALTER TABLE `messages`
DROP `body`,
DROP INDEX `cache_key`,
ADD `structure` TEXT,
ADD UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`);

Che ha lo scopo di adeguare il vecchio db con la struttura nuova.
In particolare il problema si verificava aggiungendo l’indice univoco composto da `user_id`, `cache_key`, `uid`.
Siccome seguo Roundcube da prima che gli spuntassero i primi dentini è normale che ci sono stati errori nello sviluppo e quindi è logico che si è arrivati ad un problema simile.

Come si procede per risolvere l’inconveniente? In due semplici step:

1. Si elencano i valori duplicati
2. Si eliminano i valori duplicati

Prima di togliere i valori duplicati l’errore era questo:

#1062 - Duplicate entry '8-INBOX.msg-1' for key 2

Giusto per rendere il più pratico possibile l’articolo…

Procediamo con il passo1:
Per elencare i valori duplicati basta usare questa query:

SELECT `cache_key` , `uid` , `user_id`, COUNT(*) AS quanti
FROM `messages`
GROUP BY `cache_key` , `uid` , `user_id`
HAVING quanti > 1;
Seleziono i campi coinvolti nella creazione dell’indice univoco ed in più conto le occorrenze, infine raggruppo i valori per i campi selezionati e mostro solo quelli che hanno occorrenza maggiore di 1.
Il risultato spaventoso della query è:

cache_key uid user_id quanti
INBOX.msg 1 8 2
INBOX.msg 2 70 2
INBOX.msg 3 30 2
INBOX.msg 5 101 3
INBOX.msg 28 43 4
INBOX.msg 40 43 3
INBOX.msg 42 43 2
INBOX.msg 45 43 2
INBOX.msg 52 43 2
INBOX.msg 53 43 2
INBOX.msg 57 101 3
INBOX.msg 63 101 5
INBOX.msg 84 101 2
INBOX.msg 85 101 2
INBOX.msg 1155 68 2
INBOX.msg 1156 68 2
INBOX.msg 1920 7 3
INBOX.msg 3362 7 2
INBOX.msg 3363 7 2

Bene adesso occorre visualizzare e distruggere!
Con questa query:

SELECT *
FROM `messages`
WHERE `cache_key` = 'INBOX.msg'
AND `uid` =1
AND `user_id` =8
si selezionano le occorenze della prima tupla duplicata e quindi è sufficiente cancellare quelle in eccesso. Io ho sempre lasciato la più vecchia, massimo rispetto per gli anziani.
Ripetere la query modificando opportunamente parametri per tutte le tuple duplicate.

Finito?
Proviamo adesso a rieseguire la query….FUNZIONA!
Infine voglio regalare una chicca, mettendo dopo ALTER, prima di TABLE, la clausola IGNORE il DBMS non si ferma al primo errore di record duplicato, ma va avanti, e di fatto salta l’inserimento dei record con valori già inseriti.
Quindi la fatica di prima era evitabile!
Hihihi che dispettoso vero? Sento gli insulti…
Non l’ho detto subito perché non l’ho mai provato e a me piace fare le cose a manina per verificare con i miei occhi che tutto sia a posto.
Quando riavrò il medesimo problema userò IGNORE.