failed to commit due to Duplicate column value violates
0
Hi, all. We met a error when combine a user entity with a image entity. e.g. we want to create new user(Proprietor) with some attributes and also 3 images(These 3 images are all unique, as an ID card front-image, a back-image and a face image). 1- We have a validation check (Rule Expression) for phone of the Entity. 2- We use the image uploader and then use microflow to create image objects when open the user add page. when we add a user, input a wrong phone number and select all 3 images. Then save the changes. The text widget of the phone input will show a error message; Then we correct the phone number, and save the changes again, we get the following error: com.mendix.core.CoreRuntimeException: Failed to commit at com.mendix.basis.component.CommitHandler.commit(CommitHandler.scala:151) Caused by: com.mendix.core.CoreRuntimeException: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"before":[{}],"action":{"type":"CommitAction"},"after":[],"type":"EventExtendedAction"}', all database changes executed by this action were rolled back at com.mendix.basis.actionmanagement.ActionManagerBase.executeInTransactionSync(ActionManagerBase.java:123) Caused by: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"before":[{}],"action":{"type":"CommitAction"},"after":[],"type":"EventExtendedAction"}', all database changes executed by this action were rolled back at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:156) Caused by: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"before":[{}],"action":{"type":"CommitAction"},"after":[],"type":"EventExtendedAction"}', all database changes executed by this action were rolled back at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:36) Caused by: com.mendix.core.CoreRuntimeException: Exception occurred in action '{"before":[{}],"action":{"type":"CommitAction"},"after":[],"type":"EventExtendedAction"}', all database changes executed by this action were rolled back at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.processErrorState(CoreActionHandlerImpl.scala:151) Caused by: com.mendix.core.CoreException: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.systemwideinterfaces.connectionbus.data.UniqueConstraintViolationRuntimeException: Duplicate column value violates its unique constraint: (conn=311) Duplicate entry '3377699720529488' for key 'PRIMARY' (SQL State: 23000, Error Code: 1062) at com.mendix.basis.actionmanagement.SyncEventExtendedAction.executeAction(SyncEventExtendedAction.java:45) Caused by: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.systemwideinterfaces.connectionbus.data.UniqueConstraintViolationRuntimeException: Duplicate column value violates its unique constraint: (conn=311) Duplicate entry '3377699720529488' for key 'PRIMARY' (SQL State: 23000, Error Code: 1062) at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:156) Caused by: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.systemwideinterfaces.connectionbus.data.UniqueConstraintViolationRuntimeException: Duplicate column value violates its unique constraint: (conn=311) Duplicate entry '3377699720529488' for key 'PRIMARY' (SQL State: 23000, Error Code: 1062) at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:36) Caused by: com.mendix.systemwideinterfaces.connectionbus.data.UniqueConstraintViolationRuntimeException: Duplicate column value violates its unique constraint: (conn=311) Duplicate entry '3377699720529488' for key 'PRIMARY' (SQL State: 23000, Error Code: 1062) at com.mendix.basis.action.user.CommitAction$$anonfun$commitPersistableObjects$4.applyOrElse(CommitAction.scala:97) at com.mendix.basis.action.user.CommitAction$$anonfun$commitPersistableObjects$4.applyOrElse(CommitAction.scala:95) at scala.runtime.AbstractPartialFunction.apply(AbstractPartialFunction.scala:38) at scala.util.Failure.recoverWith(Try.scala:236) at com.mendix.basis.action.user.CommitAction.commitPersistableObjects(CommitAction.scala:95) at com.mendix.basis.action.user.CommitAction.executeAction(CommitAction.scala:59) at com.mendix.basis.action.user.CommitAction.executeAction(CommitAction.scala:29) at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:46) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:71) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:49) at com.mendix.core.actionmanagement.CoreAction.call(CoreAction.java:54) at com.mendix.basis.actionmanagement.ActionManagerBase$1.execute(ActionManagerBase.java:147) at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32) at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:152) at com.mendix.basis.actionmanagement.SyncEventExtendedAction.executeAction(SyncEventExtendedAction.java:43) at com.mendix.basis.actionmanagement.EventExtendedAction.execute(EventExtendedAction.java:101) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:71) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:49) at com.mendix.core.actionmanagement.CoreAction.call(CoreAction.java:54) at com.mendix.basis.actionmanagement.ActionManagerBase$1.execute(ActionManagerBase.java:150) at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32) at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:152) at com.mendix.basis.actionmanagement.ActionManagerBase.executeInTransactionSync(ActionManagerBase.java:116) at com.mendix.basis.component.CommitHandler.$anonfun$storeObjectsAndAutoCommittedObjects$1(CommitHandler.scala:189) at scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:238) at scala.collection.immutable.List.foreach(List.scala:392) at scala.collection.TraversableLike.map(TraversableLike.scala:238) at scala.collection.TraversableLike.map$(TraversableLike.scala:231) at scala.collection.immutable.List.map(List.scala:298) at com.mendix.basis.component.CommitHandler.storeObjectsAndAutoCommittedObjects(CommitHandler.scala:187) at com.mendix.basis.component.CommitHandler.commit(CommitHandler.scala:125) at com.mendix.basis.component.CommitHandler.commit$(CommitHandler.scala:109) at com.mendix.basis.component.InternalCore.commit(InternalCore.scala:46) at com.mendix.basis.component.CommitHandler.commitWithEvents(CommitHandler.scala:69) at com.mendix.basis.component.CommitHandler.commit(CommitHandler.scala:28) at com.mendix.basis.component.CommitHandler.commit$(CommitHandler.scala:27) at com.mendix.basis.component.InternalCore.commit(InternalCore.scala:46) at com.mendix.basis.component.CommitHandler.commit(CommitHandler.scala:23) at com.mendix.basis.component.CommitHandler.commit$(CommitHandler.scala:23) at com.mendix.basis.component.InternalCore.commit(InternalCore.scala:46) at com.mendix.filedocumentprocessor.internal.FileDocumentProcessorImpl.writeImage(FileDocumentProcessorImpl.java:184) at com.mendix.filedocumentprocessor.internal.FileDocumentProcessorImpl.storeImageInternal(FileDocumentProcessorImpl.java:150) at com.mendix.filedocumentprocessor.internal.FileDocumentProcessorImpl.storeImage(FileDocumentProcessorImpl.java:132) at com.mendix.basis.component.InternalCoreBase.storeImageDocumentContent(InternalCoreBase.java:1103) at com.mendix.webui.requesthandling.FileRequestHandler.$anonfun$doUpload$2(FileRequestHandler.scala:166) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.mendix.webui.requesthandling.helpers.StateHandling.withState(StateHandling.scala:35) at com.mendix.webui.requesthandling.helpers.StateHandling.withState$(StateHandling.scala:32) at com.mendix.webui.requesthandling.FileRequestHandler.withState(FileRequestHandler.scala:51) at com.mendix.webui.requesthandling.helpers.StateHandling.withState(StateHandling.scala:29) at com.mendix.webui.requesthandling.helpers.StateHandling.withState$(StateHandling.scala:22) at com.mendix.webui.requesthandling.FileRequestHandler.withState(FileRequestHandler.scala:51) at com.mendix.webui.requesthandling.FileRequestHandler.doUpload(FileRequestHandler.scala:151) at com.mendix.webui.requesthandling.FileRequestHandler.handleUpload(FileRequestHandler.scala:83) at com.mendix.webui.requesthandling.FileRequestHandler.handleRequest(FileRequestHandler.scala:68) at com.mendix.webui.requesthandling.FileRequestHandler.processRequest(FileRequestHandler.scala:61) at com.mendix.externalinterface.connector.RequestHandler.doProcessRequest(RequestHandler.java:40) at com.mendix.external.connector.MxRuntimeConnector$1.execute(MxRuntimeConnector.java:76) at com.mendix.external.connector.MxRuntimeConnector$1.execute(MxRuntimeConnector.java:73) at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32) at com.mendix.external.connector.MxRuntimeConnector.processRequest(MxRuntimeConnector.java:79) at com.mendix.basis.impl.MxRuntimeImpl.processRequest(MxRuntimeImpl.java:937) at com.mendix.m2ee.appcontainer.server.handler.RuntimeHandler.service(RuntimeHandler.java:42) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:867) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:542) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255) at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1588) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1345) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:480) at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1557) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1247) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) at org.eclipse.jetty.server.Server.handle(Server.java:502) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:364) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260) at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305) at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103) at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:126) at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:366) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:765) at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:683) at java.base/java.lang.Thread.run(Thread.java:834) I suppose the data should not be commit the first time we check the validation rule. And if we change the validation rule to ‘Required’, we can save the changes the second time. How can we solve this? TIA Andes
asked
Andes Wen
3 answers
2
Maybe you are running into a false autocommit. Upon opening the page, you instantiate the new user, fill out the attributes, add an image, and save that image. Since it has the reference to the Proprietor, it autosaves the Proprietor.
So, don’t save the image before you save the Proprietor. Check if that helps.
Nevertheless, I still cannot reproduce your error. Even with the employee in my example now having three 1-1 references to the same image and saving it all without a problem, in Mendix 8.9.0…
You might want to try and set this up in a separate App and see if you can reproduce it. If so, open a question at support.mendix.com
answered
Tim v Steenbergen
0
Is your validation rule checking against a regex? Perhaps [0-9]*
I have roughly the same setup in 8.9.0, see https://forumquestions-sandbox.mxapps.io/p/employee
The first steps you can do is some further analysis:
- check if the problem still exists if you remove the associations to the image;
- check if the problem still exists if you change the validation-rule;
- check if your validation rule does give the expected result.
- debug to see what happens when you save with an invalid phone number. Does your new object show up in the database;
answered
Tim v Steenbergen
0
Hi, Tim. Thank you for the response. As you mentioned, yes I also find it strange in this way. My validation rule is something like validating if the input is numerical numbers. To make things simple, here is what I test with: '^[0-9]*$'
- check if the problem still exists if you remove the associations to the image;
#### the problem disappear if association removed. But this cannot resolve the problem, as we will need an association.
- check if the problem still exists if you change the validation-rule;
#### If change to 'Required', the problem disappears. But we need a expression rule to validate that the input is numerical numbers(some expression more complex then the test one).
- check if your validation rule does give the expected result.
#### yes, we can see the validation rule works.
- debug to see what happens when you save with an invalid phone number. Does your new object show up in the database;
#### If we input the invalid number, yes, the new image object is created and temporarily stored in database. This is what suprises me, and ofcause it is the problem. Why data gets stored before we commit?