This week's book giveaway is in the Open Source Projects forum.
We're giving away four copies of Spark in Action and have Jean-Georges Perrin on-line!
See this thread for details.
Win a copy of Spark in Action this week in the Open Source Projects forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Inexplicable ConstraintViolationException

 
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi fellow Ranchers,

I have built a web app using Apache Tapestry.  One of the pages - UpdateUser.java - uses a standard Tapestry BeanEditForm to allow an admin user to alter another user's information.  One of the fields that may be altered is the userName but, as this is declared to be unique (Hibernate annotation), on BeanEditForm firing a 'validate' event I check to see whether a requested user name is already taken by another user.  Recording a form error within onValidate() ensures that no 'success' event gets fired next, as it is within onSuccess() that the user object finally gets updated (persisted): crudServiceDAO.update(user).

Below is my onValidate() method.



Note that part of the method is commented out, and a 'break point' of sorts inserted.  Two lines above the break point is a log output, and this successfully writes to the log.  Therefore my spotlight is on the line immediately beneath it, where I query the existance of a user already having the new user name.  This appears to be what triggers the below exception(s).


   org.hibernate.exception.ConstraintViolationException

   could not execute statement

   SQL
       n/a
   SQLState
       23000
   errorCode
       1062

and:

   java.sql.SQLIntegrityConstraintViolationException

   Duplicate entry 'Abel' for key 'UK_h029unq4qgmbvesub83df4vok'

   SQLState
       23000
   errorCode
       1062



Indeed, the database does already contain a user with user name "Abel" (it's a test).  Presumably, my app is attempting to create another user by the name "Abel", but where and why?  The line "crudServiceDAO.findUniqueWithNamedQuery(...)" is only calling the "User.BY_USERNAME" named query that you can see in the below snippet of my User.java entity class.



Possibly something odd is occurring in the inner workings of Hibernate, but this beyond my level of understanding.

Your assistance would be greatly appreciated.

Chris.
 
Rancher
Posts: 4603
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't you have the stack trace?
That would give you an insight into what is going on.
 
Christopher Dodunski
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Don't you have the stack trace?
That would give you an insight into what is going on.



I'm away from my PC at the minute, but for now the logs give some more detail.  Else I'll post the full stack trace tomorrow.


13-01-2018 00:28:09 DEBUG UpdateUser:74 - [ENTER] onValidateFromUpdateForm()
13-01-2018 00:28:09 DEBUG UpdateUser:177 - onValidateFromUpdateForm
13-01-2018 00:28:09 DEBUG UpdateUser:179 - Form user: [Kimmy|James|Cook]
13-01-2018 00:28:09 WARN  SqlExceptionHelper:145 - SQL Error: 1062,
SQLState: 23000
13-01-2018 00:28:09 ERROR SqlExceptionHelper:147 - Duplicate entry 'Kimmy'
for key 'UK_h029unq4qgmbvesub83df4vok'
13-01-2018 00:28:09 DEBUG UpdateUser:165 - [ FAIL]
onValidateFromUpdateForm --
org.hibernate.exception.ConstraintViolationException
org.hibernate.exception.ConstraintViolationException: could not execute
statement
...
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate
entry 'Kimmy' for key 'UK_h029unq4qgmbvesub83df4vok'
...
13-01-2018 00:28:09 ERROR Registry:208 - could not execute statement
13-01-2018 00:28:09 ERROR Registry:209 - Operations trace:
13-01-2018 00:28:09 ERROR Registry:218 - [ 1] Handling traditional
'action' component event request for user/Update:updateform.form.
13-01-2018 00:28:09 ERROR Registry:218 - [ 2] Triggering event 'action' on
user/Update:updateform.form
13-01-2018 00:28:09 ERROR Registry:218 - [ 3] Triggering event 'validate'
on user/Update:updateform.form
13-01-2018 00:28:09 ERROR RequestExceptionHandler:236 - Processing of
request failed with uncaught exception:
org.apache.tapestry5.runtime.ComponentEventException: could not execute
statement [at classpath:com/optomus/harbour/components/OptoEditForm.tml,
line 2]
org.apache.tapestry5.runtime.ComponentEventException: could not execute
statement [at classpath:com/optomus/harbour/components/OptoEditForm.tml,
line 2]
...
Caused by: org.apache.tapestry5.ioc.internal.OperationException: could not
execute statement [at
classpath:com/optomus/harbour/components/OptoEditForm.tml, line 2]
...
Caused by: org.apache.tapestry5.runtime.ComponentEventException: could not
execute statement [at
classpath:com/optomus/harbour/components/OptoEditForm.tml, line 2]
...
Caused by: org.hibernate.exception.ConstraintViolationException: could not
execute statement
...
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate
entry 'Kimmy' for key 'UK_h029unq4qgmbvesub83df4vok'
...

 
Dave Tolls
Rancher
Posts: 4603
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The stack trace would help.

Could you also show the code for findUniqueWithNamedQuery?

Indeed, can you do a SELECT from the USER table in whatever DB tool you use, matching the problematic query?
Also, can you post the table DDL, at least the constraints?
 
Christopher Dodunski
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:The stack trace would help.

Could you also show the code for findUniqueWithNamedQuery?

Indeed, can you do a SELECT from the USER table in whatever DB tool you use, matching the problematic query?
Also, can you post the table DDL, at least the constraints?



Certainly.

Below please find the stack trace:


com.mysql.cj.jdbc.exceptions.SQLError createSQLException() SQLError.java 533
com.mysql.cj.jdbc.exceptions.SQLError createSQLException() SQLError.java 513
com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping translateException() SQLExceptionsMapping.java 115
com.mysql.cj.jdbc.ConnectionImpl execSQL() ConnectionImpl.java 1983
com.mysql.cj.jdbc.PreparedStatement executeInternal() PreparedStatement.java 1826
com.mysql.cj.jdbc.PreparedStatement executeUpdateInternal() PreparedStatement.java 2034
com.mysql.cj.jdbc.PreparedStatement executeUpdateInternal() PreparedStatement.java 1970
com.mysql.cj.jdbc.PreparedStatement executeLargeUpdate() PreparedStatement.java 5001
com.mysql.cj.jdbc.PreparedStatement executeUpdate() PreparedStatement.java 1955
org.hibernate.engine.jdbc.internal.ResultSetReturnImpl executeUpdate() ResultSetReturnImpl.java 133
org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch addToBatch() NonBatchingBatch.java 58
org.hibernate.persister.entity.AbstractEntityPersister update() AbstractEntityPersister.java 3224
org.hibernate.persister.entity.AbstractEntityPersister updateOrInsert() AbstractEntityPersister.java 3126
org.hibernate.persister.entity.AbstractEntityPersister update() AbstractEntityPersister.java 3456
org.hibernate.action.internal.EntityUpdateAction execute() EntityUpdateAction.java 140
org.hibernate.engine.spi.ActionQueue execute() ActionQueue.java 377
org.hibernate.engine.spi.ActionQueue executeActions() ActionQueue.java 369
org.hibernate.engine.spi.ActionQueue executeActions() ActionQueue.java 287
org.hibernate.event.internal.AbstractFlushingEventListener performExecutions() AbstractFlushingEventListener.java 339
org.hibernate.event.internal.DefaultAutoFlushEventListener onAutoFlush() DefaultAutoFlushEventListener.java 62
org.hibernate.internal.SessionImpl autoFlushIfRequired() SessionImpl.java 1205
org.hibernate.internal.SessionImpl list() SessionImpl.java 1262
org.hibernate.internal.QueryImpl list() QueryImpl.java 101
org.hibernate.internal.AbstractQueryImpl uniqueResult() AbstractQueryImpl.java 905
com.optomus.harbour.dal.HibernateCrudServiceDAO findUniqueWithNamedQuery() HibernateCrudServiceDAO.java 90
com.optomus.harbour.pages.user.UpdateUser advised$onValidateFromUpdateForm_170eca30fb6() UpdateUser.java 182
org.apache.tapestry5.internal.plastic.AbstractMethodInvocation proceed() AbstractMethodInvocation.java 90
org.apache.tapestry5.ioc.internal.services.LoggingAdvice advise() LoggingAdvice.java 46
org.apache.tapestry5.internal.plastic.AbstractMethodInvocation proceed() AbstractMethodInvocation.java 92
com.optomus.harbour.pages.user.UpdateUser onValidateFromUpdateForm() UpdateUser.java
com.optomus.harbour.pages.user.UpdateUser dispatchComponentEvent() UpdateUser.java
org.apache.tapestry5.internal.structure.ComponentPageElementImpl dispatchEvent() ComponentPageElementImpl.java 917
org.apache.tapestry5.internal.structure.ComponentPageElementImpl processEventTriggering() ComponentPageElementImpl.java 1102
org.apache.tapestry5.internal.structure.ComponentPageElementImpl$5 invoke() ComponentPageElementImpl.java 1047
org.apache.tapestry5.internal.structure.ComponentPageElementImpl$5 invoke() ComponentPageElementImpl.java 1044
org.apache.tapestry5.internal.structure.ComponentPageElementResourcesImpl invoke() ComponentPageElementResourcesImpl.java 154
org.apache.tapestry5.internal.structure.ComponentPageElementImpl triggerContextEvent() ComponentPageElementImpl.java 1043
org.apache.tapestry5.internal.structure.InternalComponentResourcesImpl triggerContextEvent() InternalComponentResourcesImpl.java 288
org.apache.tapestry5.corelib.components.Form fireValidateEvent() Form.java 673
org.apache.tapestry5.corelib.components.Form onAction() Form.java 522
org.apache.tapestry5.corelib.components.Form dispatchComponentEvent() Form.java
org.apache.tapestry5.internal.structure.ComponentPageElementImpl dispatchEvent() ComponentPageElementImpl.java 917
org.apache.tapestry5.internal.structure.ComponentPageElementImpl processEventTriggering() ComponentPageElementImpl.java 1102
org.apache.tapestry5.internal.structure.ComponentPageElementImpl$5 invoke() ComponentPageElementImpl.java 1047
org.apache.tapestry5.internal.structure.ComponentPageElementImpl$5 invoke() ComponentPageElementImpl.java 1044
org.apache.tapestry5.internal.structure.ComponentPageElementResourcesImpl invoke() ComponentPageElementResourcesImpl.java 154
org.apache.tapestry5.internal.structure.ComponentPageElementImpl triggerContextEvent() ComponentPageElementImpl.java 1043
org.apache.tapestry5.internal.services.ComponentEventRequestHandlerImpl handle() ComponentEventRequestHandlerImpl.java 73
org.apache.tapestry5.internal.services.AjaxFilter handle() AjaxFilter.java 42
org.apache.tapestry5.upload.internal.services.UploadExceptionFilter handle() UploadExceptionFilter.java 76
org.apache.tapestry5.modules.TapestryModule$37 handle() TapestryModule.java 2216
org.apache.tapestry5.internal.services.ComponentRequestHandlerTerminator handleComponentEvent() ComponentRequestHandlerTerminator.java 43
org.apache.tapestry5.internal.services.DeferredResponseRenderer handleComponentEvent() DeferredResponseRenderer.java 45
org.apache.tapestry5.services.InitializeActivePageName handleComponentEvent() InitializeActivePageName.java 39
org.apache.tapestry5.internal.services.RequestOperationTracker$1 perform() RequestOperationTracker.java 55
org.apache.tapestry5.internal.services.RequestOperationTracker$1 perform() RequestOperationTracker.java 52
org.apache.tapestry5.internal.services.RequestOperationTracker handleComponentEvent() RequestOperationTracker.java 47
com.optomus.harbour.security.AuthenticationFilter handleComponentEvent() AuthenticationFilter.java 83
org.apache.tapestry5.internal.services.ComponentEventDispatcher dispatch() ComponentEventDispatcher.java 48
org.apache.tapestry5.modules.TapestryModule$RequestHandlerTerminator service() TapestryModule.java 305
org.apache.tapestry5.internal.services.RequestErrorFilter service() RequestErrorFilter.java 26
org.apache.tapestry5.modules.TapestryModule$3 service() TapestryModule.java 846
org.apache.tapestry5.modules.TapestryModule$2 service() TapestryModule.java 836
org.apache.tapestry5.internal.services.StaticFilesFilter service() StaticFilesFilter.java 89
com.optomus.harbour.services.AppModule$1 service() AppModule.java 150
org.apache.tapestry5.internal.services.CheckForUpdatesFilter$2 invoke() CheckForUpdatesFilter.java 105
org.apache.tapestry5.internal.services.CheckForUpdatesFilter$2 invoke() CheckForUpdatesFilter.java 95
org.apache.tapestry5.ioc.internal.util.ConcurrentBarrier withRead() ConcurrentBarrier.java 83
org.apache.tapestry5.internal.services.CheckForUpdatesFilter service() CheckForUpdatesFilter.java 119
org.apache.tapestry5.modules.TapestryModule$HttpServletRequestHandlerTerminator service() TapestryModule.java 256
org.apache.tapestry5.upload.internal.services.MultipartServletRequestFilter service() MultipartServletRequestFilter.java 45
org.apache.tapestry5.internal.gzip.GZipFilter service() GZipFilter.java 59
org.apache.tapestry5.internal.services.IgnoredPathsFilter service() IgnoredPathsFilter.java 62
org.apache.tapestry5.modules.TapestryModule$1 service() TapestryModule.java 796
org.apache.tapestry5.TapestryFilter doFilter() TapestryFilter.java 166

 
Christopher Dodunski
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And the code for findUniqueWithNamedQuery:



And directly querying the MySQL database:



And the User table schema:



I appreciate your help in getting to the bottom of this problem.

Chris.
 
Dave Tolls
Rancher
Posts: 4603
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Right, the key to this (and I didn't think about it until I saw the stacktrace) is this bit:

org.hibernate.event.internal.DefaultAutoFlushEventListener onAutoFlush() DefaultAutoFlushEventListener.java 62
org.hibernate.internal.SessionImpl autoFlushIfRequired() SessionImpl.java 1205
org.hibernate.internal.SessionImpl list() SessionImpl.java 1262
org.hibernate.internal.QueryImpl list() QueryImpl.java 101
org.hibernate.internal.AbstractQueryImpl uniqueResult() AbstractQueryImpl.java 905
com.optomus.harbour.dal.HibernateCrudServiceDAO findUniqueWithNamedQuery() HibernateCrudServiceDAO.java 90


The bit in bold is saying that Hibernate has identified that there are uncommitted changes to one of the bound model instances.
The auto flush is attempting to commit that to the database.

Going by the error it looks like it's trying to commit your User object?
How did you get hold of or create/populate that User?
 
Christopher Dodunski
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Right, the key to this (and I didn't think about it until I saw the stacktrace) is this bit:

org.hibernate.event.internal.DefaultAutoFlushEventListener onAutoFlush() DefaultAutoFlushEventListener.java 62
org.hibernate.internal.SessionImpl autoFlushIfRequired() SessionImpl.java 1205
org.hibernate.internal.SessionImpl list() SessionImpl.java 1262
org.hibernate.internal.QueryImpl list() QueryImpl.java 101
org.hibernate.internal.AbstractQueryImpl uniqueResult() AbstractQueryImpl.java 905
com.optomus.harbour.dal.HibernateCrudServiceDAO findUniqueWithNamedQuery() HibernateCrudServiceDAO.java 90


The bit in bold is saying that Hibernate has identified that there are uncommitted changes to one of the bound model instances.
The auto flush is attempting to commit that to the database.

Going by the error it looks like it's trying to commit your User object?
How did you get hold of or create/populate that User?



Thanks Dave, you're awesome!

Your explanation fits with my original suspicions.  I'd earlier raised the matter on the Tapestry forum, writing...


The error output suggests that BeanEditForm is committing the change to the database even before onValidate() is called - certainly not what I wish to occur.



But the folks there were adamant that this could not occur until BeanEditForm had fired a 'success' event, and my exception was occurring inside onValidate() not onSuccess().

"BeanEditForm is a powerful Tapestry component capable of generating a complete create/edit user interface for a typical JavaBean." - Tapestry website (http://tapestry.apache.org/beaneditform-guide.html).

In this instance Hibernate appears to be overriding Tapestry's designed behaviour by jumping in the way and attempting to persist the (User) object, even before BeanEditForm fires a 'success' event.  I'll make the folks at Tapestry aware of this, and see what they suggest.  Perhaps there is a means of suppressing a Hibernate 'AutoFlush' from within BeanEditForm (a code solution).  Or maybe the solution is a simple Hibernate annotation placed above the 'User' field in UpdateUser.java.

To answer your question, I instantiate and populate the BeanEditForm component with the simple markup:



Though my more complete version is a little more complex, partly because I extended BeanEditForm to create my own OptoEditForm to have more control over button rendering.



I really appreciate your help in solving this Dave, and will report back with the end solution.  :-)

Chris.
 
Dave Tolls
Rancher
Posts: 4603
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK.
So, Hibernate thinks this is a new User.
Presumably from the EditForm thing?
Does the User in that have an id set?

If not then that's the issue here.

I wouldn't presume that there's a bug in Tapestry just yet as something like that would have been spotted by now.
 
Christopher Dodunski
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:OK.
So, Hibernate thinks this is a new User.
Presumably from the EditForm thing?
Does the User in that have an id set?

If not then that's the issue here.

I wouldn't presume that there's a bug in Tapestry just yet as something like that would have been spotted by now.



No, I don't think Hibernate thinks it is a new user.  If I alter the form user name field to a name not already taken, the loaded user is updated rather than a new record created in the database.

Yes, the user being edited has an ID, but there is no visible ID field in the edit form due to the ID field being annotated with @NonVisual.  Nonetheless the user object includes the ID, and you can see in my code above that I use this ID when logging user activity.  So what is the solution?  I Googled Hibernate 'AutoFlush' behaviour and it seems it catches quite a few out (https://vladmihalcea.com/how-does-the-auto-flush-work-in-jpa-and-hibernate/).  One of the suggested means of blocking this behaviour is as below.



I'm currently thinking to modify my findUniqueWithNamedQuery() method accordingly.  I've not discovered any way to accomplish this declaratively.
 
Christopher Dodunski
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The final solution was a simple update to my findUniqueWithNamedQuery() method to override Hibernate's default behaviour of 'autoflushing' an altered entity loaded into BeanEditForm:



Note the addition of setFlushMode(FlushMode.MANUAL).
 
Dave Tolls
Rancher
Posts: 4603
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's not really a solution since Hibernate will still have an unflushed entity hanging around.
I suppose it might rollback...

So what is happening data-wise?

Because altering the entity is the cause of this.  If the entity is not supposed to be saved then why is it being altered?

I guess I'm just a bit confused as to why it's got into this state, and what flow/model has caused it.
 
Christopher Dodunski
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:That's not really a solution since Hibernate will still have an unflushed entity hanging around.
I suppose it might rollback...

So what is happening data-wise?

Because altering the entity is the cause of this.  If the entity is not supposed to be saved then why is it being altered?

I guess I'm just a bit confused as to why it's got into this state, and what flow/model has caused it.



Sorry, I've probably not done a good job of explaining the sequence of steps to update a user.

Step 1: Admin clicks on the hyperlink to update a certain user.

Step 2: BeanEditForm loads that user into a form.

Step 3: Admin alters fields as necessary.

Step 4: Admin hits the 'Submit' button.

Step 5: BeanEditForm fires a 'validate' event, causing the onValidate() method to be invoked.  In this method I check the submitted fields, for instance that the user name remains unique.  It was here that Hibernate was jumping in the way and auto-flushing the update before I'd completed the validations.

Step 6: If no errors are presented to the form, BeanEditForm then fires a 'success' event, causing the onSuccess() method to be invoked.

Step 7: The onSuccess() method persists the updated user this way: crudServiceDAO.update(user).

So you can see that setFlushMode(FlushMode.MANUAL) merely suspends the database update until I've completed the validation checks.
 
Dave Tolls
Rancher
Posts: 4603
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Personally I would let the database/Hibernate handle the uniqueness side of things at the update.
You've got to handle it anyway as there is no guarantee that someone hasn't entered the same name between the validation and the eventual update.
 
Christopher Dodunski
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Personally I would let the database/Hibernate handle the uniqueness side of things at the update.
You've got to handle it anyway as there is no guarantee that someone hasn't entered the same name between the validation and the eventual update.



It's true that the database has the final word in what gets persisted.  Personally, I like to do as much validation as possible before bothering the database.  Client-side validation reduces work on the server, and the various Tapestry validation mechanisms provide feedback in a graceful manner when a user enters something wrong.
 
What are you doing in my house? Get 'em tiny ad!
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic