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
