Clarity: System error upon Save or Opening an investment properties page due to data corruption in PRJ_OBS_ASSOCIATIONS table
Article ID: 18256
Updated On:
Products
CLARITY PPM FOR ITG
CLARITY PPM FEDERAL
CA Identity Manager
CA Identity Governance
CA Identity Portal
Clarity PPM SaaS - Application
Clarity PPM On Premise
Issue/Introduction
Description:
During the creation and configuration of financial OBS and associating the departments and locations created in this process, data in PRJ_OBS_ASSOCIATIONS can become corrupted, causing system error when saving or just opening certain investment properties page.
Any investment object can be affected, see below an example with Idea being the affected object type.
- Create a Department OBS and a Location OBS associating the Idea object and any other object. Create an entity with these OBS.
- Now create on more pair of Department and Location OBS and a second Entity but do not associate the Idea object with the two OBS.
- Link locations to departments for both entities.
- Now create a new Idea.
- Go to the Idea financial page, add department and locations which were created in step 2 (Note: the Idea object is not associated to the department OBS at this time), click Save and there is no error.
- Now go back to properties page - Observe that 'Department OBS' field is blank (because the OBS are not associated with the Idea object)
- Update any field on the Idea Main Properties page, then Save.
- Go back to financial page - observe that Department field is blank
- Now update the Department and Location fields with new values and Save
Expected Result: Update is successful, and Department is showing on the Main Properties page
Actual Result: System error, the following error shows up in the app log
ERROR 2014-04-17 11:28:32,755 [http-bio-80-exec-6] niku.xql2 (clarity:pcSetupUser:5070018__E745C3CF-8D2F-41CE-ACE6-CC05FC719697:projmgr.productPropertiesPost) Internal Processing exception
com.niku.union.persistence.PersistenceException:
SQL error code: 2627
Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Violation of PRIMARY KEY constraint 'PRJ_OBS_ASSOCIATIONS_PK'. Cannot insert duplicate key in object 'niku.PRJ_OBS_ASSOCIATIONS'.
SQL error code: 3621
Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]The statement has been terminated.
Executed:
INSERT INTO prj_obs_associations
(ID, unit_id, table_name, record_id, created_date, created_by,
last_updated_date, last_updated_by
)
VALUES (?, ?, ?, ?, ?, ?, ?, ? )
Derived from statement:
<?xml version="1.0" encoding="UTF-8"?>
<statement location="financialProperties.xml" id="revmgr.insertLocationObsAssoc" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="none" xmlns="http://schemas.niku.com/2002/pmd">
<sql dbVendor="all">
<text>
INSERT INTO prj_obs_associations
(ID, unit_id, table_name, record_id, created_date, created_by,
last_updated_date, last_updated_by
)
VALUES (?, ?, ?, ?, ?, ?, ?, ? )
</text>
<param name="id" type="long" direction="IN" expressionListDelimiter="," tableName="PRJ_OBS_ASSOCIATIONS" sequenceType="nextValue"/>
<param name="obs_unit_id" type="long" direction="IN" expressionListDelimiter=","/>
<param name="table_name" type="string" direction="IN" expressionListDelimiter=","/>
<param name="projectId" type="long" direction="IN" expressionListDelimiter=","/>
<param replace="timestamp" direction="IN" expressionListDelimiter=","/>
<param replace="userId" direction="IN" expressionListDelimiter=","/>
<param replace="timestamp" direction="IN" expressionListDelimiter=","/>
<param replace="userId" direction="IN" expressionListDelimiter=","/>
</sql>
</statement>
Referenced by:
<?xml version="1.0" encoding="UTF-8"?>
<statementRef id="revmgr.insertLocationObsAssoc" inputSource="map" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" xmlns="http://schemas.niku.com/2002/pmd"/>
Using input:
{table_name=INV_PRODUCT, value=revmgr.createLocationObsAssoc, obs_unit_id=5001004, projectId=5001001, parameters=[null:obs_unit_id:5001004:, null:table_name:INV_PRODUCT:, null:projectId:5001001:], parameterSet=[], [email protected]} sequences[PRJ_OBS_ASSOCIATIONS: 5003004]
at com.niku.union.persistence.PersistenceController.createException(PersistenceController.java:2049)
at com.niku.union.persistence.PersistenceController.handleSQLException(PersistenceController.java:2153)
at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2780)
at com.niku.union.persistence.PersistenceController.processStatement(PersistenceController.java:868)
at com.niku.union.persistence.PersistenceController.processStatements(PersistenceController.java:768)
at com.niku.union.persistence.PersistenceController.doProcessRequest(PersistenceController.java:576)
at com.niku.union.persistence.PersistenceController.processRequest(PersistenceController.java:306)
at com.niku.xql2.pmd.PMDDataSource.execute(PMDDataSource.java:204)
at com.niku.xql2.handlers.ExecuteHandler.postProcess(ExecuteHandler.java:90)
at com.niku.xql2.XQLVisitor.postProcess(XQLVisitor.java:1412)
at com.niku.union.xml.dom.DOMWalker.postProcess(DOMWalker.java:210)
at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:94)
at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:92)
at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:92)
at com.niku.union.xml.dom.DOMWalker.traverse(DOMWalker.java:36)
at com.niku.xql2.XQLProcessor.process(XQLProcessor.java:257)
at com.niku.xql2.XQLProcessor.process(XQLProcessor.java:283)
at com.niku.xql2.binding.ServiceControlBindingXql.processFile(ServiceControlBindingXql.java:388)
at com.niku.xql2.binding.ServiceControlBindingXql.getDocument(ServiceControlBindingXql.java:271)
at com.niku.xql2.binding.ServiceControlBindingXql.processRequest(ServiceControlBindingXql.java:155)
at com.niku.union.service.ServiceControlImpl.processRequest(ServiceControlImpl.java:762)
at com.niku.union.service.ServiceControlImpl.processRequest(ServiceControlImpl.java:211)
at com.niku.odf.object.Utils.execService(Utils.java:196)
at com.niku.odf.object.Utils.execService(Utils.java:256)
at com.niku.odf.object.ODFObjectImpl.update(ODFObjectImpl.java:2933)
at com.niku.odf.object.ODFObjectImpl.update(ODFObjectImpl.java:2795)
at com.niku.odf.service.ODFUpdateServiceBinding.process(ODFUpdateServiceBinding.java:72)
at com.niku.odf.service.ODFService.doProcess(ODFService.java:179)
at com.niku.odf.service.BaseODFService.processRequest(BaseODFService.java:112)
at com.niku.union.service.ServiceControlImpl.processRequest(ServiceControlImpl.java:762)
at com.niku.union.service.ServiceControlImpl.processRequest(ServiceControlImpl.java:211)
at com.niku.union.web.WebServiceAdapter.processRequest(WebServiceAdapter.java:219)
at com.niku.union.web.PortletController.processServiceRequest(PortletController.java:1419)
at com.niku.union.web.PortletController.processProcessActionRequest(PortletController.java:233)
at com.niku.union.web.PortletController.processRequest(PortletController.java:160)
at com.niku.union.web.WebActionController.processAction(WebActionController.java:1362)
at com.niku.union.web.WebActionController.processRequest(WebActionController.java:187)
at com.ca.clarity.uif.service.vxml.VXMLService.processRequest(VXMLService.java:455)
at com.ca.clarity.uif.UIServlet.service(UIServlet.java:69)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.niku.union.web.filter.GZIPResponseFilter.doFilter(GZIPResponseFilter.java:142)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.niku.union.web.filter.CharsetFilter.doFilter(CharsetFilter.java:68)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.filters.AddDefaultCharsetFilter.doFilter(AddDefaultCharsetFilter.java:89)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
at com.niku.union.web.valves.ErrorPageRedirectValve.invoke(ErrorPageRedirectValve.java:70)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:579)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:309)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
Caused by: java.sql.SQLIntegrityConstraintViolationException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Violation of PRIMARY KEY constraint 'PRJ_OBS_ASSOCIATIONS_PK'. Cannot insert duplicate key in object 'niku.PRJ_OBS_ASSOCIATIONS'.
at com.ca.clarity.jdbc.sqlserverbase.ddcw.b(Unknown Source)
at com.ca.clarity.jdbc.sqlserverbase.ddcw.a(Unknown Source)
at com.ca.clarity.jdbc.sqlserverbase.ddcv.b(Unknown Source)
at com.ca.clarity.jdbc.sqlserverbase.ddcv.a(Unknown Source)
at com.ca.clarity.jdbc.sqlserver.tds.ddr.v(Unknown Source)
at com.ca.clarity.jdbc.sqlserver.tds.ddr.a(Unknown Source)
at com.ca.clarity.jdbc.sqlserver.tds.ddq.a(Unknown Source)
at com.ca.clarity.jdbc.sqlserver.tds.ddr.a(Unknown Source)
at com.ca.clarity.jdbc.sqlserver.dda3.m(Unknown Source)
at com.ca.clarity.jdbc.sqlserverbase.dde7.e(Unknown Source)
at com.ca.clarity.jdbc.sqlserverbase.dde7.a(Unknown Source)
at com.ca.clarity.jdbc.sqlserverbase.ddd2.a(Unknown Source)
at com.ca.clarity.jdbc.sqlserverbase.dde7.v(Unknown Source)
at com.ca.clarity.jdbc.sqlserverbase.dde7.r(Unknown Source)
at com.ca.clarity.jdbc.sqlserverbase.ddd2.execute(Unknown Source)
at sun.reflect.GeneratedMethodAccessor41.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:68)
at org.logicalcobwebs.cglib.proxy.Proxy$ProxyImpl$$EnhancerByCGLIB$$b859b59.execute(<generated>)
at com.niku.union.persistence.jdbc.SQLTracePreparedStatement.execute(SQLTracePreparedStatement.java:109)
at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2742)
... 62 moreThis query will show that there are now multiple obs unit associations made between the OBS and the same Idea. An investment cannot be associated to more than one unit from the same OBS structure, therefore the data corruption is violating the primary key constraint on the PRJ_OBS_ASSOCIATIONS table.
This issue can also cause a problem with Ideas not being visible after upgrading to 13.3 (MSSQL), or an upgrade failure for Oracle database.
Reference CLRT-74224 which is documented in a separate KB.
Solution:
WORKAROUND:
Before using an OBS (departments) for an object, first check if the object is currently associated to the OBS from Administration, OBS
Identify the data corruption for Ideas with the following query, change the investment type accordingly for other investment types such as projects. Contact Clarity Technical Support to ensure proper data cleanup based on the query result
select inv.code, poa.unit_id, pou.name, pot.unique_name from prj_obs_associations poa, inv_investments inv, prj_obs_units pou, prj_obs_types pot, prj_obs_object_types poot, entity e where poa.table_name = 'INV_IDEA' and poa.unit_id = pou.id and pou.type_id = pot.id and pot.id = poot.type_id and inv.id = poa.record_id and e.org_chart_obs_type_id = pou.type_id and poa.record_id in (SELECT poa.record_id FROM prj_obs_associations poa, prj_obs_units pou, prj_obs_types pot, prj_obs_object_types poot WHERE poa.unit_id = pou.id AND upper(poa.table_name) = 'INV_IDEA' AND pou.type_id = pot.id AND pot.id = poot.type_id AND upper(poa.table_name) = upper(poot.table_name) AND poot.assoc_att_code = 'odf_obs_fin_dept' GROUP BY poa.record_id HAVING COUNT(poa.record_id)>1) group by inv.code, poa.unit_id, pou.name, pot.unique_name
STATUS/RESOLUTION:
This issue is being addressed as CLRT-74529 for a resolution
Keywords: CLARITYKB, CLRT-74529, clarity13open
Environment
Release: ESPCLA99000-13.2-Clarity-Extended Support Plus
Component:
Component:
Feedback
Yes
No
Powered by
