Mailing List Archive: DBMail: users

data loss

Index | Next | Previous | View Threaded

sim at compulab

Apr 7, 2008, 3:12 AM

Post #1 of 6 (447 views)
Permalink

data loss

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.

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.

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.

(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)

Thank you
Sim
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

marc at electronics-design

Apr 7, 2008, 3:15 AM

Post #2 of 6 (416 views)
Permalink

Re: data loss [In reply to]

If I'm correct, you should be able to ditch data from dbmail_fromfield
and dbmail_header***, then import in postgresql, and run dbmail-util on
the database to restore table-data
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

uwe at kiewel-online

Apr 7, 2008, 3:27 AM

Post #3 of 6 (416 views)
Permalink

Re: data loss [In reply to]

Sim Zacks schrieb:
> 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.
>
> 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.
>
> 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.
>
> (select count(*) from dbmail_messages a left join dbmail_mailboxes b on
> a.mailbox_idnr=b.mailbox_idnr where b.mailbox_idnr is null;)
>
>
>
As you say:
> We have not used any of the maintenance functions yet, and have not done
> any SQL level deletes on these tables.
>
Did you ever run "dbmail-util -c -t -u -p -d -y"?

This utility do following:
(from my log:)
Repairing DBMAIL messageblocks integrity...
Ok. Found [0] unconnected messageblks.

Repairing DBMAIL physmessage integrity...
Ok. Found [0] unconnected physmessages.

Repairing DBMAIL message integrity...
Ok. Found [0] unconnected messages.

Repairing DBMAIL mailbox integrity...
Ok. Found [0] unconnected mailboxes.

Repairing DBMAIL for NULL messages...
Ok. Found [0] NULL messages.

Deleting messages with DELETE status...
Ok. [203] messages deleted.

Setting DELETE status for deleted messages...
Ok. [1] messages set for deletion.
Re-calculating used quota for all users...
Ok. Used quota updated for all users.

Vacuuming and optimizing database...
Ok. Database cleaned up.

Maintenance done. No errors found.

Regards,
Uwe
> 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)
>
> Thank you
> Sim
> _______________________________________________
> DBmail mailing list
> DBmail[at]dbmail.org
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>

_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

sim at compulab

Apr 7, 2008, 5:06 AM

Post #4 of 6 (416 views)
Permalink

Re: data loss [In reply to]

What I'm hearing is that this data loss is normal???
How do such things happen?

Sim

_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

Apr 7, 2008, 5:32 AM

Post #5 of 6 (416 views)
Permalink

Re: data loss [In reply to]

Sim,

If you have orphan rows in fromfield or message your schema is incomplete.

There should be a complete set of constraints that prevent such things.

Sim Zacks wrote:
> What I'm hearing is that this data loss is normal???
> How do such things happen?
>
> Sim
>
> _______________________________________________
> DBmail mailing list
> DBmail[at]dbmail.org
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>

--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

Apr 7, 2008, 5:36 AM

Post #6 of 6 (418 views)
Permalink

Re: data loss [In reply to]

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
DBmail[at]dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact lists@gossamer-threads.com