DLRS throwing Qualified Parent ID duplicate error

[vera-wes]

Occasionally, when DLRS is running - both in Scheduled and Realtime calculation mode - a duplicate value error is thrown with an error message such as:

Apex script unhandled trigger exception by user/organization:

dlrs_AttendanceTrigger: execution of AfterInsert

caused by: System.DmlException: Upsert failed. First exception on row 0; first error: DUPLICATE_VALUE, duplicate value found: dlrs__QualifiedParentID__c duplicates value on record with id: a0Hb000000gHov0: []

Class.dlrs.RollupService.updateMasterRollupsTrigger: line 944, column 1 Class.dlrs.RollupService.handleRollups: line 842, column 1 Class.dlrs.RollupService.triggerHandler: line 307, column 1 Trigger.dlrs_AttendanceTrigger: line 7, column 1

Can't seem to figure out why the Qualified Parent IDs are duplicating, and those records are deleted by the time I navigate to an individual record to try to identify why some aren't deleting quickly enough to avoid dupes, but it's preventing some of the rollups from updating

[afawcett]

Are you updating, inserting or deleting child records via an external process that is chunking in parallel?

[vera-wes]

We have quite a few DLRS rollups, including on records that are parent to these attendance records, but there's no change to records happening in parallel via external process, no.

[afawcett]

Ok it maybe a concurrency issue coalescing around the same parent record via different processes. I've been doing a some research on this via Google, its not that conclusive as to the cause, but i'll keep looking...

[afawcett]

Ok this looks to be the explanation, which links to the docs here. This bit is important... "If the key is matched multiple times, then an error is generated and the object record is neither inserted or updated.". When i look at the definition of the field dlrs__QualifiedParentID__c in the tool it is NOT defined as case sensitive, which ID's are! So you must have some parent record ID's that are identical apart from the case.

[afawcett]

Frustratingly the 'case sensitive' option on the field cannot now be changed, since its been set in a prior release and its one of the aspects the platform does not let you change. At least i know the source of the issue. I have a few options to create a new field and drop the use of the old, but i'll need to think about the upgrade path a bit more.... I'll mark this issue as a priority fix for the next window i have to work on the tool...

[afawcett]

Ah ok, more googling reveals a better fix to make the Id's that go into this field unique, i'll take a look at this, this should be a better fix.

[vera-wes]

That all makes sense - although that bit about case sensitivity locking is annoying (albeit probably more annoying for you than me) - do you have a best guess on timeline for the fix?

[vera-wes]

The rollup triggers are firing as part of the same transaction as another crucial trigger - is there any way to keep any of the rollup triggers from firing without uninstalling the package? Will unchecking "Active" prevent anything from running, or just prevent the rollup from actually being calculated?

[vera-wes]

Also, this error is still being thrown in Scheduled calculation mode. Shouldn't these DLRS transactions be queuing separately rather than running within the trigger transaction if they're scheduled rather than realtime?

[afawcett]

You can disable by unticking the Active checkbox for sure, no need to uninstall the package. . As i work on this tool in my personal time, like many other community contributors, i cannot give a timescale on a fix, however i have marked it as a priority fix for when i get the next window.

[nlabrada1]

Andrew, any update on when the fix for this is coming? I know the time scale piece you mention above, just curious because it is impacting some of my integrations.

[afawcett]

I have been moving home/continent recently, so things have been a little slow, however the dust is settling a bit now, and i'm keen to focus some time on this tool soon! Ideally sometime during the xmas/new year holidays...

[nlabrada1]

Andrew, really appreciate this. It really is an awesome tool (and a life saver!)

[afawcett]

@nlabrada1 Ok so..... i wrote an Apex test to reproduce this before apply the fix. I cannot reproduce it with parent Id's that vary only by case. Which is what i thought was the issue. If my test is working, it now looks like we need to look for another cause.... Meanwhile can you just confirm your on the latest version (per README file)?

[afawcett]

This is the debug output... you can see dlrs__ParentId__c is not only unique but the full 18 digits (case sensitive friendly ID form). This is despite the first 15 characters of the parent Id's deliberately in my test being the same despite the case.

dlrs__LookupRollupSummaryScheduleItems__c:{Id=a01b000000cuXZWAA2, dlrs__ParentId__c=001b0000008RmIDAA0}

dlrs__LookupRollupSummaryScheduleItems__c:{Id=a01b000000cuXZXAA2, dlrs__ParentId__c=001b0000008rmIDAAY}

[afawcett]

Also I think my assertion "So you must have some parent record ID's that are identical apart from the case." is something we should try and check in your case. Can you see about checking that?

[afawcett]

Also can you confirm if you are using Custom Metadata based rollup definitions?

[afawcett]

@vera-wes @nlabrada1 Let me know if either of you can help with my later questions on this?

[staceyeileen]

@afawcett I know this is an old/closed issue but I am continually receiving this same error, and it doesn't look like there was a resolution to it. Should I open a new issue?

This error occurred: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY: dlrs_Student_CompletionTrigger: execution of AfterUpdate caused by: System.DmlException: Upsert failed. First exception on row 2; first error: DUPLICATE_VALUE, duplicate value found: dlrs__QualifiedParentID__c duplicates value on record with id: a1o50000007aOXi: [] Class.dlrs.RollupService.updateMasterRollupsTrigger: line 1023, column 1 Class.dlrs.RollupService.handleRollups: line 892, column 1 Class.dlrs.RollupService.triggerHandler: line 329, column 1 Trigger.dlrs_Student_CompletionTrigger: line 7, column 1.

[aidanlincolnn]

@afawcett I have been receiving the same error about once or twice per day for over a year. If there is any assistance I can provide in helping you solve it, please let me know.

[picaresquity]

I also am receiving this error.

In my situation, I have separate DLRS related to child records meeting different criteria that all roll up to the same Parent record ID (an Account ID). But ALL of these rollups are in Scheduled Mode to avoid this sort of collision issue.

The error is triggering when records from the child object are inserted, even though these records do not meet the criteria that should be triggering the rollup to begin with.

Let me know if I can add any more information that is helpful here. Since my rollups are already in Scheduled mode and the rollup criteria are already written very narrowly, I'm at a loss of what else I can do to fix this.

[staleyr]

@afawcett in our org we recently changed 8 of our DLRS calculations from realtime to incrementally scheduled due to performance. Following this change we're receiving a lot of these errors.

Upon investigation i believe this error is being thrown because the upsert on "scheduledItems" has duplicate records in it. I've been debugging this error and in the debug log it says the upsert DML is about to execute for 16 records, however we only have 8 DLRS calculations. I believe this happens due to recursion in triggers (i don't believe the recursion is in DLRS, i believe it's elsewhere in our native code base). That aside because of how Salesforce triggers works recursion can be unavoidable at times. Wondering if changing the "scheduledItems" from a List to a Set would fix this issue since it'd only have unique values. Not sure if you want to write a unit test to verify this theory first. Happy to help where i can!

[MorganMarchese]

Hi Andrew,

We are still experiencing this issue occasionally during our scheduled nightly roll-up, as recently as yesterday (3/23/2020).

While I understand and appreciate that you work on this in your spare time and that this issue is hard to track down, it would be great if we had a way to work around this. Does DLRS use the AllOrNothing boolean to do inserts/updates? Or should I assume that if I receive this error then the entire rollup failed?

@afawcett

[afawcett]

@MorganMarchese @staleyr @picaresquity @staceyeileen @aidanfowler looking at this now... past comments on ideas to the cause have been most interesting and I'll investigate afresh. I am presently on PTO so a good time to focus.

[afawcett]

@MorganMarchese to answer your Q in the meantime, no DLRS does not use AllOrNothing, basically if you get an error whatever that execution context was doing is rolled back.

[afawcett]

Ok I spent a few hours more on this this afternoon trying to reproduce it and could not.

• Its very interesting that you have 8 DLRS and it attempted to upsert 16 tracking that down i think is going to be key!
• I cannot see how recrusioin would cause this, since it would be two separate upserts and this error only occurs with duplicates in the list given to a given upsert.
• I have also been starring at the logic below, which has an outer and inner loop to build the list of records to upsert. The first loop is over the list in lookups and the second is over masterRecordIds.
• The unique calculated value set on the field dlrs__QualifiedParentID__c is a combo of rollup id and parent/master id and I have confirmed is the 18 character variant needed in case insensitive scenarios.
• Since the masterRecordIds list is unique and the lookups list is also unique. Then as far as I can see I cannot see how the code would result in 16 records if lookups has 8 entries in it and masterRecordIds is unique (its a set)
• That said the logic to calculate the lookups list in the calling methods is complex so maybe I'm missing something?
• So I think the next thing is to nail down a reproduction of this and perhaps let me have a subscriber login and review the debug log in more detail. Would that be ok?

        for(RollupSummary lookup : lookups)
        {
            if(lookup.CalculationMode == RollupSummaries.CalculationMode.Scheduled.name() || scheduleAllRollups)
            {       
            	// For polymoprhic relationships the master Id list maybe mixed types, associated with the correct rollup
                SObjectType parentType = gd.get(lookup.ParentObject);
                if(parentType==null)
                	continue;
                // For scheduled rollups queue the parent Id record for processing
                for (Id parentId : masterRecordIds)
                {
                	if(parentId.getSobjectType() == parentType) {
	                    LookupRollupSummaryScheduleItems__c scheduledItem = new LookupRollupSummaryScheduleItems__c();
	                    scheduledItem.Name = parentId;
	                    scheduledItem.LookupRollupSummary__c = lookup.Record instanceof LookupRollupSummary2__mdt ? null : lookup.Id;
	                    scheduledItem.LookupRollupSummary2__c = lookup.Id;
	                    scheduledItem.ParentId__c = parentId;
	                    scheduledItem.QualifiedParentID__c = parentId + '#' + lookup.Id; 
	                    scheduledItems.add(scheduledItem);
	                    if(lookup.Record instanceof LookupRollupSummary2__mdt) {
	                 		scheduledItemsForMDTs.add(scheduledItem);   	
	                    }
	                }
                }                   
            }
            else if(lookup.CalculationMode == RollupSummaries.CalculationMode.Realtime.name() ||
            	lookup.CalculationMode == RollupSummaries.CalculationMode.Developer.name())
            {
                // Filter realtime & Developer lookups in order to generate LRE contexts below
                runnowLookups.add(lookup);
            }
        }

[afawcett]

Ok i was just above to call it a day with my investigation and tried a few other Google searches coming back to my original assertion that this might be a concurrency issue... and found this... Race condition in Salesforce upsert operations may cause DUPLICATE_VALUE error. I know you have all said its not a concurrency issue though? So again I think its back to solid reproduction of this issue to really make progress here. Really sorry this is not better news everyone.

[afawcett]

So in summary...

• if this is a concurrency issue, the workaround suggest in the Salesforce KB above to do a 'retry' is tricky ... given its a sync apex trigger operation this is running in. And Apex has not "wait" (by design) operation to wait and then retry. We could get inventive somehow and do the unique aggregation async but thats quite an architecture change (not necessary impossible though).
• if this is a duplicate records in the list issue, we for sure can I feel envision a fix here!

Either-way we need a reproduction to really progress as its not really good practice to make speculative fixes, especially in a package used by so many. However... maybe an experimental code path (assuming it's the duplicate records in list issue) could be considered.

Andy

[aotolbert]

@afawcett I am having this issue with DLRS as well and I would be willing to provide any additional information about our use case to aid in resolving this particular issue. We have tried a number of different attempted fixes to no avail and have had to implement crude workarounds to ensure proper data flow

[dwhitig]

@afawcett we also have started to get this error on one of our rollups consistently on the same record. Happy to work with you if you want as an example. Thanks!