Profiles nightly jobs failing on duplicate key

[sn...@bsd.uchicago.edu]

Running ProfilesRNS 2.9.

Our jobs have been failing for approximately 1 week+ on the following error:

Fails in [Framework.].[RunJobGroup] @JobGroup = 4 with error

Cannot insert duplicate key row in object 'RDF..Node' with unique index 'idx_ValueHash'. The duplicate key value is (0xc0ff6d999aa0276692fcf1ac038b8bbf12146ae7). 

We have seen this error before so re-ran the following to attempt to 'fix' the keys, trying both correcting the individual record and finally the mass update as detailed below:

declare @baseURI varchar(100)

select @baseURI = value from [Framework.].Parameter where ParameterID = 'baseURI'

select * into #tmp 

from [RDF.].Node 

where value like @baseURI + '%' 

and value not like '%Modules/CustomViewPersonGeneralInfo/PhotoHandler.ashx%'  

and value <> @baseURI + cast(nodeID as varchar(50))

 

update [RDF.].[Node] 

set Value =  @baseURI + cast(nodeID as varchar(50)), 

ValueHash = [RDF.].[fnValueHash](Language,DataType, @baseURI + cast(nodeID as varchar(50))) 

where nodeid in (select NodeID from #tmp)

UPDATE m

SET m.ValueHash = n.ValueHash

FROM [RDF.Stage].InternalNodeMap m, [RDF.].[Node] n

WHERE m.NodeID = n.NodeID and m.NodeID in (select NodeID from #tmp)

drop table #tmp

Now the above procedure worked for us ~1 year ago when we had seen this behavior previously, now no joy.

Next steps to try?

Thanks, Stephan

[Brown, Nicholas William]

Stephan

 

I assume that it is processdatamap that is failing (that should be the only job adding rows to RDF..Node). You can check this by looking in Framwork..Job to see where if the processdatamap row is jobgroup 3 has an error. If so, we should be able to see which datamapID is responsible for the failure by looking at the log

 

select top 10 * from [RDF.Stage].[Log.DataMap] order by logID desc

 

If the top row has a null for enddate then that datamapid is probably the problem, if not it is probably the next datamap ID.

 

Look up the class, property and networkproperty for this id in [Ontology.].DataMap

 

If you sent me these values I may be able to suggest some further troubleshooting steps.

 

Nick

--
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/f8e7447b-3745-41c1-a68a-fb8775494786n%40googlegroups.com.

[Moisey Gruzman]

we have problem with duplicated keys  in Nightly executing Private or Public "UpdateCache" jobs. Number of these failures is growing

in last month we reported 6 times. We are trying reindexing DB tables and it may be help for next Nightly run, but the failure comes againg in several runs. 

Any suggestions?

Profiles uc_Nightly has failed on SFPRF-PDB01 on Sep 29 2023  1:58AM . executing EXEC [Search.Cache].[Private.UpdateCache] with Violation of PRIMARY KEY constraint 'PK__#NodePre__390E9BB834BFE443'. Cannot insert duplicate key in object 'dbo.#NodePrefix'. The duplicate key value is (0.000154223, 421245834).

 

Profiles uc_Nightly has failed on SFPRF-PDB01 on Sep 24 2023  1:56AM . executing EXEC [Search.Cache].[Public.UpdateCache] with Violation of PRIMARY KEY constraint 'PK__#NodePre__390E9BB89252F3F7'. Cannot insert duplicate key in object 'dbo.#NodePrefix'. The duplicate key value is (Classic Texts/Latin American Studies, 114136399).

 

Profiles uc_Nightly has failed on SFPRF-PDB01 on Sep 14 2023  2:05AM . executing EXEC [Search.Cache].[Public.UpdateCache] with Violation of PRIMARY KEY constraint 'PK__#NodePre__390E9BB81D09A632'. Cannot insert duplicate key in object 'dbo.#NodePrefix'. The duplicate key value is (0000-0003-1417-1188, 385384270).

 

Profiles uc_Nightly has failed on SFPRF-PDB01 on Sep  8 2023  2:04AM . executing EXEC [Search.Cache].[Private.UpdateCache] with Violation of PRIMARY KEY constraint 'PK__#NodePre__390E9BB88D48DD2D'. Cannot insert duplicate key in object 'dbo.#NodePrefix'. The duplicate key value is (https://urldefense.com/v3/__https://researcherprofiles.org/profile/423247046__;!!LQC6Cpwp!vfyoLlp6wa_KJc-G-Hp5SwxZ9atdjq3ovCI2jwzEloec_L2-S4Obt003W3Il5UfK5mCyOezIYT7EKXZo9VKq$ , 423247046).

 

Profiles uc_Nightly has failed on SFPRF-PDB01 on Aug 31 2023  2:37AM . executing EXEC [Search.Cache].[Public.UpdateCache] with Violation of PRIMARY KEY constraint 'PK__#NodePre__390E9BB80E5B824F'. Cannot insert duplicate key in object 'dbo.#NodePrefix'. The duplicate key value is (, Integrative Sciences (Computational Biology), 396151599).

 

Profiles uc_Nightly has failed on SFPRF-PDB01 on Aug 17 2023  1:25AM . executing EXEC [Search.Cache].[Public.UpdateCache] with Violation of PRIMARY KEY constraint 'PK__#NodePre__390E9BB89198B704'. Cannot insert duplicate key in object 'dbo.#NodePrefix'. The duplicate key value is (Claire is an Assistant Clinical Research Coordinator working with the PET Core team in the Rabinovici Lab. She was born and raised in the Bay Area and graduated from University of California, Santa Cruz in 2021 with a Bachelor of Arts in Psychology. Claire was previously an undergraduate research assistant in the High Level Perception Lab and Samaha Lab at UC Santa Cruz, where she worked on projects studying music cognition and the neural basis of confidence in decision making., 323757519).