Jorge, One part up for debate that *I* did differently than DBMail expects is that I created a column in dbmail_aliases called user_idnr and made it a foreign key with cascade on delete. This way I could drop everything (except the physmessages as we learned) just by deleting the dbmail_user. There are situation that you may want dbmail_aliases to be stand alone, but 99.9% of the time I didn't require that, and when I did, I just made a dummy user that I attached all my aliases and forwards to. These are system like things, or reserved account names (root, postmaster, etc). Most I did in my Postfix config so I have only 1 or 2 in dbmail. That's my preference, and if it works for you, then I think you can probably do the same thing and achieve the same results. As an aside, I also put my SpamAssassin tables in with my dbmail tables. This way I can tie them into the dbmail_user table as well and cascade delete those as well. -- David
________________________________ From: [EMAIL PROTECTED] on behalf of Jorge Bastos Sent: Fri 7/14/2006 9:22 AM To: DBMail mailinglist Subject: Re: [Dbmail] Delete accounts 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" <dbmail@dbmail.org> 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 > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail > _______________________________________________ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
<<winmail.dat>>
