Support Article

SQLIntegrityConstraintViolationException with BIX using XML

SA-31620

Summary



Business Intelligence Exchange (BIX) is used to extract data from Pega database to XML from command line.
When the script for BIX extraction is run SQLIntegrityConstraintViolationException errors are thrown in logs. 


Error Messages



08:37:33,041 [   your_host] (  internal.access.DatabaseImpl) ERROR   - Unable to add instance of class Data-Admin-System with pzInsKey DATA-ADMIN-SYSTEM YOUR_SYSTEM to queue
com.pega.pegarules.pub.database.IntegrityConstraintViolationException: Database-Saver-Insertfail    
From: (B315D999B86542841CBF3019B064E71E7:(PREnvironment)) 
    SQL: insert into PEGA_DATA.pr_sys_workindexer_queue (PXCREATEDATETIME , PXINSNAME , PXLASTEXECUTIONDATETIME , PXOBJCLASS , PXPROCESSINGNODEID , PYAGENTNAME , PYATTEMPTS , PYITEMID , PYITEMSTATUS , PYMAXATTEMPTS , PYMINIMUMDATETIMEFORPROCESSING , PYPRIORITY , PYSELECTID , pzInsKey , pzPVStream) values (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)
    SQL Inserts: <2016-12-14 08:37:32.364>    <SYSTEM-WORK-INDEXER 14817262523730004D69334B0289CE30BF95EBA1D9D8891C>    <2016-12-14 08:37:32.364>    <System-Work-Indexer>    <<null>>    <Pega-RULES:SystemWorkIndexer>    <0>    <SYSTEM-WORK-INDEXER 14817262523730004D69334B0289CE30BF95EBA1D9D8891C>    <Scheduled>    <1>    <2016-12-14 08:37:32.364>    <0>    <14817262523730004D69334B0289CE30BF95EBA1D9D8891C>    <SYSTEM-WORK-INDEXER SYSTEM-WORK-INDEXER 14817262523730004D69334B0289CE30BF95EBA1D9D8891C>    <<stream>>    

Caused by SQL Problems.
Problem #1, SQLState 23000, Error code 1: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (PEGA_DATA.PR_SYS_WORKINDEXER_QUEUE_PK) violated

    at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:193)
    at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.addBatch(DatabasePreparedStatementImpl.java:646)
    at com.pega.pegarules.data.internal.access.Saver.saveInstanceWithNoDeletedColumnOnlyIfNew(Saver.java:1222)
    at com.pega.pegarules.data.internal.access.Saver.saveInstance(Saver.java:830)
    at com.pega.pegarules.data.internal.access.DatabaseImpl.performOps(DatabaseImpl.java:3028)
    at com.pega.pegarules.data.internal.access.DatabaseImpl.attemptToProcessUpdates(DatabaseImpl.java:2433)
    at com.pega.pegarules.data.internal.access.DatabaseImpl.processUpdates(DatabaseImpl.java:2283)
    at com.pega.pegarules.data.internal.access.Saver.save(Saver.java:652)
    at com.pega.pegarules.data.internal.access.DatabaseImpl.save(DatabaseImpl.java:5196)
    at com.pega.pegarules.session.internal.async.agent.QueueManagerImpl.enqueue(QueueManagerImpl.java:612)
    at com.pega.pegarules.session.internal.async.agent.QueueManagerImpl.enqueue(QueueManagerImpl.java:403)
    at com.pega.pegarules.data.internal.access.DatabaseImpl.noteChange(DatabaseImpl.java:3589)
    at com.pega.pegarules.data.internal.access.DatabaseImpl.notifyOtherSystemsAfterUpdates(DatabaseImpl.java:2822)
    at com.pega.pegarules.data.internal.access.DatabaseImpl.attemptToProcessUpdates(DatabaseImpl.java:2482)
    at com.pega.pegarules.data.internal.access.DatabaseImpl.processUpdates(DatabaseImpl.java:2283)
    at com.pega.pegarules.data.internal.access.Saver.save(Saver.java:652)
    at com.pega.pegarules.data.internal.access.DatabaseImpl.save(DatabaseImpl.java:5196)
    at com.pega.pegarules.data.internal.access.DatabaseImpl.save(DatabaseImpl.java:5180)
    at com.pega.pegarules.session.internal.mgmt.PRNodeImpl.initializeSystem(PRNodeImpl.java:821)
    at com.pega.pegarules.session.internal.mgmt.PREnvironment.getThreadAndInitialize(PREnvironment.java:373)
    at com.pega.pegarules.session.internal.PRSessionProviderImpl.getThreadAndInitialize(PRSessionProviderImpl.java:1913)
    at com.pega.pegarules.session.internal.engineinterface.etier.impl.EngineStartup.initEngine(EngineStartup.java:645)


Steps to Reproduce



Run BIX from command line with XML, using last updated time as start.


Root Cause



A defect in Pegasystems’ code or rules. 

When running BIX, PegaRULES engine is started in minimal startup mode on the command line JVM. Upon inspection, user notices that BIX prconfig.xml has environment settings that disable Agents. Lucene Search indexing is another product component that has no use running in a JVM where BIX is running.

Thus user should disable this component by updating the BIX specific prconfig.xml in order to prevent the error from happening.

Resolution



Perform the following local-change:
  1. In the BIX prconfig.xml, add the following entry:<env value="false" name="indexing/enabled"/>
  2. And run BIX extraction again.

Published December 19, 2016 - Updated December 27, 2016

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.