java.sql.BatchUpdateException: ORA-00600: internal error code, arguments: [qmxarElemAt2], [0], [], [], [], [], [], [] ORA-04088: error during execution of trigger 'POCWIP.PNO_INSERT'

Oracle Community | 68212 | 1 decade ago
  1. 0

    Trigger leads to ora-600 error from jdbc programme but sql in sql*plus works OK.

    Oracle Community | 1 decade ago | 68212
    java.sql.BatchUpdateException: ORA-00600: internal error code, arguments: [qmxarElemAt2], [0], [], [], [], [], [], [] ORA-04088: error during execution of trigger 'POCWIP.PNO_INSERT'
  2. 0

    I'm updating two different instances of Oracle while running under Tomcat. The increment generator is using the highest value from the first instance when it generates a new key for the same table in the second instance. This results in a unique key constraint violation. Running a simplified test program in batch works, and Hibernate debug shows 2 calls that fetch the first free id (one for each db): id.IncrementGenerator::getNext(76): first free id: 123461 But the real app, running under Tomcat, only shows one such trace. In this case, the first update, to db#1, runs under an http-processor thread; that generates the "first free id" trace. Later, a worker thread running a Quartz-scheduled task opens both db#1 and db#2, and adds a new "Offer" to db#2. No "first free id" trace appears. I've included a condensed version of the failing task's code below. Hibernate version: 2.1.8 database: Oracle 9i Mapping documents: <hibernate-mapping package="com.xyz.admin.data"> <class name="Offer" table="OFFER"> <id name="id" type="java.lang.Long"> <column name="OFFER_ID" sql-type="number" not-null="true"/> <generator class="increment"/> </id> Code from openSession() to failure: localSessionFactory = _hibernateEnvironments.getLocalSessionFactory(); localSession = localSessionFactory.openSession(); localTransaction = localSession.beginTransaction(); localSession.update(_offerJob); // re-associate this with our session _offerJob.setMessage( "initializing" ); targetSessionFactory = _hibernateEnvironments.getSessionFactory( _offerJob.getTargetEnvironmentName() ); targetSession = targetSessionFactory.openSession(); offers = _offerJob.getDeployableOffers(); for (Iterator iter = offers.iterator(); iter.hasNext(); ) { DeployableOffer deployOffer = (DeployableOffer)iter.next(); Offer sourceOffer = deployOffer.getOffer(); Long sourceId = sourceOffer.getId(); // it seemed necessary to do a 'get' here to avoid LazyInitialization errors later. sourceOffer = (Offer)localSession.get(Offer.class, sourceId); <calls method:> _targetSessionFactory = targetSession.getSessionFactory(); Offer targetOffer = null; sourceOffer.setSynchronizationKey(syncKey); ClassMetadata offerMetadata = _targetSessionFactory.getClassMetadata( Offer.class ); Transaction targetTransaction = targetSession.beginTransaction(); targetOffer = (Offer)offerMetadata.instantiate(null); targetOffer.setSynchronizationKey(syncKey); /* ... update targetOffer fields... */ targetOffer.setWebStatus(...); targetSession.saveOrUpdate( targetOffer ); targetTransaction.commit(); /* update of target offer fails here due to unique key constraint violation on Offer_id */ stack trace: * ERROR 10:23:49.671 [Promotion Admin_Worker-2] deploy.OfferSynchronizer::synchronizeOffer(225): error synchronizing; net.sf.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:73) at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29) at net.sf.hibernate.impl.BatcherImpl.convert(BatcherImpl.java:328) at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:135) at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:61) at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:58) at net.sf.hibernate.impl.BatcherImpl.prepareBatchStatement(BatcherImpl.java:111) at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:454) at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:436) at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:37) at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2449) at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2435) at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2392) at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2261) at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61) at com.xyz.promotion.admin.deploy.OfferSynchronizer.synchronizeOffer(OfferSynchronizer.java:214) at com.xyz.promotion.admin.deploy.OfferDeployer.execute(OfferDeployer.java:156) at com.xyz.promotion.admin.deploy.DeployQuartzJob.execute(DeployQuartzJob.java:72) at org.quartz.core.JobRunShell.run(JobRunShell.java:191) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:516) Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (TSI.SYS_C002728) violated at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:459) at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:3907) at com.mchange.v2.sql.filter.FilterPreparedStatement.executeBatch(FilterPreparedStatement.java:260) at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54) at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:128) ... 16 more Debug level Hibernate log excerpt: * DEBUG 12:32:34.968 [http-9980-Processor24] id.IncrementGenerator::getNext(62): fetching initial value: select max(OFFER_ID) from OFFER * DEBUG 12:32:34.984 [http-9980-Processor24] id.IncrementGenerator::getNext(76): first free id: 123462 [the above occurs during creation of new Offer on db#1] * DEBUG 12:33:01.437 [http-9980-Processor24] id.SequenceGenerator::generate(76): Sequence identifier generated: 124 * INFO 12:34:14.109 [Promotion Admin_Worker-1] deploy.DeployQuartzJob::execute(60): Deploy Job executing * INFO 12:34:14.234 [Promotion Admin_Worker-1] deploy.OfferDeployer::execute(151): processing offerid=123462 * WARN 12:34:15.093 [Promotion Admin_Worker-1] util.JDBCExceptionReporter::logExceptions(57): SQL Error: 1, SQLState: 23000 * ERROR 12:34:15.109 [Promotion Admin_Worker-1] util.JDBCExceptionReporter::logExceptions(58): ORA-00001: unique constraint (TSI.SYS_C002728) violated

    Hibernate JIRA | 1 decade ago | Dwight Harm
    net.sf.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
  3. 0

    I'm updating two different instances of Oracle while running under Tomcat. The increment generator is using the highest value from the first instance when it generates a new key for the same table in the second instance. This results in a unique key constraint violation. Running a simplified test program in batch works, and Hibernate debug shows 2 calls that fetch the first free id (one for each db): id.IncrementGenerator::getNext(76): first free id: 123461 But the real app, running under Tomcat, only shows one such trace. In this case, the first update, to db#1, runs under an http-processor thread; that generates the "first free id" trace. Later, a worker thread running a Quartz-scheduled task opens both db#1 and db#2, and adds a new "Offer" to db#2. No "first free id" trace appears. I've included a condensed version of the failing task's code below. Hibernate version: 2.1.8 database: Oracle 9i Mapping documents: <hibernate-mapping package="com.xyz.admin.data"> <class name="Offer" table="OFFER"> <id name="id" type="java.lang.Long"> <column name="OFFER_ID" sql-type="number" not-null="true"/> <generator class="increment"/> </id> Code from openSession() to failure: localSessionFactory = _hibernateEnvironments.getLocalSessionFactory(); localSession = localSessionFactory.openSession(); localTransaction = localSession.beginTransaction(); localSession.update(_offerJob); // re-associate this with our session _offerJob.setMessage( "initializing" ); targetSessionFactory = _hibernateEnvironments.getSessionFactory( _offerJob.getTargetEnvironmentName() ); targetSession = targetSessionFactory.openSession(); offers = _offerJob.getDeployableOffers(); for (Iterator iter = offers.iterator(); iter.hasNext(); ) { DeployableOffer deployOffer = (DeployableOffer)iter.next(); Offer sourceOffer = deployOffer.getOffer(); Long sourceId = sourceOffer.getId(); // it seemed necessary to do a 'get' here to avoid LazyInitialization errors later. sourceOffer = (Offer)localSession.get(Offer.class, sourceId); <calls method:> _targetSessionFactory = targetSession.getSessionFactory(); Offer targetOffer = null; sourceOffer.setSynchronizationKey(syncKey); ClassMetadata offerMetadata = _targetSessionFactory.getClassMetadata( Offer.class ); Transaction targetTransaction = targetSession.beginTransaction(); targetOffer = (Offer)offerMetadata.instantiate(null); targetOffer.setSynchronizationKey(syncKey); /* ... update targetOffer fields... */ targetOffer.setWebStatus(...); targetSession.saveOrUpdate( targetOffer ); targetTransaction.commit(); /* update of target offer fails here due to unique key constraint violation on Offer_id */ stack trace: * ERROR 10:23:49.671 [Promotion Admin_Worker-2] deploy.OfferSynchronizer::synchronizeOffer(225): error synchronizing; net.sf.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:73) at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29) at net.sf.hibernate.impl.BatcherImpl.convert(BatcherImpl.java:328) at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:135) at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:61) at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:58) at net.sf.hibernate.impl.BatcherImpl.prepareBatchStatement(BatcherImpl.java:111) at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:454) at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:436) at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:37) at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2449) at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2435) at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2392) at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2261) at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61) at com.xyz.promotion.admin.deploy.OfferSynchronizer.synchronizeOffer(OfferSynchronizer.java:214) at com.xyz.promotion.admin.deploy.OfferDeployer.execute(OfferDeployer.java:156) at com.xyz.promotion.admin.deploy.DeployQuartzJob.execute(DeployQuartzJob.java:72) at org.quartz.core.JobRunShell.run(JobRunShell.java:191) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:516) Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (TSI.SYS_C002728) violated at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:459) at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:3907) at com.mchange.v2.sql.filter.FilterPreparedStatement.executeBatch(FilterPreparedStatement.java:260) at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54) at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:128) ... 16 more Debug level Hibernate log excerpt: * DEBUG 12:32:34.968 [http-9980-Processor24] id.IncrementGenerator::getNext(62): fetching initial value: select max(OFFER_ID) from OFFER * DEBUG 12:32:34.984 [http-9980-Processor24] id.IncrementGenerator::getNext(76): first free id: 123462 [the above occurs during creation of new Offer on db#1] * DEBUG 12:33:01.437 [http-9980-Processor24] id.SequenceGenerator::generate(76): Sequence identifier generated: 124 * INFO 12:34:14.109 [Promotion Admin_Worker-1] deploy.DeployQuartzJob::execute(60): Deploy Job executing * INFO 12:34:14.234 [Promotion Admin_Worker-1] deploy.OfferDeployer::execute(151): processing offerid=123462 * WARN 12:34:15.093 [Promotion Admin_Worker-1] util.JDBCExceptionReporter::logExceptions(57): SQL Error: 1, SQLState: 23000 * ERROR 12:34:15.109 [Promotion Admin_Worker-1] util.JDBCExceptionReporter::logExceptions(58): ORA-00001: unique constraint (TSI.SYS_C002728) violated

    Hibernate JIRA | 1 decade ago | Dwight Harm
    net.sf.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Update list with new element from persist object

    Stack Overflow | 2 years ago
    org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
  6. 0

    AbstractFlushingEventListener - Could not synchronize database state with session - Spring Forum

    spring.io | 1 year ago
    org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. java.sql.BatchUpdateException

      ORA-00600: internal error code, arguments: [qmxarElemAt2], [0], [], [], [], [], [], [] ORA-04088: error during execution of trigger 'POCWIP.PNO_INSERT'

      at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException()
    2. oracle.jdbc.dbaccess
      DBError.throwBatchUpdateException
      1. oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:459)
      1 frame
    3. Oracle jdbc
      OraclePreparedStatement.executeBatch
      1. oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:3907)
      1 frame
    4. Unknown
      loader3.main
      1. loader3.main(loader3.java:83)
      1 frame