I know it seems like a lot of queries, but it basically runs like this.
A beginning SELECT * FROM dbmail_messages WHERE status='3'; // This
selects the list of message ids to be deleted.
Now each ID is iterated over like so:
SELECT * FROM dbmail_messages WHERE message_idnr='x'; // Grab the
physmessage_id that was being used.
DELETE FROM dbmail_messages WHERE message_idnr='x';
SELECT * FROM dbmail_messages WHERE physmessage_id='y'; // Check
if the physmessage is still in use in a different message.
DELETE FROM dbmail_physmessages WHERE physmessage_id='y'; // Run
this query if no other messages use this physmessage.
DELETE FROM dbmail_messageblks WHERE physmessage_id='y'; // And
this one too if not needed anymore.
End Loop
Unfortunately not all SQL servers used by dbmail handle constraints
and/or triggers making dbmail take care of the deletes for the
physmessage and messageblk rows.
There has been some talk about how to delete orphaned physmessages, so
it is possible to do the deletes by hand some thing like this provided
that you have your database has the proper constraints or triggers to
delete the other rows in tables like dbmail_headervalue,
dbmail_messageblks, etc.
dbmail-util -dy # Set delete on messages that can be dumped.
DELETE FROM dbmail_messages WHERE status='3'; // Delete the deleted
messages.
DELETE FROM dbmail_physmessage WHERE id NOT IN (SELECT physmessage_id
FROM dbmail_messages); // Delete the orphans now
dbmail-util -ay # Check the whole thing
Marc Dirix wrote:
Hi,
I'm deleting about 15GB of mail, from unused mailboxes.
I've done this by setting status=3 and then running dbmail-util. Which
is now running for hours and hours.
So I started checking which statements are executed, if I'm right it
is doing:
SELECT physmessage_id
FROM dbmail_messages
WHERE message_idnr = 7714566;
DELETE FROM dbmail_messages
WHERE message_idnr = 7714566;
SELECT message_idnr
FROM dbmail_messages
WHERE physmessage_id = 4015611;
DELETE FROM dbmail_physmessage
WHERE id = 4015611;
DELETE FROM dbmail_messageblks
WHERE physmessage_id = 4015611;
Now the questions are:
1) What use has the second SELECT statement?
2) Why are you not using a transaction for this? (Or did I mis that?)
3) Is there no way to batch multiple deletes with per statement?
Marc
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
