Detecting corruption from RM10 Drag'n'Drop

#1 · 2025-01-26, 2:02 pm

Quote from Tom Holden on 2025-01-26, 2:02 pm
It looks highly likely that RM10's replacement of background GEDCOM export-import with a new, more direct procedure in RM10 is resulting in corruption of data as has been reported in this forum in Opening database in SQLite causes damage?? and in the RootsMagic Community in RM10 Issue with copying and in Splitting Tree to Maternal and Paternal - Records Counts mismatch?. This last reference was posted in August '24 so the issue has likely been around since the release of v10.0.0.
What is surprising is that such corruption has not been widely detected and reported. That may be because it only stands out under special conditions, e.g., dragging "Descendants and their spouses" from a new database to a larger database and maybe only if there is pedigree collapse in the set. Users familiar with their trees will be startled to find impossible parents popping out, marriages to ghosts being added. Otherwise, the corruption may be almost imperceptible in RM, in the Summary sidebar in the Family or Spouses view.
After much trial and error to satisfy myself that the new drag'n'drop is at fault and getting some understanding of the symptoms in the database, I wrote the following script to help detect the more subtle instances. If they exist, there may well be the more glaring ones but they are harder to detect from investigation of the tables. Possibly some correlation of UTCModDates might provide leads but otherwise visual inspection is a recourse.
SELECT DISTINCT
Ch.ChildID
, IIF((SELECT PersonID FROM PersonTable WHERE PersonID=Ch.ChildID),'Y','N') AS CPID
, FatherID
, IIF((SELECT PersonID FROM PersonTable WHERE PersonID=FatherID),'Y','N') AS FPID
, MotherID
, IIF((SELECT PersonID FROM PersonTable WHERE PersonID=MotherID),'Y','N') AS MPID
, FamilyID
FROM ChildTable Ch
LEFT JOIN FamilyTable USING(FamilyID)
WHERE
Ch.ChildID NOT IN (SELECT PersonID FROM PersonTable) --Child is a non-person (no matching PersonID)
OR
(FatherID NOT IN (SELECT PersonID FROM PersonTable) AND FatherID) -- non-zero and non-person
OR
(MotherID NOT IN (SELECT PersonID FROM PersonTable) AND MotherID) -- non-zero and non-person
;
If you've been using RM10 drag'n'drop with the spouses option, I strongly recommend you run the script to see if it flags anything. If it does, you've got a lot of inspecting to do, maybe going back to a previous version. If it does not, you might be lucky but I'd suggest you look in areas that you have dragged in people from another database for the glaring corruptions I've described.
And I'd recommend using GEDCOM export-import with RM10 instead of drag'n'drop until RM10 has been fixed.

It looks highly likely that RM10's replacement of background GEDCOM export-import with a new, more direct procedure in RM10 is resulting in corruption of data as has been reported in this forum in Opening database in SQLite causes damage?? and in the RootsMagic Community in RM10 Issue with copying and in Splitting Tree to Maternal and Paternal - Records Counts mismatch?. This last reference was posted in August '24 so the issue has likely been around since the release of v10.0.0.

What is surprising is that such corruption has not been widely detected and reported. That may be because it only stands out under special conditions, e.g., dragging "Descendants and their spouses" from a new database to a larger database and maybe only if there is pedigree collapse in the set. Users familiar with their trees will be startled to find impossible parents popping out, marriages to ghosts being added. Otherwise, the corruption may be almost imperceptible in RM, in the Summary sidebar in the Family or Spouses view.

After much trial and error to satisfy myself that the new drag'n'drop is at fault and getting some understanding of the symptoms in the database, I wrote the following script to help detect the more subtle instances. If they exist, there may well be the more glaring ones but they are harder to detect from investigation of the tables. Possibly some correlation of UTCModDates might provide leads but otherwise visual inspection is a recourse.

SELECT DISTINCT
Ch.ChildID
, IIF((SELECT PersonID FROM PersonTable WHERE PersonID=Ch.ChildID),'Y','N') AS CPID
, FatherID
, IIF((SELECT PersonID FROM PersonTable WHERE PersonID=FatherID),'Y','N') AS FPID
, MotherID
, IIF((SELECT PersonID FROM PersonTable WHERE PersonID=MotherID),'Y','N') AS MPID
, FamilyID
FROM ChildTable Ch
LEFT JOIN FamilyTable USING(FamilyID)
WHERE
Ch.ChildID NOT IN (SELECT PersonID FROM PersonTable) --Child is a non-person (no matching PersonID)
OR
(FatherID NOT IN (SELECT PersonID FROM PersonTable) AND FatherID) -- non-zero and non-person
OR
(MotherID NOT IN (SELECT PersonID FROM PersonTable) AND MotherID) -- non-zero and non-person
;

If you've been using RM10 drag'n'drop with the spouses option, I strongly recommend you run the script to see if it flags anything. If it does, you've got a lot of inspecting to do, maybe going back to a previous version. If it does not, you might be lucky but I'd suggest you look in areas that you have dragged in people from another database for the glaring corruptions I've described.

And I'd recommend using GEDCOM export-import with RM10 instead of drag'n'drop until RM10 has been fixed.

Uploaded files:

SuspectChildParent.sql

#2 · 2025-01-27, 12:59 pm

Thanks so much for all of the work you've put into this issue.

I've never had the need to use the drag 'n drop feature, and my DB passes your script's test, but I'm really concerned about how far the problem may extend.

Seems to me that this kind of validity test should be part of RM's database tools suite. Of course, if RM used SQLite's referential integrity constraints, fewer tests would be needed.

Any comment from RM Inc?

kevync has reacted to this post.

#3 · 2025-01-27, 1:48 pm

but I'm really concerned about how far the problem may extend.

"we don't know what we don't know " might apply. Sorry -- since is not beta -- that seems quite sloppy

#4 · 2025-01-27, 2:06 pm

Quote from Richard Otter on 2025-01-27, 12:59 pm
Thanks so much for all of the work you've put into this issue.

I'm feeling punch-drunk from trying this and that and realising how rusty I've gotten with SQLite.

I've never had the need to use the drag 'n drop feature, and my DB passes your script's test, but I'm really concerned about how far the problem may extend.

There's the unanswered question of how dangerous it is to one's data, having found that the Clean Phantom Records tool deletes what my script detects and surmised that there are or could be corruptions that the script cannot.

Seems to me that this kind of validity test should be part of RM's database tools suite. Of course, if RM used SQLite's referential integrity constraints, fewer tests would be needed.

Agreed. I've complained since early days about RM's propensity to leave detritus behind and I like to think that the Clean Phantoms and Enhanced Database Properties features are a direct result of SQLite scripts we've published here.

Any comment from RM Inc?

Only to @CherylC's submission of her database last November and in the Community to her original post. I've not submitted anything directly to RM as I'm working with her database and posting to the Community.

kevync has reacted to this post.

#5 · 2025-01-27, 7:24 pm

since I am guess most of use the tools including remove Phantoms -- not sure we would always notice the loss or be aware of loss immediate in most cases. It is concerning that data loss could possibly occur at other times. Lets hope its limited to the drag n drop method.

Kevin

#6 · 2025-01-27, 7:37 pm

Why is Renee still suggesting DragN-drop ?

Uploaded files:

#7 · 2025-01-27, 9:14 pm

Quote from kevync on 2025-01-27, 7:37 pm
Why is Renee still suggesting DragN-drop ?

Because it's the only way to transfer DNA data? Not supported in GEDCOM export-import.

#8 · 2025-01-27, 9:29 pm

I would argue she should advise caution and potential risk.

#9 · 2025-01-28, 3:15 pm

I've come up with a repeatable example that anyone could try described at https://community.rootsmagic.com/t/rm-10-dragndrop-drops-ieds-into-your-database/13073/9?u=tomh

Tell me I'm wrong!

kevync has reacted to this post.

#10 · 2025-01-28, 3:55 pm

Thanks again for your hard work on this!