constraint violation issue.

[doubledenim]

I've been trying to solve this issue for a day now. And i think it pretty much comes down to a lack of understanding of the life cycle.

I've got a session bean that calls a method on my persistence manager, passing it a persistence object called "session".

this method goes through my session persistence object and deletes attached elements of it, however it throws a constraint violation when i try to remove the SessionRate(s), saying there is a FK constraint on session.sessionRate, even though i have removed this relationship by setting session.sessionRate to null on my first line. So even though I have removed the value that is causing the constraint violation in the first line, i still get code that is run after this causing a constraint violation, therefore nothing gets changed and the whole lot gets rolled back. I am wondering if it is because it is all being done in the same batch/transaction?? see below.

em.createQuery("update Session s SET s.sessionRate = :sessionRate WHERE s = :session").setParameter("sessionRate",null).setParameter("session",session).executeUpdate();
 for(SessionPartner sessionPartner:session.getSessionPartners())
 {
 for(SessionDepartureLocation sessionDepartureLocation:sessionPartner.getSessionDepartureLocations())
 {
 for(SessionArrivalLocation sessionArrivalLocation:sessionDepartureLocation.getSessionArrivalLocations())
 {

 em.createQuery("delete SessionRate sr where sr.sessionArrivalLocation = :sessionArrivalLocation").setParameter("sessionArrivalLocation",sessionArrivalLocation).executeUpdate();
 }
 em.createQuery("delete SessionArrivalLocation sal where sal.sessionDepartureLocation = :sessionDepartureLocation").setParameter("sessionDepartureLocation",sessionDepartureLocation).executeUpdate();
 }

 em.createQuery("delete SessionDepartureLocation sdl where sdl.sessionPartner = :sessionPartner").setParameter("sessionPartner",sessionPartner).executeUpdate();
 }

[waynebaylor]

are Session and SessionRate annotated as entities or are you doing everything manually with SQL?

try setting this property in your persistence.xml and then check the order in which hibernate is executing the SQL

<property name="hibernate.show_sql">true</property>

[doubledenim]

Yeah, Session and SessionRate are both persistence objects.

I used that flag to display SQL queries, but nothing seems to be in any odd order.

[doubledenim]

Just going through that SQL output again. and theres a heap of question marks in the output. Any idea what that means?

19:52:25,390 INFO [STDOUT] Hibernate: update session set session_rate_id=? where session_id=?
19:52:25,390 WARN [FromElementType] Using non-qualified column reference [sessionArrivalLocation -> ([session_arrival_location_id])]
19:52:25,406 INFO [STDOUT] Hibernate: delete from session_rate where session_arrival_location_id=?
19:52:25,453 INFO [STDOUT] Hibernate: delete from session_rate where session_arrival_location_id=?
19:52:25,484 WARN [FromElementType] Using non-qualified column reference [sessionDepartureLocation -> ([session_departure_location_id])]
19:52:25,500 INFO [STDOUT] Hibernate: delete from session_arrival_location where session_departure_location_id=?
19:52:25,515 INFO [STDOUT] Hibernate: delete from session_rate where session_arrival_location_id=?
19:52:25,515 INFO [STDOUT] Hibernate: delete from session_rate where session_arrival_location_id=?
19:52:25,531 INFO [STDOUT] Hibernate: delete from session_arrival_location where session_departure_location_id=?
19:52:25,578 INFO [STDOUT] Hibernate: delete from session_departure_location where session_supplier_id=?
19:52:25,593 INFO [STDOUT] Hibernate: delete from session_rate where session_arrival_location_id=?
19:52:25,609 INFO [STDOUT] Hibernate: delete from session_arrival_location where session_departure_location_id=?
19:52:25,609 INFO [STDOUT] Hibernate: delete from session_departure_location where session_supplier_id=?

[doubledenim]

OK, I've been trying to figure this one out for about a week now, and it seems I have tried everything to try and make sense what is going on.

One thing that stands out that I don't quite understand is the following. To explain what is happening i'll describe the heirarchy of the methods in question.

I have a session bean which handles all of my general business logic, then a persistence manager bean that handles any interaction with my persistence layer (sending and retrieving Objects to and from my db via the EntityManager). EG:
SessionBean.methodBlah()-->PersistenceManagerBean.methodWhatever()

Some where in SessionBean.methodA() I have a line which calls the persistenceManager, which deletes elements off an object which is named session (yes there are too many things called session), the SQL output is as follows, which appears to be fine:

...........
01:21:25,359 INFO [STDOUT] Hibernate: update session set session_rate_id=? where session_id=?
01:21:25,359 INFO [STDOUT] Hibernate: delete from session_rate where session_return_location_id=?
01:21:25,375 INFO [STDOUT] Hibernate: delete from session_rate where session_return_location_id=?
01:21:25,375 INFO [STDOUT] Hibernate: delete from session_return_location where session_departure_location_id=?
01:21:25,468 INFO [STDOUT] Hibernate: delete from session_rate where session_return_location_id=?
01:21:25,468 INFO [STDOUT] Hibernate: delete from session_rate where session_return_location_id=?
01:21:25,484 INFO [STDOUT] Hibernate: delete from session_return_location where session_departure_location_id=?
01:21:25,484 INFO [STDOUT] Hibernate: delete from session_departure_location where session_provider_id=?
01:21:25,562 INFO [STDOUT] Hibernate: delete from session_rate where session_return_location_id=?
01:21:25,578 INFO [STDOUT] Hibernate: delete from session_return_location where session_departure_location_id=?
01:21:25,578 INFO [STDOUT] Hibernate: delete from session_departure_location where session_provider_id=?

Then there is another call to the persisence manager from this method followed by a return as follows:

persistenceManager.updateSession(session);
return response;

On performing the first line persistenceManager.updateSession(session);
i get the following SQL output which is fine:

......
01:21:28,484 INFO [STDOUT] Hibernate: select providerlo0_.provider_location_id as provider1_36_, providerlo0_.address as address36_, providerlo0_.name as name36_, providerlo0_.location_id as location6_36_, providerlo0_.code as code36_, providerlo0_.creation_date as creation5_36_, providerlo0_.provider_id as provider7_36_ from provider_location providerlo0_ where providerlo0_.provider_id=? and providerlo0_.location_id=?
01:21:28,484 INFO [STDOUT] Hibernate: insert into session_departure_location (enabled, creation_date, session_provider_id, provider_location_id, updated_date) values (?, ?, ?, ?, ?)
01:21:28,484 INFO [STDOUT] Hibernate: insert into session_return_location (enabled, creation_date, departure_date, return_date, session_departure_location_id, provider_location_id, updated_date) values (?, ?, ?, ?, ?, ?, ?)
01:21:28,484 INFO [STDOUT] Hibernate: insert into session_return_location (enabled, creation_date, departure_date, return_date, session_departure_location_id, provider_location_id, updated_date) values (?, ?, ?, ?, ?, ?, ?)
01:21:28,484 INFO [STDOUT] Hibernate: insert into session_departure_location (enabled, creation_date, session_provider_id, provider_location_id, updated_date) values (?, ?, ?, ?, ?)
01:21:28,484 INFO [STDOUT] Hibernate: insert into session_return_location (enabled, creation_date, departure_date, return_date, session_departure_location_id, provider_location_id, updated_date) values (?, ?, ?, ?, ?, ?, ?)
01:21:28,484 INFO [STDOUT] Hibernate: insert into session_return_location (enabled, creation_date, departure_date, return_date, session_departure_location_id, provider_location_id, updated_date) values (?, ?, ?, ?, ?, ?, ?)
01:21:28,500 INFO [STDOUT] Hibernate: insert into session_departure_location (enabled, creation_date, session_provider_id, provider_location_id, updated_date) values (?, ?, ?, ?, ?)
01:21:28,500 INFO [STDOUT] Hibernate: insert into session_return_location (enabled, creation_date, departure_date, return_date, session_departure_location_id, provider_location_id, updated_date) values (?, ?, ?, ?, ?, ?, ?)

However when I step over the line return response; I get this extra bit of sql being performed! Followed by a constraint error.

01:21:30,703 INFO [STDOUT] Hibernate: update session set affiliate_id=?, session_rate_id=?, customer_id=?, credit_card_details_id=?, reservation_id=?, updated_date=?, step=?, customer_birth_date=?, customer_country_code_1=? where session_id=?
01:21:30,734 WARN [JDBCExceptionReporter] SQL Error: 1452, SQLState: 23000
01:21:30,734 ERROR [JDBCExceptionReporter] Cannot add or update a child row: a foreign key constraint fails (`myapplication/session`, CONSTRAINT `FK76508296B1CFEF2D` FOREIGN KEY (`session_rate_id`) REFERENCES `session_rate` (`session_rate_id`))
01:21:30,734 ERROR [AbstractFlushingEventListener] Could not synchronize database state with session

This doesn't make sense to me as all I am doing is returning a value and there is nothing being called, let alone anything being called that relates to db persistence.

Am I missunderstanding how my persistence layer works?

Any assistance would be greatly appreciated

[waynebaylor]

my first guess would be that the delete logic is missing something that would clean up this foreign key relationship.

[doubledenim]

yep, that was my first guess. Which is when i included this:

em.createQuery("update Session s SET s.sessionRate = :sessionRate WHERE s = :session").setParameter("sessionRate",null).setParameter("session",session).executeUpdate();

update session set session_rate_id=? where session_id=?

However, the thing i was trying to point out in my last post was that there was SQL being produced on execution of the return statement, when there was no persistence logic being executed whatsoever.

Any ideas as to why?

[waynebaylor]

if your transaction ends after the "return response;" that extra SQL could be clean-up from hibernate or synchronization between the DB and the persistence context.