failed update 2.11 to 2.11.1
sn...@bsd.uchicago.edu
Meeks, Eric
Looks like you have some duplicate values in the FacultyRankSort column of the [Profile.Data].[Person.FacultyRank] table, or maybe you have a NULL in that column. Just clean that up with real unique numeric values and you should get past this.
When you put values in, do so such that
SELECT * FROM [Profile.Data].[Person.FacultyRank] order by FacultyRankSort
puts things in the order in which you want them to appear in the dropdown. We redid ours a few years ago and did it in increments of 10 so that we could squeeze more in if we ever wanted to without having to alter the others, so instead of 1,2,3,… we did 10,20,30,… but really, anything that sorts them correctly is fine.
Eric
From: profi...@googlegroups.com <profi...@googlegroups.com>
On Behalf Of sn...@bsd.uchicago.edu
Sent: Tuesday, November 22, 2022 1:00 PM
To: ProfilesRNS <profi...@googlegroups.com>
Subject: [ProfilesRNS] failed update 2.11 to 2.11.1
We're in the process of upgrading from 2. 9 to latest. Unfortunately we've run into an issue between 2. 11 and 2. 11. 1 as follows when running ProfilesRNS_Upgrade_Schema. sql. The script encountered an error building two indexes due to unique key
ZjQcmQRYFpfptBannerStart
|
ZjQcmQRYFpfptBannerEnd
--
You received this message because you are subscribed to the Google Groups "ProfilesRNS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
profilesrns...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/profilesrns/b7cff443-43e7-49ae-a937-09e8e04510d6n%40googlegroups.com.
Noe, Stephan [BSD]
Eric –
Now a little more confused.
We have 3,544 rows in [ProfilesRNS].[Profile.Data].[Person.FacultyRank] which doesn’t seem to be consistent with the use case I’m assuming based on your response. To my knowledge we’ve never touched that table directly in any way.
In [ProfilesRNS].[Profile.Import].[PersonAffiliation] which we do load into, we have 3,186 rows, with exactly one row per faculty, with primaryaffiliation and affiliationorder always 1, and facultyrank and facultyrankorder always null if that matters.
Stephan
“This message was received from outside of the organization. Please pay special attention and practice care when clicking on any links, or providing any information to the sender. Cyber attacks commonly attempt to trick you in to thinking the sender is a reputable individual who you can trust.”
--
You received this message because you are subscribed to a topic in the Google Groups "ProfilesRNS" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/profilesrns/7rufde7Lps8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
profilesrns...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/profilesrns/BYAPR05MB5560B43E02E881FF3DE18C2EE50D9%40BYAPR05MB5560.namprd05.prod.outlook.com.
Meeks, Eric
Yeah, having NULL in facultyrank in the [Profile.Import].[PersonAffiliation] is likely messing things up. I’d try putting in an empty string for facultryrank and setting the facultyrankorder to 10 or some constant for everyone. You’ll need to clean out all the null entries in Person.FacultyRank as well. Once you get stuff cleaned up you can set the Visible flag to 0 in Person.FacultyRank so it doesn’t mess with the UI. Once all this is done you should just have the one empty string entry in Person.FacultyRank and it would be wired to everyone.
Eric
From: profi...@googlegroups.com <profi...@googlegroups.com>
On Behalf Of Noe, Stephan [BSD]
Sent: Tuesday, November 22, 2022 2:24 PM
To: profi...@googlegroups.com
Subject: Re: [ProfilesRNS] failed update 2.11 to 2.11.1
Eric – Now a little more confused. We have 3,544 rows in [ProfilesRNS]. [Profile. Data]. [Person. FacultyRank] which doesn’t seem to be consistent with the use case I’m assuming based on your response. To my knowledge we’ve never touched that table
To view this discussion on the web visit https://groups.google.com/d/msgid/profilesrns/PH0PR04MB727182520CE2E4EEC273723BF10D9%40PH0PR04MB7271.namprd04.prod.outlook.com.
Brown, Nicholas William
In version 2.11.1 we added unique constraints to 7 tables. Some of the queries in the dataloading process have joins that cause a huge amount of incorrect data to be created in profiles when certain fields are not unique. This is all described in the install guide, and tested for in the [Profile.Import].[ValidateProfilesImportTables] procedure, but after helping a couple of institutions fix issues caused by data errors, I decided to add the constraints, which cause the data load transaction to roll back when the data fails these constraints.
You shouldn’t have any problems having facultyrank and facultyranksort null for everyone. This is fine in profiles. You may run into issues if facultyrank is null for some users and not null for others. What it sounds like happened here is that you used to load facultyrank into profiles, and you no longer do (again this should be fine) but you had some duplicate null values in the facultyrank tables. These probably didn’t cause any issues in profiles as the joins wouldn’t have matched the nulls anyway. Hence why this was never noticed. Now you are applying the uniqueness constraint, and this is breaking the constraint.
There are a couple of fixes you can make for this:
- You can run the rest of the upgrade without adding the uniqueness constraint, not having the constraint won’t affect the running of profiles, and you are not loading facultyrank, so you are not going to load any bad data.
- You can delete the unused facultyranks from the facultyrank table
delete from [Profile.Data].[Person.FacultyRank] where FacultyRankID not in (select distinct FacultyRankID from [Profile.Data].[Person.Affiliation])
If this doesn’t work, you probably have inactive users with facultyrank values, you would need to fix this by updating their facultyrankID values in [Profile.Data].[Person.Affiliation] to the value for the null facultyranks (I’m not sure if this would be a null or a value) and then run EXEC [Profile.Cache].[Person.UpdatePerson] and EXEC [Profile.Cache].[Person.UpdateAffiliation]
Nick
To view this discussion on the web visit https://groups.google.com/d/msgid/profilesrns/BYAPR05MB5560B84676772B9A46E3CFD5E50D9%40BYAPR05MB5560.namprd05.prod.outlook.com.
Noe, Stephan [BSD]
Sorry the delay. None of the suggestions allowed us to create the constraints.
In [Profile.Import].[PersonAffiliation] we have 3,175 rows, with 73 distinct ‘titles’ and in every case FacultyRank, FacultyRankOrder both set to null.
In [Profile.Data].[Person.Affiliation] we likewise have 3,175 rows, 73 distinct ‘Title’ and FacultyRankID always null.
In [Profile.Data].[Person.FacultyRank] we have 3,559 rows(?) with FacultyRank, FacultyRankSort always null.
My DBA was wondering if we could truncate [Person.Affiliation], [Person.FacultyRank], safely and re-run the jobs to see the outcome?
Would we be correct in assuming that we’d expect to see 73 rows in [Profile.Data].[Person.FacultyRank]?
I should also add we just realized we have 4,103 rows in [Profile.Import].[Person] but only 3,175 rows in [Profile.Import].[PersonAffiliation]. [Profile.Import].[PersonFilterFlag] has 4,103 rows. Our best guess here is the original maintainer populated person, personaffiliation, and personfilterflag tables out of band in a somewhat ad hoc manner some years ago, we’ve since automated that process and all three tables should be populated in lockstep. Unfortunately I don’t have a good data source to backfill the missing information for the outliers.
Thanks,
Stephan
To view this discussion on the web visit https://groups.google.com/d/msgid/profilesrns/MN2PR07MB61448EC886E6DB7570FD47BDA60C9%40MN2PR07MB6144.namprd07.prod.outlook.com.
Brown, Nicholas William
You should be able to truncate that table and rerun the jobs.
If you have more records in the Profile.Import.Person table than the Profile.Import.PersonAffiliation table, I would expect that you would run into issues. I would recommend deleting (or setting isActive = 0) in the Profile.Import.Person table for anyone without a corresponding record in the PersonAffiliation table.
Nick
To view this discussion on the web visit https://groups.google.com/d/msgid/profilesrns/PH0PR04MB7271FFD186BEBA38D7F2B1FBF11A9%40PH0PR04MB7271.namprd04.prod.outlook.com.