Duplicate column value violates its unique constraint

[Silvan-V]

Hi Chris,

We have an issue where uploading multiple files at the same time with the dropzonewidget in Mendix 7.6 sometimes doesn't work. Sometimes all files are uploaded correctly, but other times we get errors on some or all files. The error in the console in Mendix is: Duplicate column value violates its unique constraint. It looks like Mendix tries to create and commit the object that is associated to the system.filedocument object (the association that we configured in the widget). Apparently, however, the object that is created can not be committed because an object with the same primary key already exists. Do you have any idea what could go wrong here?

Thanks.

[KilianCroese]

See the full stacktrace and images of the relevant domain model and dropzone widget properties below:

Uploading file failed: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"type":"CommitAction"}', all database changes executed by this action were rolled back [User '{USER}' with session id '{SESSIONID}' and roles '{ROLES}']
(1/60) com.mendix.core.CoreRuntimeException: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"type":"CommitAction"}', all database changes executed by this action were rolled back
(2/60) #011at com.mendix.basis.actionmanagement.ActionManager.executeInTransactionSync(ActionManager.java:161)
(3/60) 
(4/60) Caused by: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"type":"CommitAction"}', all database changes executed by this action were rolled back
(5/60) #011at com.mendix.basis.actionmanagement.ActionManager.executeSync(ActionManager.java:195)
(6/60) 
(7/60) Caused by: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"type":"CommitAction"}', all database changes executed by this action were rolled back
(8/60) #011at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:37)
(9/60) 
(10/60) Caused by: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"type":"CommitAction"}', all database changes executed by this action were rolled back
(11/60) #011at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.processErrorState(CoreActionHandlerImpl.scala:155)
(12/60) 
(13/60) Caused by: com.mendix.systemwideinterfaces.connectionbus.data.UniqueConstraintViolationRuntimeException: Duplicate column value violates its unique constraint: ERROR: duplicate key value violates unique constraint "agreement$agreementwizard_pkey"
(14/60)   Detail: Key (id)=(37154696925808611) already exists. (SQL State: 23505, Error Code: 0)
(15/60) #011at com.mendix.basis.action.user.CommitAction$$anonfun$commitPersistableObjects$2.applyOrElse(CommitAction.scala:97)
(16/60) #011at com.mendix.basis.action.user.CommitAction$$anonfun$commitPersistableObjects$2.applyOrElse(CommitAction.scala:95)
(17/60) #011at scala.runtime.AbstractPartialFunction.apply(AbstractPartialFunction.scala:36)
(18/60) #011at scala.util.Failure.recoverWith(Try.scala:203)
(19/60) #011at com.mendix.basis.action.user.CommitAction.commitPersistableObjects(CommitAction.scala:95)
(20/60) #011at com.mendix.basis.action.user.CommitAction.executeAction(CommitAction.scala:59)
(21/60) #011at com.mendix.basis.action.user.CommitAction.executeAction(CommitAction.scala:29)
(22/60) #011at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:50)
(23/60) #011at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:76)
(24/60) #011at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:55)
(25/60) #011at com.mendix.core.actionmanagement.CoreAction.call(CoreAction.java:51)
(26/60) #011at com.mendix.basis.actionmanagement.ActionManager$1.execute(ActionManager.java:186)
(27/60) #011at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:33)
(28/60) #011at com.mendix.basis.actionmanagement.ActionManager.executeSync(ActionManager.java:191)
(29/60) #011at com.mendix.basis.actionmanagement.ActionManager.executeInTransactionSync(ActionManager.java:154)
(30/60) #011at com.mendix.basis.component.InternalCore.commit(InternalCore.java:311)
(31/60) #011at com.mendix.basis.component.InternalCore.commit(InternalCore.java:198)
(32/60) #011at com.mendix.basis.component.InternalCore.commit(InternalCore.java:176)
(33/60) #011at com.mendix.basis.component.InternalCore.commit(InternalCore.java:162)
(34/60) #011at com.mendix.filedocumentprocessor.internal.FileDocumentProcessorImpl.storeFileDocument(FileDocumentProcessorImpl.java:133)
(35/60) #011at com.mendix.basis.component.InternalCore.storeFileDocumentContent(InternalCore.java:1607)
(36/60) #011at com.mendix.webui.requesthandling.FileRequestHandler.doUpload(FileRequestHandler.scala:178)
(37/60) #011at com.mendix.webui.requesthandling.FileRequestHandler.handleUpload(FileRequestHandler.scala:83)
(38/60) #011at com.mendix.webui.requesthandling.FileRequestHandler.handleRequest(FileRequestHandler.scala:68)
(39/60) #011at com.mendix.webui.requesthandling.FileRequestHandler.processRequest(FileRequestHandler.scala:60)
(40/60) #011at com.mendix.externalinterface.connector.RequestHandler.doProcessRequest(RequestHandler.java:40)
(41/60) #011at com.mendix.external.connector.MxRuntimeConnector$1.execute(MxRuntimeConnector.java:70)
(42/60) #011at com.mendix.external.connector.MxRuntimeConnector$1.execute(MxRuntimeConnector.java:67)
(43/60) #011at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:33)
(44/60) #011at com.mendix.external.connector.MxRuntimeConnector.processRequest(MxRuntimeConnector.java:73)
(45/60) #011at com.mendix.basis.impl.MxRuntimeImpl.processRequest(MxRuntimeImpl.java:876)
(46/60) #011at com.mendix.m2ee.appcontainer.server.handler.RuntimeHandler.handle(RuntimeHandler.java:41)
(47/60) #011at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
(48/60) #011at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116)
(49/60) #011at org.eclipse.jetty.server.Server.handle(Server.java:368)
(50/60) #011at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:489)
(51/60) #011at org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:953)
(52/60) #011at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:1014)
(53/60) #011at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:861)
(54/60) #011at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:240)
(55/60) #011at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:82)
(56/60) #011at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:628)
(57/60) #011at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:52)
(58/60) #011at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608)
(59/60) #011at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543)
(60/60) #011at java.lang.Thread.run(Thread.java:748)

[robertprice]

I had a similar issue. The way I worked around it was to patch the widget to only allow the uploading of a single file at a time. My patch has been submitted as a pull request to the project.

[royhermans]

I am encountering this issue as well, is there a solution available besides the workaround? If needed I can provide screenshots and logging as well.

I am using the dropzone widget on multiple pages, sometimes multiple widgets on 1 page, each one with a different association to the specialized FileDocument entity.

The error usually points to an object/entity that is not part of the association where I get the error.

[ChrisdeG]

Actually I need a full project that shows this error.

[TobiasHankeOC]

As we encountering the same error, I wanted to add to this a bit.

It is not that much about the project it just happens "sometimes" - I made a simple sample project, but only encountered it there once (directly on first try), so not sure if this really would help you out as the next 20+ trys it worked fine. Attached also the error message I got on this project.

It can happen that 2 of 3 images do not work and it can happen that it works for a long time even with 20 images at once. However I never seen it happening after the first upload was done.

From this it seems to be a overlap, if the parent entity is not yet commited (for example as we use it as an anonymous collector point) and then runs into this issue by auto commiting the parent.

ErrorMessage.txt Test.zip

Tried now with parallel uploads set to 1- still manages to violate unique constraint for the pkey sometimes. When I commit the parent beforehand it seems to work fine.

[ChrisdeG]

Can you please test https://github.com/ChrisdeG/DropZone/releases/download/4.0.6/DropZone.mpk

[Vilscon]

@ChrisdeG You sure it's fixed? Running 7.23.11 and Dropzone 4.0.6 and getting this error, quite often.

Running Microsoft SQL as Database.