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]

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!

[Molly-Chicago]

@afawcett We are getting the Qualified Parent ID duplicate error today on every online transaction we are running. This worked fine up to yesterday. Rolled back process changes and still the same issue. This is a realtime error when trying to run a process related to generating an opportunity:

_This error occurred when the flow tried to create one or more records: dlrs_OpportunityContactRoleTrigger: 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: a1Y3h0000013taJ: [] (dlrs) .

We've tried turning off all realtime calc modes and still get the error. Now we are needing to deactivate DLRS. The last time we saw an issue like this, we were able to get around it by setting the automation to run as "SYSTEM" due to a guest user that kicks off the process. That is no longer helping.

[Molly-Chicago]

An update - we were able to eliminate the error after disabling all of the OpportunityContactRole rollups. We then started adding them back in one at a time to see what would happen. We found that if we changed the "scheduled" rollups to "realtime", and left the "process builder" rollups alone, that the error no longer occurs. That let us reactivate all of them. We still don't know the root cause, but perhaps these clues will help.

[daniel-j-d]

It is possible that I have found a root cause of this. In my case this error occurs only for the guest users of a community. The guest users cannot edit the records (it is not possible to grant the 'Edit' permission on a profile level) and the RollupService class is declared as with sharing. When the LookupRollupSummaryScheduleItems__c are upserted for the first time everything works fine but when the code is more complex and some other record is created that has a common parent with the one that originally triggered the recalculation then RollupService tries to update the schedule item (which is not allowed) and it throws the error. The fix for the RollupService would be to upsert the schedule items from a class that is declared as without sharing or to check if the recalculation was already scheduled for the given records and skip them.

[dwhitig]

That's interesting Daniel. I have been having the problem and it was with an object that uses the Guest User. That does sound like it may be the issue for me as well!

…

On Fri, Jul 30, 2021 at 10:28 AM daniel-j-d ***@***.***> wrote: It is possible that I have found a root cause of this. In my case this error occurs only for the guest users of a community. The guest users cannot edit the records (it is not possible to grant the 'Edit' permission on a profile level) and the RollupService class is declared as with sharing. When the LookupRollupSummaryScheduleItems__c are upserted for the first time everything works fine but when the code is more complex and some other record is created that has a common parent with the one that originally triggered the recalculation then RollupService tries to update the schedule item (which is not allowed) and it throws the error. The fix for the RollupService would be to upsert the schedule items from a class that is declared as without sharing or to check if the recalculation was already scheduled for the given records and skip them. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#375 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABCP7OQPNQW4AXBYM3P5ICLT2KZIXANCNFSM4CMLQOGQ> .

-- Donna Whitig, PMP Certified Salesforce Administrator Salesforce Delaware Valley Nonprofit User Group Leader Direct Line: (484) 639-9358

[rob-alexa]

I am also experiencing this issue with a community guest user.

[MZvejs]

Maybe this helps someone having this issue. Wasn't profile related.
We have a LWC with 5 tables displaying some records. Users enter some data and press save. Due to an oversight each of the 5 tables was calling a save method to save their data to the database. A part of this save method was a delete operation for the old records. The key problem was that the delete operation would try to delete the same data 5 times (once for each table calling the save method which contains the delete operation). The tables call the save operation in quick succession so the delete operations were somewhat overlapping. What I imagine happened with DLRS was that it tried to create some records for it's own use for each of the records being deleted, but since the deletes were overlapping DLRS would create duplicates and then fail to insert them, throwing this error.
Fix for us was to run the save method once for all the records of all 5 tables, meaning the delete operation would run only once and then proceed to save new records. Alternative would have been to run the delete once before the save method runs once for each of the 5 tables.

[msakarettnaborg]

I am also experiencing this issue with a Community Guest User.

Edit - a non-community regular business user just encountered this issue.

[Filikin]

I also have this issue with scheduled rollups - the records are inserted using a flow which is run from a site community guest user. Disabling all my scheduled rollups fixed the issue - time to write a trigger :)
I don't understand why a scheduled lookup would trigger an error in real time

[peneloperodie]

Coming to comment that I'm experiencing the same issue on scheduled rollups - on each it's the after update that's causing the error so in my case I've removed that from the triggers since I can't see anything in our use case that wouldn't be covered by the before update trigger anyway :)

[ValouV]

Also commenting that i have that issue on scheduled rollups.

[pandeygyan]

We're having similar issues as well. As the dlrs__QualifiedParentID__c field is constructed using two 18 character (case insensitive) ids it's almost impossible to be caused as a result of two ids with same characters only differing by casing; reason, there shouldn't be duplicate 18 character ids.

So, it’s almost certain that it's caused by a race condition. We also noticed a related issue where where the code complaining " Record has been deleted with dlrs__QualifiedParentID__c 0xxxxxxx....". I think this is also caused by the race condition where scheduled apex processing the lookup rollup scheduled item records and deleting the processed items and rollup is trying the upsert a lookup rollup summary scheduled item for the same lookup and same parent.

Now the question is, why do we need to use upsert operation at all? dlrs__QualifiedParentID__c is a unique external id, could we not just use insert like below ?
Database.insert(scheduledItems, false);

We only care about the existence of the lookup rollup summary scheduled item records, not when the records were created/updated. Right?

@afawcett @aheber

[aheber]

@pandeygyan I'm not sure I understand the question about upsert vs insert.

No the system doesn't care about the audit timestamps but the system uses upsert to try and avoid having multiple records generated for the same parent on the same rollup. We use Upsert so if the scheduled item for that parent already exists then we don't create a second rollup for that same record.

I do agree it is likely a race condition where multiple concurrent actions are mutating the same records and between the time that Salesforce's code pulls the record for comparison and when it commits the database record someone else snuck in and changed the state.

When we execute a DML upsert, Salesforce's code has to see which of those uniqueIDs are already present vs. what is new. Presumably between the time that Salesforce's internal query started executing, found results, matches those results to our upsert requested records, and tries to commit those records to the database. Something else finished it's similar action using a common unique id. Now when Salesforce, in our transaction, has already decided it needs to create records because the UniqueIds didn't exist, it tries to commit that database change but the database says "I'm sorry, that unique id is already in use" even though it was our match point so how could it exist and not have been found the first time.

That is a theory based on other similar problems I've investigated. I'm not sure how we can deal with it because we can't claim a record lock on records that don't exist yet and we don't have a reasonably mechanism in the current code to make scheduled item creation single-threaded. (Using Platform Events or CDC could fix that but we aren't adding that quite yet)

Maybe if DLRS had logic that simply retried once, that can be a costly decision and wouldn't be made lightly.

In my perfect world I think the right solution is going to be to try once in the current transaction, if it fails for any reason then we try and make the change using asynchronous code. There are a lot of gotchas to that type of design, including being aware the current context and whether spawning a new Queueable or similar is a good idea. For instance, spawning a new Queueable from inside another Async context is risky and is prone to causing a significant number of additional bugs.

[pandeygyan]

Thanks for the quick reply @aheber!

Our goal is to avoid having multiple records for the same parent and the same lookup rollup summary. As we have QualifiedParentID as unique, external id, which is constructed by the concatenation of parentId and the lookup rollup summary Id. Using just Database.insert (scheduledItems, false) will prevent the records having the QualifiedParentID if those are already in the database/object (because QualifiedParentID is unique) but allows the new ones. No?

[aheber]

Meaning we could perform an insert and anything that comes back as duplicates, resulting in an error, is then discarded as unnecessary?
It could work, I don't know if there would be any additional problems. Using upsert and discarding duplicate value errors would have the same effect except we'd have a lot less errors to worry about, but it would be essentially the same code to evaluate and discard those.
I don't know if we'd be missing out on any other necessary data points in those events but there might be an edge case that wouldn't work well, we could probably work around it though.

If during the commit, that record was deleted because it was processed, our commit could return DUPLICATE though it was actually deleted. This could leave that update without getting processed and without a scheduled item to queue that process. I'm not sure how often this would happen but it would have approximately the same chances as any of these other errors showing up. Just poor timing all around. We could try and work around that situation with database locks, but I'd be real worried about locking on those records, it might create more errors and slowness than these errors are causing.

A lot of the fear I have trying to mitigate this is the inability to reproduce it on demand. How will we know we have it fixed, and haven't created other problems, if we can test these scenarios on demand. I'm mostly thinking out loud with this but maybe discarding duplicate record messages, or at least confirming the records are currently present with a lock for only those records would be the right idea. Give us some measured confidence. I just don't know how get more confidence in the safety of making that change.

[drmordica]

@aheber was this issue ever resolved? We have started seeing this error in one of our sandboxes from our integration and cant determine what the cause is. There clearly is no duplicate scheduled item record that exist, at least from what we can tell and yet it is still throwing the error.

[aheber]

@drmordica we haven't been working on implementing any changes related to this ticket. The team is actively working on a new UI for rollup configuration management. Once that is done in the next month or so then we'll be reviewing open issues again to see what we can replicate and get fixed.

[CoachT13]

I am also running into this issue as of late. It appears as though the schedule items are not being cleared/removed/deleted when the scheduled DLRS is run (we have a nightly job scheduled). When an action/task causes a similar entry in the schedule item list, the conflict/error is thrown because there is already an existing matching Qualified Parent ID.

I would expect that once the schedule has completed the rollup tasks (the DLRS for the associated triggering record), that the schedule item would be cleared so that subsequent activities wouldn't cause this conflict.

This could also occur if there are two triggering events that happen between the scheduled DLRS runs. In that event, I would expect that the DLRS Schedule Item creation would recognize that it's already scheduled to run and would not require an additional entry into the schedule item object. Unless I'm not fully understanding the concept behind the schedule items.

[aheber]

I was able to get this replicated.

If LookupRollupSummaryScheduleItems__c is set to private sharing and a Scheduled item record is created by one user. If that same record would be created for another user that can't avoid the sharing rules then it throws this error.

The upsert doesn't find the existing record/can't access it and tries to insert it. This causes the duplicate record error to be generated.

Being able to replicate this should allow me to get it fixed.

[aheber]

Fix will be included in v2.22 when it releases.