Brilliant idea on the creating a new table. I'll give it a try Monday or Tuesday just in case something goes poorly. I'll report back my findings as well. My main though for doing the select to get a list of ID's is that I can be a bit more "gentle" trying to delete 600k items. Since this is in production I didn't want to lock the tables, and shoot the process load through the roof. I also thought that was how you might implement this in dbmail-util eventually. Thanks for your help as always, David
________________________________ From: [EMAIL PROTECTED] on behalf of Paul J Stevens Sent: Fri 7/14/2006 6:54 AM To: DBMail mailinglist 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
<<winmail.dat>>
