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

Article Id: 18256
Status: Published
Updated On: 21-03-2018 07:12
Legacy Id: TEC612565
Products:
CLARITY PPM FOR ITG , Clarity PPM On Premise , CLARITY PPM FEDERAL , Clarity PPM SaaS , CA Identity Manager , CA Identity Governance , CA Identity Portal , Clarity PPM SaaS - Application , 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.


  1. Create a Department OBS and a Location OBS associating the Idea object and any other object. Create an entity with these OBS.
  2. 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.
  3. Link locations to departments for both entities.
  4. Now create a new Idea.
  5. 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.
  6. Now go back to properties page - Observe that 'Department OBS' field is blank (because the OBS are not associated with the Idea object)
  7. Update any field on the Idea Main Properties page, then Save.
  8. Go back to financial page - observe that Department field is blank
  9. 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: