RootsMagic Database Verification

RootsMagic
,
1

I’ve been working on a tool to identify errors in RootsMagic databases. My preference would be RM add this to tools, but near term I can see a large number of database errors that database tools aren’t catching ..

AncestryTable - LinkId 25277 for AncestryId 862810362099:9000:198638252 points to invalid CitationID 113422
AncestryTable - LinkId 25295 for AncestryId 862810363323:9000:198638252 points to invalid CitationID 113435
AncestryTable - LinkId 68539 for AncestryId 862810319080:9000:198638252 points to invalid CitationID 13390
AncestryTable - LinkId 213394 for AncestryId 862998296654:9000:198638252 points to invalid CitationID 56627
AncestryTable - LinkId 215880 for AncestryId 863015934920:9000:198638252 points to invalid CitationID 154577
AncestryTable - LinkId 215924 for AncestryId 863015972364:9000:198638252 points to invalid CitationID 154583
AncestryTable - LinkId 215935 for AncestryId 863015973020:9000:198638252 points to invalid CitationID 154577
AncestryTable - LinkId 215955 for AncestryId 863016191185:9000:198638252 points to invalid CitationID 154583

I would assume these can all be deleted, but I feel RM should delete these when publishing to Ancestry.

AncestryTable - LinkId 44145 for AncestryId F1E127E3A8704520A03DFECAB407079D points to invalid MediaId 9
AncestryTable - LinkId 44146 for AncestryId 21AEBC49E2BD4F4EA9826CD937364F71 points to invalid MediaId 5
AncestryTable - LinkId 44147 for AncestryId 33753C53A004488AB138AD1FA95C3E84 points to invalid MediaId 4
AncestryTable - LinkId 58470 for AncestryId B71FBD29E1E8402DAFF5C847B3D72EBE points to invalid MediaId 16537
AncestryTable - LinkId 58471 for AncestryId 61643DFC70A74A1BBDE284CB7C7EB256 points to invalid MediaId 16536
AncestryTable - LinkId 58472 for AncestryId 7D4E563507E44EA4959C4754E4CF6D31 points to invalid MediaId 16535
AncestryTable - LinkId 58474 for AncestryId CBBB266571854F5D8AB28B6C23163512 points to invalid MediaId 16533
AncestryTable - LinkId 58517 for AncestryId E134D214BA274A0999646E5AD940112D points to invalid MediaId 16576

I would assume these can all be deleted, but I feel RM should delete these when publishing to Ancestry.

CitationLinkTable - LinkId 132509 for Citation 132441 points to invalid EventId 112686

I would assume these can all be deleted, but I feel RM should delete these when removing phantoms.

CitationLinkTable - LinkId 68132 for Citation 40 points to invalid NameId 9655
CitationLinkTable - LinkId 68134 for Citation 40 points to invalid NameId 9656
CitationLinkTable - LinkId 68135 for Citation 40 points to invalid NameId 9656
CitationLinkTable - LinkId 102269 for Citation 102269 points to invalid NameId 21992
CitationLinkTable - LinkId 102766 for Citation 102766 points to invalid NameId 22053
CitationLinkTable - LinkId 111215 for Citation 111215 points to invalid NameId 26398
CitationLinkTable - LinkId 111223 for Citation 111223 points to invalid NameId 26398

I would assume these can all be deleted, but I feel RM should delete these when removing phantoms.

CitationTable - CitationId 116255 points to invalid SourceId 0
EventTable - EventId 105925 points to invalid Event Type 0
EventTable - EventId 105929 points to invalid Event Type 0
EventTable - EventId 90306 points to invalid Event Type 0

These are obvious errors, but some meaningful way to correct them needs to exist.

EventTable - EventId 89444 points to invalid Place Details Id 15710
EventTable - EventId 89448 points to invalid Place Details Id 15711
EventTable - EventId 100531 points to invalid Place Details Id 16872
EventTable - EventId 100536 points to invalid Place Details Id 16874
EventTable - EventId 100795 points to invalid Place Details Id 17006

I would assume these can all be deleted, but I feel RM should delete these when removing phantoms. I expect the source to lie in some 16 bit integers in UI.

EventTable - EventId 100828 points to invalid Place Details Id 17013
EventTable - EventId 108906 points to invalid Place Details Id 19456
EventTable - EventId 121633 points to invalid Place Details Id 22842
EventTable - EventId 126288 points to invalid Place Details Id 23467
EventTable - EventId 126304 points to invalid Place Details Id 23472

I would assume these can all be deleted, but I feel RM should delete these when removing phantoms.

FamilyTable - FamilyId 228 points to invalid Child PersonId 485
FamilyTable - FamilyId 9050 points to invalid Child PersonId 11033
FamilyTable - FamilyId 15768 points to invalid Child PersonId 43162
FamilyTable - FamilyId 16112 points to invalid Child PersonId 44057
NameTable - NameId 37318 (, ) for Person 0 points to invalid PersonId

These are obvious errors, but some meaningful way to correct them needs to exist.

PlaceTable Chester, South Carolina, United States (265) for MasterId 16777219 points to invalid PlaceId
PlaceTable Tarrant, Texas, United States (352) for MasterId 16777219 points to invalid PlaceId
PlaceTable South Reading, Wakefield, Massachusetts (365) for MasterId 16777219 points to invalid PlaceId
PlaceTable Clay, Hancock, West Virginia, United States (425) for MasterId 16777219 points to invalid PlaceId
PlaceTable Lagrange, Wyoming, Pennsylvania, United States (561) for MasterId 16777219 points to invalid PlaceId

Now these are clearly 16 bit errors, where a integer overflowed and went negative, invalidating the original reference.

PlaceTable Troy, Rensselaer, New York, United States (21250) for MasterId 32727 points to invalid PlaceId
PlaceTable Stamford, Delaware, New York, United States (21255) for MasterId 32652 points to invalid PlaceId
PlaceTable Nottinghamshire, England (21268) for MasterId 32652 points to invalid PlaceId
PlaceTable Nottingham, Nottinghamshire, England (21271) for MasterId 32652 points to invalid PlaceId
PlaceTable Huntington, Fairfield, Connecticut, United States (21281) for MasterId 32652 points to invalid PlaceId
PlaceTable Stratfield, Fairfield, Connecticut, United States (21286) for MasterId 32652 points to invalid PlaceId

You can see this clearly in placed issues batched around 32767.

I would like everyone’s opinion on the best way to deal with these outside RM while RM works to resolve these issues.

1 Like
2

Not sure If did same things but I did find a small handful using:

SELECT EventID, OwnerID, OwnerType, EventType
FROM EventTable
WHERE EventType = 0 OR EventType IS NULL;

they will not always appear at bottom (if they have a sort date they could be in middle) – I have Witness these being created. This seems to be a combination of user and RM but no program should be entering invalid data –

image
image713×176 4.78 KB

image
image914×193 6.43 KB

This Happens when the Fact type is blank – sometimes other info will be link – other times maybe not

image
image1646×573 40.2 KB

I dont think I had any of the others except for Ancestry but my current db is not connected so that was interesting despite being only ONE row

3

You example got me thinking though – I wonder if there type of errors might contributing to “Access Violations Errors” or similar

4

There are likely multiple sources for access violations, one of them being these database errors. I also know that certain special characters and line endings cause them.

1 Like
5

agreed – not limited to database – but I more wondering by rank if a larger portion came from such db errors – and if a user could fix them more easily (not needing outside tools) then that would be help — but at same time I understand RM would not want an overaggressive tool that clean up too much.(Hard balance in many cases)

in my example of

SELECT EventID, OwnerID, OwnerType, EventType
FROM EventTable
WHERE EventType = 0 OR EventType IS NULL;

those needed to be fixed NOT removed (except for one null/blank) they needed to be corrected – I think what happened on those is when RM did catch the FACT TYPE that was selected then it is saved with a blank/null one.