Hey Sumbry and List:
If you still have orphaned messages I offer a solution.
DBMA will find and mark orphaned messages for deletion.
(DBMA V2.1.2)
http://library.mobrien.com/dbmailadministrator/
Untar it. Open in browser.
Configure from the GUI.
Select "Update Delete Status" checkbox and press "Go".
All your "orphaned messages" are marked for deletion with
dbmail_messages.status = '001'.
Look for changes in the MAIN MENU statistics column: "Number of deletes
pending"
Do it again and all are remarked "003" for immediate delete.
Your next crontab maintenance run removes all the '003's.
This 'fix_deletes function' runs a scan of the database looking for
three basic problems.
1. messages marked delete by the client but status is still 000;
2. messages having no 'owner mailbox'** and
3. messages unattached to a user.
So if this is a pressing matter, go for it... what follows is a blabber
explaining the mechanics. :o)
best...
Mike
And the blabber...
A console query fix for MySQL...
UPDATE dbmail_messages \
JOIN dbmail_mailboxes ON dbmail_messages.mailbox_idnr =
dbmail_mailboxes.mailbox_idnr \
LEFT JOIN dbmail_users ON dbmail_users.user_idnr =
dbmail_mailboxes.owner_idnr \
SET status = '001' WHERE dbmail_users.user_idnr IS NULL
Whereas SELECT and DELETE work OK, the UPDATE method however does not
work in the JOIN for PostgreSQL.
The trick is that for both PostgreSQL and MySQL the LEFT JOIN function
beautifully returns a NULL value on a non-match LEFT JOIN. A nice
commonality.
Once we have an orphaned (no user) message block, we are faced with the
problem of the DBMS(pgsql/mysql) cascading work from a NULL point. So we
go backwards from dbmail_messages.message_idnr to see which ones have no
owner; i.e.: JOIN in a reverse-create route to NULL user_idnr ROW.
Ergo for the "non_exist_user|still_exist_mail" relationship (the
dbmail_messages.mailbox_idnr matched to the
dbmail_mailboxes.mailbox_idnr matched to
dbmail_mailboxes.owner_idnr.matched to a NULL dbmail_users.user_idnr)
the LEFT JOIN is a workable solution; reverse cascading the dbmail
schema to a null value at the front of the cascade.
For DbMailAdministrator(DBMA V2.1.2) (PERL GUI) I did it this way to
meet the distinctive requirements of both PostgreSQL and MySQL:
###### this is only 1 of 3 parts to the "fix_delete funtion"
# Approx Line 2068 DBMA.cgi
# Find em...
$sth = $dbh->prepare(
"SELECT message_idnr from $dbmail_messages_table
JOIN $dbmail_mailboxes_table ON $dbmail_messages_table.mailbox_idnr =
$dbmail_mailboxes_table.mailbox_idnr
LEFT JOIN $dbmail_users_table ON $dbmail_users_table.user_idnr =
$dbmail_mailboxes_table.owner_idnr
WHERE $dbmail_users_table.user_idnr IS NULL"
);
unless ($sth->execute())
{
print
"$DBI::errstr <br />Failed in updating deleted status for
non-existant users.";
}
# and the Judas kiss...
while (($message_idnr) = $sth->fetchrow_array)
{
$str .= "$message_idnr";
$sth =
$dbh->prepare(
"UPDATE $dbmail_messages_table SET status = '001',
deleted_flag = '1' where message_idnr = '$message_idnr'"
);
$sth->execute();
unless ($dbh->commit)
{
$errormessage =
"$DBI::errstr <br />Failed in updating
deleted status for
non-existant users.";
&DBMA_ConnectStatus;
}
}
$sth->finish();
# etc...
##############################
CIAO
sumbry wrote:
orphaned messageblks is exactly the sort of stuff dbmail-util is supposed to
look for.
if dbmail-util -r doesn't clean out those blks, we have a bug.
I pointed this out on dbmail-dev a little while ago as I had the same
problem. dbmail-util doesn't yet fix these.
select count(*) from dbmail_physmessage WHERE id NOT IN (SELECT
physmessage_id FROM dbmail_messages);
Replace select with delete to actually delete the stragglers. This is
only for postgres (no idea if mysql supports this).
Weren't there going to be a cleanup tool to get rid of old messages no
longer linked from any mailbox?
I did some housekeeping, and still have 300.000+ messageblks, tho only
350 messages.
-----
"It's a damn poor mind that can only think of one way
to spell a word." -- Andrew Jackson
[EMAIL PROTECTED]
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
