I've been watching you two talking,
But i didn't yet implemented that this system sinse i asked this on the
list.
I was thinking on delete the user from the dbmail-users table and then pass
dbmail-util to take care of the rest, but then i saw you (paul and niblett)
posting this on the list and i started to think of deleting the record from
the dbmail-users table and then use this SQL statment,sinse this is going to
be a new thing my question is, is this improvement going to be inserted into
dbmail-util Paul ?
If so can you warn when it is available in svn?
Thanks,
Jorge
----- Original Message -----
From: "Paul J Stevens" <[EMAIL PROTECTED]>
To: "DBMail mailinglist" <[email protected]>
Sent: Friday, July 14, 2006 11:54 AM
Subject: Re: [Dbmail] Delete accounts
Just tried this myself. Took a loooong time to run, but appears to have
cleaned out about 1/3 of my storage. My innodb status suddenly shows 10G
space! Nice. And it didn't lock the tables most of the time so mail
services were not interrupted.
Paul J Stevens wrote:
Looks ok to me, but why write a special script? You can do:
CREATE TABLE tmp (id bigint(21) not null primary key);
INSERT INTO tmp (id) SELECT id FROM dbmail_physmessage p
LEFT JOIN dbmail_messages m ON p.id = m.physmessage_id
WHERE m.physmessage_id IS NULL);
DELETE FROM dbmail_physmessage WHERE id IN (SELECT id FROM tmp);
DROP TABLE tmp;
This will delete all physmessages that do not have a message associated
with them, taking with them all related messageblks if you have the
constraints setup correctly.
Niblett, David A wrote:
So before I do something monumentally stupid. Paul can you
tell me if this is correct for finding my unconnected physmessages.
SELECT dbmail_physmessage.id, dbmail_messages.physmessage_id
FROM dbmail_physmessage
LEFT JOIN dbmail_messages
ON dbmail_physmessage.id = dbmail_messages.physmessage_id
WHERE dbmail_messages.physmessage_id IS NULL
My plan is to dump the ID's to a file and then use a simple program
to work through them and delete them.
If my calculations are correct I have about 600k messages that are
not attached to a mailbox any more. That's about 1/3 of my Email
store and over a year of time.
Thanks.
--
David A. Niblett | email: [EMAIL PROTECTED]
Network Administrator | Phone: (352) 334-3400
Gainesville Regional Utilities | Web: http://www.gru.net/
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Paul J Stevens
Sent: Tuesday, July 11, 2006 3:37 AM
To: DBMail mailinglist
Subject: Re: [Dbmail] Delete accounts
Niblett, David A wrote:
Sorry, I didn't remember, that I did create a foreign key constraint
on a new column in dbmail_aliases connecting it back to user_idnr.
By adding that column, I was able to retain complete functionality.
Now I admit that my internal requirement is that you can't have an
dbmail_alias entry if you don't have a dbmail_users entry. I made a
placeholder user and tie all my standalone aliases to it. I suppose I
could have used user_idnr #1 (the internal user), but that doesn't
seem like a good idea.
As for the messages, I think that is also removed (this is v2.0.10).
CREATE TABLE dbmail_messages (
...
mailbox_idnr INT8 REFERENCES dbmail_mailboxes(mailbox_idnr)
ON DELETE CASCADE,
physmessage_id INT8 REFERENCES dbmail_physmessage(id)
ON DELETE CASCADE,
...
David,
You're reading this the wrong way. The statement above says that if you
delete a physmessage all associated messages are deleted. Deleting
messages does *not* affect related physmessages.
Since the dbmail_messages keys to the dbmail_mailboxes, and to
dbmail_physmessages (same with dbmail_messageblks), I believe that it
will cascade all the way down.
No it doesn't. And that's a known bug (#305).
When I delete a user, I never see any orphaned blocks when I run
dbmail-util.
Because it turns out dbmail-util does not check for them properly;
contrary to my earlier statement.
--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
Dbmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
