How is it possible to get a duplicate key value violates unique constraint error? - Mendix Forum

How is it possible to get a duplicate key value violates unique constraint error?

5

Hi all,

today I found this error on our server logs:

UniqueConstraintViolationRuntimeException: Duplicate column value violates its unique constraint: ERROR: duplicate key value violates unique constraint "crmcore$person_pkey"
Detail: Key (id)=(30680772461462705) already exists. (SQL State: 23505, Error Code: 0)

If I interpret this correctly there a uniqueness constraint violation on the internal id generated by Mendix.

So, how is is possible that this can happen at all?

I expected that Mendix should handle that kind of stuff. Now I'm really wondering what we could do to fix this.

Did anyone else see this kind of error?

regards, Fabian

EDIT - RESOLUTION: as far as I could determine this was caused by automcommited objects beeing used in the ProcessQueue.

asked
6 answers
4

It seems this error is happening a lot lately :)

We now had this issue also. In my opinion this is a combination of a modeler error and a Menidx bug. We had this issue  in a microflow when we had a new parent object where a couple of new child objects where created. While committing the child objects list this error would pop up because the parent object needs to be autocommitted and there the Mendix bug pops up. Mendix should not throw an error in this situation in my opinion. Sollution is to do the commit of the parent object first.

Regards,

Ronald

 

answered
2

In this instance I think the situation is much simpler. It looks like a save button (with a custom microflow behind it) is executed for a second time before the first execution is finished. This simply means the 2nd execution tries to create the same object (with the same id) a little bit later than the 1st execution. Causing this error to be thrown. A simple way to resolve this would be to use the "disable during action" option (see https://docs.mendix.com/refguide/action-button#disable-during-action-only-for-microflow-actions)

answered
0

I always noticed this in a Client where the environment is with 2 mendix servers hosting same app and a load balancer in front. Apparently in those hurried clicking attempts the calls are being split and 1 server takes too long time to server while the other finishes. And yes, Disabled During Action never solved this case.

answered
0

Often you can fix the issue using the Uniqueness Constraint Migration toolkit. Ask Mendix support for it and they can send the MPK file to you, which helps you detecting and solving the uniqueness violation. https://docs.mendix.com/refguide/uniqueness-constraint-migration

We encountered similar issues a high concurrency application. Probably Mendix could not process the concurrent events correctly. 

Did you recently switch from uniqueness violation check by runtime to database?

answered
0

Hi Fabian,

I have run into this issue when restoring a database and not restarting the application with an on-prem application. From what I have learned this should technically never happen as Mendix is handling the creation of the ids. That being said, are you using any SQL to insert data into your application?

 

answered
0

Hi Fabian,

I ran into this once when I added an extra association between to entities. The first association was many to one. The second was many to many with the owner set to default (i.g. the same object as the previous association). This resulted in the same reference Id being stored in the object twice (or more). Is this maybe the case in your project? 

Best regards,

Serge

answered