A NOTE has been added to this issue. ====================================================================== http://www.dbmail.org/mantis/view.php?id=305 ====================================================================== Reported By: variable Assigned To: paul ====================================================================== Project: DBMail Issue ID: 305 Category: Command-Line programs (dbmail-users, dbmail-util) Reproducibility: always Severity: minor Priority: high Status: acknowledged target: ====================================================================== Date Submitted: 28-Feb-06 23:04 CET Last Modified: 09-Sep-06 11:41 CEST ====================================================================== Summary: integrity checks in maintenance.c are incomplete Description: When I delete a user either through dbmail-util or just though phppgadmin the phymessage and messageblks for that users message still exist, the message, alias, and mailbox have all been deleted. If I run dbmail-users -e <user> then dbmail-users -d <user> then it works as it should. This has been the same on both 2.0.7 and 2.0.9 on postgresql. ====================================================================== Relationships ID Summary ---------------------------------------------------------------------- related to 0000348 When delete subfolder, MS Outlook don't... ======================================================================
---------------------------------------------------------------------- paul - 01-Mar-06 10:45 ---------------------------------------------------------------------- This is a feature, not a bug. There are no relational constraints on the mail-storage tables (physmessage, messageblks). This is because physmessages can be shared between more than one mailbox/user. ---------------------------------------------------------------------- variable - 01-Mar-06 21:37 ---------------------------------------------------------------------- But shouldnt dbmail-util -ay detect unattached mail and delete it? Because it does not do this. This is on a test system with only 2 users as well, and only 1 message at a time. ---------------------------------------------------------------------- paul - 01-Mar-06 22:50 ---------------------------------------------------------------------- I've rephrased the report then. dbmail-util -t currently doesn't test for unused physmessages. This is an omission. all the db_icheck_... functions need some cleaning up and refactoring. ---------------------------------------------------------------------- variable - 09-Mar-06 21:18 ---------------------------------------------------------------------- in a temporary fix for this problem i added the below to my php delete user script and it cleans all the messages out. DELETE FROM dbmail_physmessage WHERE id NOT IN (SELECT physmessage_id FROM dbmail_messages) that should work if it were in dbmail-util -ay as well. ---------------------------------------------------------------------- niblettda - 12-Jul-06 14:46 ---------------------------------------------------------------------- Just a suggestion to us a JOIN rather than an imbeded select mainly for speed and memory requirements. In my testing SELECT FROM dbmail_physmessage WHERE id NOT IN (SELECT physmessage_id FROM dbmail_messages) is much slower and in most cases fails for me with http://www.dbmail.org/mantis/view.php?id=6#1.5 million records in physmessage and dbmail_messages. I had much better results with 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 Though I'm still trying to figure out the best way to actually delete the records this way. ---------------------------------------------------------------------- schwarz - 07-Sep-06 14:12 ---------------------------------------------------------------------- Work-around: CREATE TEMPORARY TABLE tmptable 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; DELETE FROM dbmail_physmessage USING tmptable, dbmail_physmessage WHERE dbmail_physmessage.id = tmptable.id; -> then run "dbmail-util -ay" ---------------------------------------------------------------------- aaron - 09-Sep-06 11:41 ---------------------------------------------------------------------- Does this work on PostgreSQL? It works well on MySQL 4.1... DELETE FROM p USING dbmail_physmessage p LEFT JOIN dbmail_messages m ON p.id = m.physmessage_id WHERE m.physmessage_id IS NULL; Issue History Date Modified Username Field Change ====================================================================== 28-Feb-06 23:04 variable New Issue 01-Mar-06 10:45 paul Note Added: 0001015 01-Mar-06 10:45 paul Status new => resolved 01-Mar-06 10:45 paul Resolution open => no change required 01-Mar-06 21:36 variable Status resolved => feedback 01-Mar-06 21:36 variable Resolution no change required => reopened 01-Mar-06 21:36 variable Note Added: 0001017 01-Mar-06 21:36 variable Note Edited: 0001017 01-Mar-06 21:37 variable Note Edited: 0001017 01-Mar-06 22:50 paul Note Added: 0001018 01-Mar-06 22:50 paul Assigned To => paul 01-Mar-06 22:50 paul Status feedback => acknowledged 01-Mar-06 22:50 paul Resolution reopened => open 01-Mar-06 22:50 paul Projection none => minor fix 01-Mar-06 22:50 paul Category Database layer => Command-Line programs (dbmail-users, dbmail-util) 01-Mar-06 22:50 paul Summary Deletion of user refuses to delete mail => integrity checks in maintenance.c are incomplete 09-Mar-06 21:18 variable Note Added: 0001032 12-May-06 09:32 paul Relationship added related to 0000348 01-Jun-06 21:17 paul Priority normal => high 01-Jun-06 21:17 paul ETA none => < 1 month 12-Jul-06 14:46 niblettda Note Added: 0001295 07-Sep-06 14:12 schwarz Note Added: 0001395 07-Sep-06 14:12 schwarz Issue Monitored: schwarz 09-Sep-06 11:41 aaron Note Added: 0001398 ======================================================================
