Jorge, You're mixing up tables here.
there is *no* direct connection between messages and partlists. There *is* a 1-1 connection between physmessages and partlists, and that is maintained by a database constraint. But mimeparts have a 1-N connection to partlists, so no database constraint there. To remedie this we need to find lost mimeparts, and that is what this query is for: > > r = db_query(c, "SELECT p.id FROM %smimeparts p LEFT > JOIN %spartlists l ON p.id = l.part_id " > > "WHERE l.part_id IS NULL", DBPFX, DBPFX); > Unconnected mimeparts are deleted thus: > db_exec(c, "DELETE FROM %smimeparts > WHERE id = %llu", DBPFX, *(u64_t *)ids->data); But what you're doing is something else: Jorge Bastos wrote: > mysql> UPDATE dbmail_messages SET status = 3 WHERE status = 2; > > Query OK, 0 rows affected (0.00 sec) > > Rows matched: 0 Changed: 0 Warnings: 0 So, no changes! > mysql> DELETE FROM dbmail_messages WHERE status=3; > > Query OK, 1 row affected (0.03 sec) Ok, 1 message deleted. But remember this deletes only the metadata of a message (dbmail_messages and dbmail_keywords). The actual raw message data are stored in [dbmail_physmessage (1) -> (1) dbmail_partlists (n) -> (n) dbmail_mimeparts] plus all the caching tables that link to dbmail_physmessage. To find and delete physmessages that are no longer connected to any rows in dbmail_messages you need to run dbmail-util -ty, which you do just fine. Problem is dbmail-util -ty doesn't give back feedback anymore to report the number of rows updated or deleted by -dy and -py respectively. So there is no way for my to tell you more without more info. However, after doing a delete on dbmail_messages dbmail-util -ty should report cleaning up physmessages, and mimeparts, but no partlists. > mysql> DELETE dbmail_mimeparts.* FROM dbmail_mimeparts LEFT JOIN > dbmail_partlists ON (dbmail_mimeparts.id=dbmail_partlists.part_id) WHERE > dbmail_partlists.physmessage_id IS NULL; I'm pretty sure we don't use that kind of query. I wasn't even aware of this trick. > Query OK, 0 rows affected (13 min 31.80 sec) Which is ok, since you haven't cleaned up the physmessage connected to the message you deleted. But you are onto something here, which I've fixed now. -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
