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.
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