5 Replies Latest reply on Jul 6, 2009 1:32 AM by Ganesh Sethuraman

    Exception with multiple updates

    Ganesh Sethuraman Newbie

      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?

        • 1. Re: Exception with multiple updates
          Adam Warski Master

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

          Adam

          • 2. Re: Exception with multiple updates
            Ganesh Sethuraman Newbie

            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



              • 3. Re: Exception with multiple updates
                Ganesh Sethuraman Newbie

                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).

                • 4. Re: Exception with multiple updates
                  Adam Warski Master

                  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

                  • 5. Re: Exception with multiple updates
                    Ganesh Sethuraman Newbie

                    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.