Could not synchronize database state with session

shreyas · **Posted:** Fri Mar 28, 2008 11:20 am

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

We are facing the following problem intermittently and are unable to proceed:

"Could not synchronize database state with session
org.hibernate.jdbc.BatchFailedException: Batch update failed: 0"

Please let us know if you have any experience with a similar problem.

techfoundry · **Posted:** Fri Mar 28, 2008 4:47 pm

This answer is a guess based only on your symptom being similar to one I experienced - intermittent and random failures to connect to the database.

I can't remember if my exception was the same. Anyway, the root cause was the database connection timing out - this was a difficult error, happened randomly when the connection had been idle for too long, and when it happened, my web application just died.

If you don't get any other replies you might check out the timeouts for your db connection. In my case I resorted to exercising the connection by performing a HTTP GET using wget via a cronjob to cause a database query to be executed!

Good luck!

Andy.

techfoundry · **Posted:** Sat Mar 29, 2008 9:37 am

This is amusing - I got the same "Could not synchronize database state with session" exception this morning. My guess in the post above was completely wrong (assuming the exception is only thrown for one root cause).

I have a class SystemFolder than can contain many Bookmark objects.

SystemFolder :

@ManyToMany(cascade=CascadeType.ALL)
private List<Bookmark> bookmarks;

Only I had this originally as @OneToOne, but then I changed it to @ManyToMany, but did not re-create my database schema.

So in MySQL I had a join table where the bookmarks_id field was type 'PRI' iinstead of type 'MUL'

Quote:

mysql> desc systemfolder_bookmark;
+-----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+-------+
| SystemFolder_id | int(11) | NO | MUL | | |
| bookmarks_id | int(11) | NO | PRI | | |
+-----------------+---------+------+-----+---------+-------+
2 rows in set (0.08 sec)

mysql> select * from systemfolder_bookmark;
+-----------------+--------------+
| SystemFolder_id | bookmarks_id |
+-----------------+--------------+
| 117 | 34 |
+-----------------+--------------+
1 row in set (0.00 sec)

Next I already had a Bookmark in one SystemFolder - as shown in the join table above. Then I ran a test case that added the same Bookmark to another SystemFolder, this would then have created an instance of the Many-to-Many relatioship in the database.

But this would require the systemfolder_bookmark to have two rows, with bookmarks_id duplicated - and that isn't allowed when the column type for the table is PRI - only column type MUL can be allow duplicates.

So in my case, the problem was I forgot to re-create the schema. YMMV.

Andy.

Code:

2008-03-29 13:16:44,984 ERROR [org.hibernate.event.def.AbstractFlushingEventListener] - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
   at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:92)
   at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)
   at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:218)
   at org.hibernate.persister.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:1030)
   at org.hibernate.action.CollectionUpdateAction.execute(CollectionUpdateAction.java:50)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:142)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
   at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:54)
   at xx.app.datastore.util.TransactionalBaseJPADAO.doUpdate(TransactionalBaseJPADAO.java:132)
   at xx.app.datastore.util.TransactionalBaseJPADAO.update(TransactionalBaseJPADAO.java:73)
   at xx.app.domain.user.main.FolderRepositoryImpl.updateSystemFolder(FolderRepositoryImpl.java:131)
   at xx.app.domain.user.main.BookmarkManager.setBookmarkPinnedState(BookmarkManager.java:65)
   at test.cases.xx.app.domain.user.main.BookmarkManagerTest.testSetPinnedState(BookmarkManagerTest.java:390)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
   at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
   at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
   at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
   at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
   at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:71)
   at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
   at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
   at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
   at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
   at com.intellij.rt.junit4.Junit4TestMethodAdapter.run(Junit4TestMethodAdapter.java:52)
   at junit.textui.TestRunner.doRun(TestRunner.java:115)
   at com.intellij.rt.execution.junit.IdeaTestRunner.doRun(IdeaTestRunner.java:69)
   at junit.textui.TestRunner.doRun(TestRunner.java:108)
   at com.intellij.rt.execution.junit.IdeaTestRunner.startRunnerWithArgs(IdeaTestRunner.java:24)
   at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:118)
   at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:40)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
Caused by: java.sql.BatchUpdateException: Duplicate entry '35' for key 1
   at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1213)
   at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:912)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)

Code: