Exception with multiple updates

[ganeshmailbox]

In a single transaction when we tried to two 2 updates or inserts we are getting the below error java.sql.BatchUpdateException: ORA-00001: unique constraint (SLRMDB.REV) violated. In revision table it tries to insert 2 rows with the same primany key. Reason: Envers is trying to insert the REV table with the same primary key for both the updates/inserts

Is there a work around for this, thanks for helping out?

[adamw]

Well, it's hard to tell what's wrong with that litte data. Can you provide some more? A testcase maybe?

Adam

[ganeshmailbox]

Adam,
1.I created the _AUD and REV and the sequence manually using sql insert scripts, since envers didn't create these tables automatically.
In persistence.xml i have removed hbm2ddl.auto property.
Is this property required to be set (if so update)?

I also need to create the Audit tables alone and not affect any of the existing tables. Will EnversHibernateToolTask help ?
I use HIbernare-core3.3.1G.A and envers 1.2.1.G.A


2.Tried with the following code. Tried to insert 2 rows within the same transaction.But i'm getting the following errors

DataTypeLookup dataLookup = new DataTypeLookup();
DataTypeLookup dataLookup1 = new DataTypeLookup();
dataLookup.setDataTypCode('c');
dataLookup.setDataTypDesc("description");
dataLookup1.setDataTypCode('Z');
dataLookup1.setDataTypDesc("PersistingRev2");
this.dataTypeLookupDao.persist(dataLookup);
this.dataTypeLookupDao.persist(dataLookup1);

java.sql.BatchUpdateException: ORA-00001: unique constraint (SLRMDB.REV) violated

at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:458)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:3907)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
at org.hibernate.envers.synchronization.AuditSync.beforeCompletion(AuditSync.java:167)
at org.hibernate.transaction.JDBCTransaction.notifyLocalSynchsBeforeTransactionCompletion(JDBCTransaction.java:274)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:140)
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:54)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:456)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:732)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:701)
at org.springframework.test.AbstractTransactionalSpringContextTests.endTransaction(AbstractTransactionalSpringContextTests.java:355)
at org.springframework.test.AbstractTransactionalSpringContextTests.onTearDown(AbstractTransactionalSpringContextTests.java:287)
at org.springframework.test.AbstractSingleSpringContextTests.tearDown(AbstractSingleSpringContextTests.java:137)
at junit.framework.TestCase.runBare(TestCase.java:140)
at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:76)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.access$001(AbstractAnnotationAwareTransactionalTests.java:71)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests$1.run(AbstractAnnotationAwareTransactionalTests.java:175)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runTest(AbstractAnnotationAwareTransactionalTests.java:283)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runTestTimed(AbstractAnnotationAwareTransactionalTests.java:254)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runBare(AbstractAnnotationAwareTransactionalTests.java:172)
at org.springframework.test.jpa.AbstractJpaTests.runBare(AbstractJpaTests.java:174)
at org.springframework.test.jpa.AbstractJpaTests.runBare(AbstractJpaTests.java:255)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:124)
at junit.framework.TestSuite.runTest(TestSuite.java:232)
at junit.framework.TestSuite.run(TestSuite.java:227)
at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:81)
at org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:62)
at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.executeTestSet(AbstractDirectoryTestSuite.java:140)
at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.execute(AbstractDirectoryTestSuite.java:127)
at org.apache.maven.surefire.Surefire.run(Surefire.java:177)
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:585)
at org.apache.maven.surefire.booter.SurefireBooter.runSuitesInProcess(SurefireBooter.java:338)
at org.apache.maven.surefire.booter.SurefireBooter.main(SurefireBooter.java:997)


org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
at org.hibernate.envers.synchronization.AuditSync.beforeCompletion(AuditSync.java:167)
at org.hibernate.transaction.JDBCTransaction.notifyLocalSynchsBeforeTransactionCompletion(JDBCTransaction.java:274)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:140)
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:54)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:456)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:732)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:701)
at org.springframework.test.AbstractTransactionalSpringContextTests.endTransaction(AbstractTransactionalSpringContextTests.java:355)
at org.springframework.test.AbstractTransactionalSpringContextTests.onTearDown(AbstractTransactionalSpringContextTests.java:287)
at org.springframework.test.AbstractSingleSpringContextTests.tearDown(AbstractSingleSpringContextTests.java:137)
at junit.framework.TestCase.runBare(TestCase.java:140)
at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:76)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.access$001(AbstractAnnotationAwareTransactionalTests.java:71)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests$1.run(AbstractAnnotationAwareTransactionalTests.java:175)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runTest(AbstractAnnotationAwareTransactionalTests.java:283)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runTestTimed(AbstractAnnotationAwareTransactionalTests.java:254)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runBare(AbstractAnnotationAwareTransactionalTests.java:172)
at org.springframework.test.jpa.AbstractJpaTests.runBare(AbstractJpaTests.java:174)
at org.springframework.test.jpa.AbstractJpaTests.runBare(AbstractJpaTests.java:255)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:124)
at junit.framework.TestSuite.runTest(TestSuite.java:232)
at junit.framework.TestSuite.run(TestSuite.java:227)
at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:81)
at org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:62)
at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.executeTestSet(AbstractDirectoryTestSuite.java:140)
at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.execute(AbstractDirectoryTestSuite.java:127)
at org.apache.maven.surefire.Surefire.run(Surefire.java:177)
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:585)
at org.apache.maven.surefire.booter.SurefireBooter.runSuitesInProcess(SurefireBooter.java:338)
at org.apache.maven.surefire.booter.SurefireBooter.main(SurefireBooter.java:997)
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (SLRMDB.REV) violated

at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:458)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:3907)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
... 41 more

[ganeshmailbox]

Manual Creation of the Tables was the problem.
While Creating table manually , missed the follwing relationship

ALTER TABLE DATA_TYP_LKUP_AUD ADD CONSTRAINT DATA_TYP_LKUP_AUD1 PRIMARY KEY (DATA_TYP_CODE, REV);

ALTER TABLE DATA_TYP_LKUP_AUD ADD CONSTRAINT FK9D27DAF4DF74E053 FOREIGN KEY (REV)
REFERENCES REVINFO (REV);

and just created the Audit and revision tables only.

hbm2ddl seems to have only 3 options
create, create-drop, update. create and create-drop both will drop and create the tables. Update will update the existing tables and create the new ones.
Is there any way in which I can create the Audit tables and Rev tables only without affecting the exisitng tables(since we are not supposed to modify the actual ones).

[adamw]

Hello,

so the problem got solved? The hbm2ddl auto is not required, it completely optional. There is no option to generate only the Envers tables, but I guess you can generate them once for the schema and then just re-use those scripts later.

Adam

[ganeshmailbox]

Hi Adam,

Thanks for the reply.Yes its working now. We are also thinking the same of generating the scripts once and re-using them.