paul at nfg
Sep 19, 2009, 1:27 PM
Post #3 of 9
(1634 views)
Permalink
|
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 [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
|
