Clarity: System error upon Save or Opening an investment properties page due to data corruption in PRJ_OBS

Clarity: System error upon Save or Opening an investment properties page due to data corruption in PRJ_OBS_ASSOCIATIONS table

book

Article ID: 18256

calendar_today

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 more

This 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: