Sim Zacks wrote: > We are using mysql with dbmail 2.2.7_rc2 and I just noticed that we have > a number of orphan rows in dbmail_fromfield. (There are no matching rows > in dbmail_physmessage) > select count(*) from dbmail_fromfield a left join dbmail_physmessage b > on a.physmessage_id=b.id where b.id is null; > > I noticed this because I am planning on migrating to postgresql and the > constraints on the table (which apparently don't exist in the MySQL > version) were violated.
They should most definitely be there al right. Contraints are pretty much required, as are innodb tables (for mysql). > > There are also a number of rows (not the same number) in > dbmail_physmessage that are not in dbmail_fromfield, though it doesn't > violate a relationship, so I am paying less attention to that. right. > > I just checked some other tables and I have a ton of records in > dbmail_messages that do not have related records in dbmail_mailboxes. same story: with complete contraints that should never happen. > > (select count(*) from dbmail_messages a left join dbmail_mailboxes b on > a.mailbox_idnr=b.mailbox_idnr where b.mailbox_idnr is null;) > > > We have not used any of the maintenance functions yet, and have not done > any SQL level deletes on these tables. > > Can you thnk of a reason why this would happen? I noticed the > constraints in Postgresql are cascade delete. That means that it will > still allow these deletes but I won't have orphan records (or in other > words, will not know that records have been deleted) My guess is your conversion from 2.0 to 2.2 was incomplete. The contraints are the same in all three drivers. All contraints are 'cascade delete cascade update' -- ________________________________________________________________ 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
