Could not synchronize database state with session

[Jukka Posti]

Hi,

I have found a few posts concerning this topic, but have yet not been able to resolve my problem. We had a consultant setting up a system based on hibernate. System used to work, but then suddenly we started getting "Could not synchronize database state with session" error messages w/o any changes in the code => probably something had changed in the DB?!

Any help is appreciated.

Error Msg:
2005-02-03 11:11:31,392 ERROR impl.SessionImpl - Could not synchronize database state with session
net.sf.hibernate.HibernateException: SQL insert, update or delete failed (row not found)
...

Below are the statements that fail w. the error msg. See that first we had only the "first statements". When I changed the inverse value to true, I was able to get rid of the error and save a record in CodesEntered, but as a result the CodesEntered.callsessionid value in the DB was always 0. So I tried to update the 0 callsessionid afterwards with the right one by constructing the "second statements" where the "bo".callSessionId was now populated with the correct value. But this failed with the same error message.

--------- first statements -----------
Session session = DataStore.getSession();
Transaction trans = session.beginTransaction();
session.saveOrUpdate(bo); // bo = CallSessionBO instance
trans.commit();
session.close();

------- second statement ------------------
Session session = DataStore.getSession();
Transaction trans = session.beginTransaction();
session.update(bo); // bo = PropertyBO instance
trans.commit();
session.close();
---------------------------------------


Here are the mappings:

CallSessionBO (parent):
<class
name="CallSessionBO"
table="CallSession"
dynamic-update="false"
dynamic-insert="false">
<id name="id" column="id" type="java.lang.Long">
<generator class="identity"></generator>
</id>
�
<set
name="propCodes"
table="CodesEntered"
lazy="false"
inverse="false"
<!-- If I set inverse="true" => CodesEntered.callsessionid value always 0 -->
cascade="all"
sort="unsorted"
>
<key column="callsessionid" ></key>
<one-to-many
class=" PropCodeBO"/>

PropCodeBO (child):

<class
PropCodeBO"
table="CodesEntered"
dynamic-update="false"
dynamic-insert="false">
<id name="id" column="id" type="java.lang.Long">
<generator class="identity"></generator>
</id>

<property
name="callSessionId"
type="long"
update="true"
insert="true"
access="property"
column="callsessionid"/>


Database:

Table CallSession (parent), prim key = id
Table CodesEntered (child), FK column callsessionid, points to CallSession.id

[Jukka Posti]

Sorry, I need to insert an additional comment:

System behaves as mentioned in the original post only after I made the DB not check the FK:

alter table [CodesEntered] nocheck constraint [FK_CodesEntered_CallSession]
GO

When I force the FK

alter table [CodesEntered] check constraint [FK_CodesEntered_CallSession]
GO

I get this kind of error (whether the inverse="true" or inverse ="false"):

2005-02-03 12:47:21,128 ERROR util.JDBCExceptionReporter - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_CodesEntered_CallSession'. The conflict occurred in database 'PSNCC', table 'CallSession', column 'id'.
2005-02-03 12:47:21,128 WARN util.JDBCExceptionReporter - SQL Error: 3621, SQLState: HY000
2005-02-03 12:47:21,128 ERROR util.JDBCExceptionReporter - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The statement has been terminated.
2005-02-03 12:47:21,128 WARN util.JDBCExceptionReporter - SQL Error: 547, SQLState: 23000

[Lasse Koskela]

Moi Jukka,

Would you mind editing your display name so that it's not so obviously fake -- something our naming policy requires.

Thanks.

[David Harkness]

Originally posted by Jukka Source:

--------- first statements -----------
Session session = DataStore.getSession();
Transaction trans = session.beginTransaction();
session.saveOrUpdate(bo);  // bo = CallSessionBO instance
trans.commit();
session.close();
 
------- second statement  ------------------
Session session = DataStore.getSession();
Transaction trans = session.beginTransaction();
session.update(bo);   // bo = PropertyBO instance
trans.commit();
session.close();

I'm still new to Hibernate, but the following is as I understand it. In the second case you are modifying an object bo, but you're not loading it in the same session. This will cause Hibernate to believe it's a new object, if I'm not mistaken. [Actually, that's not quite right, but it did cause me problems of some other sort which I forget at the moment, possibly related to the collections it held.]

As I've had to do in porting our application from entity beans, if you want to load an object in one Session, modify it, and then save it with a new Session, you must either load it again in the new session (and throw that one out) or use the saveOrUpdateCopy() method.

Too bad SQL Server isn't telling you exactly what failed -- just "statement terminated." You can turn on SQL logging in Hibernate (set property show_sql to true) so you can see if Hibernate is issuing the queries that you expect it to.

For example, you may find it's issuing inserts when you expect updates (unique key violated, though that doesn't seem to be the case here since it's the FK that fails) or perhaps it's not inserting the parent record before the children.
[ February 03, 2005: Message edited by: David Harkness ]

[Jukka Posti]

I also thought that the child is inserted before parent. What should I do to enforce the correct order?

[David Harkness]

Originally posted by Jukka Source:
I also thought that the child is inserted before parent. What should I do to enforce the correct order?

First, verify that this is the case by turning on SQL logging or using P6Spy or some other tool. Don't guess!

If you are saving the child, make sure you save the parent, too. Also, if you use the inverse attribute, you must make the relationship bidirectional: add a ParentClass reference to the ChildClass so they both point to each other.