Data integrity problem in PlaceTable
I've just discovered the first bit of data corruption or referential integrity failure in my ver 9.0.3 database.
I'm not sure what to call the problem, I don't think it can be strictly called either corruption or referential integrity. It is just not following the rules set by RM and there is no index to enforce the rule.
My understanding of the PlaceTable is that PlaceType can take one of three values-
0 normal top level places
1 LDS places
2 detail places
TypeID=2 places use the MasterID to specify which master place they are a detail for.
TypeID=0 and TypeID=1 are both top level, so their MasterID values should be 0.
What I found were 175 place records of Type=0 that had non zero MasterID values.
I found the problem because 26 of the apparently random MasterID had a small integer value that happened to point to a particular real master place in the table. As I was browsing through the Places in the RM UI, I notice a top level place that indicated that it had 27 detail places, but when its detail places were displayed, there were none. The reason is now obvious. The count of detail places was just using the MasterID, while the display of detail places also required that the PlaceType=2. Display of top level places ignores MasterID and just checks PlaceType.
This particular issue is trivial to fix, just change those random numbers in those records to zeros.
This has me spooked. I haven't done any scripting involving places, but who knows what is going on with the RMNOCASE issues being discussed here.
Of course, I have experienced innumerable Access Exceptions and without the use of SQL transactions, anything could happen.
Perhaps some other could check their databases-
select MasterID, pt.Name
from PlaceTable as pt
where pt.PlaceType=0 and pt.MasterID <> 0
I suspect the access violations as the root cause and it is similar to what I've inferred is the cause of the rogue name in some views for a spouse that is otherwise reported as "Unknown". I believe that when adding a person to the database, the first step taken by the program is recording the name values in the NameTable. At this point, NameTable.OwnerID is 0. The next step adds a new record to the PersonTable which generates the RIN or PersonID that needs to be plugged into the OwnerID in the NameTable in order to link the Name record to the Person record. If there is a crash prior to that point, the NameTable.OwnerID remains 0. Meanwhile, over in the FamilyTable, every unknown spouse has an ID=0 and, now, in those selected views (not all views and reports), every "Unknown" spouse gets this orphaned name.
I'd bet that there's a parallel with the non-conforming records in your PlaceTable. The program crashed before completing an update of the PlaceTable when adding a Place Detail...
Sounds plausible, except that the numbers in the MasterID seem mostly random except there are many repeats, more like memory corruption.
Anyone gotten a chance to test their database?
Here are the bogus MasterIDs. When a number repeats, the second column says how many times.
1080_______x___3
1363
2560_______x__36
1702088____x__16
1702140
2133248
2660352
2814848______x_4
2820096
2865152______x_15
3082240
3160064
3831808
3987456_______x_5
4029504_______x_2
4114432
4176256
5473920_______x_21
7162544
7429296
8166096
8776704________x_6
9029632
9558368
10550800_______x_4
11769040_______x_3
11895872_______x_2
11999728
12034192
12168608
12810976_______x_11
12879440_______x_3
13587920_______x_4
13702832
14090912
14348928_______x_2
15834608_______x_2
15953872
130103296
130545664
131360768
131495936______x_4
135841792______x_13
136194048
1986229337
I had zero errors from your script, both in RM7 and RM9. However, I am a very poor test case because I quit using Place Details several years ago and removed all the existing ones from my database at that time. I merged all the existing Place Details back into the main Place field. Therefore, 100% of my MasterID's are zero.
I tested a couple of dozen files from RM7 through to RM9, some mine, some from others.
RM7 - none
RM8 - 174 in a file from someone else
6 in a Play file of mine that originated in RM7 which had none
RM9 - the same 6 in the upgrade of that file and preserved through a down conversion from RM9 to RM8 to RM7 and back to RM9