When using a Status module as default in New UX, "ODF-0015: Value must be unique" and "ORA-00001: unique constraint (NIKU.CMN_UI_PREFERENCES_U1) violated" thrown on projects created in Classic with no Status report

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

STEPS TO REPRODUCE:

1. Create a custom Blueprint for Project, with only change the Status module to be first in the list of module (this means it will open first), Save
2. Now go to Classic - Project List
3. Add Blueprint field to the view
4. Now Create a new project
5. Set the Blueprint to this new Blueprint we created, Save
6. Now go to Modern UX - find and open the newly created project by clicking on the Project Tile

Expected Results: The project to open successfully

Actual Results: Error message ODF-0015: Value must be unique,or a second message Could not load project status report

The Status Report is created but the data in table CMN_UI_PREFERENCES may become bad and cause issues with opening any projects or filtering on them. Once the error starts happening it may no longer display in UI but be there in logs:

In the app-ca log we see a unique constraint error:

ERROR 2020-11-18 09:54:53,478 [http-nio-8080-exec-943] object.CustomObjectInstanceClass (clarity:admin:xxxxxxxx__F63CC616-277D-483C-AB9B-8F115E9A5F38:PPM_REST_API) Exception on insert
com.niku.union.persistence.PersistenceApplicationException: java.sql.SQLIntegrityConstraintViolationException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (NIKU.CMN_UI_PREFERENCES_U1) violated
               at com.niku.union.persistence.PersistenceController.checkPMDOverride(PersistenceController.java:2036)
              at com.niku.union.persistence.PersistenceController.handleSQLException(PersistenceController.java:2218)
              at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:3118)
              at com.niku.union.persistence.PersistenceController.processStatement(PersistenceController.java:881)
              at com.niku.union.persistence.PersistenceController.processStatements(PersistenceController.java:781)

In the HAR we can see an error HTTP 400 during POST. The request seems to be sent only once.

This error is thrown as well in some scenarios: 

ERROR 2020-11-30 11:43:15,984 [http-nio-8080-exec-446] rest.validation (clarity:admin:70179747__05F048AE-62C6-45FF-885B-A8A165797C1D:PPM_REST_API) ODFResourceProvider :: Could not create resource. Object code alias: [ requestUrl: http://example.com/ppm/rest/v1/private/uiPreferences , _restResourceName : uiPreferences , _id : null , _parentInfo : null , _apiVersion : v1 , _includeLinksArray : true , _contextId : -1 , _hierarchyId : -1]

ERROR 2020-11-30 11:43:15,984 [http-nio-8080-exec-446] rest.validation (clarity:admin:70179747__05F048AE-62C6-45FF-885B-A8A165797C1D:PPM_REST_API) ExceptionInfo ::  Could not create resource. Resource name: uiPreferences. Error code: npt.DUPLICATE.PREFERENCES Error message: Invalid Preference record. Cannot create or update preference record, UI Preference already exists.

Important: The error does NOT initially happen if the project has a created Status Report. The error does not happen if you create a new project in Modern UX. However once you set the Status module to be default and open at least one project with no Status report, then the error is thrown in different areas and may be just directly thrown in the logs also on insert of a new project

Environment

Release : 15.7.1 - 15.9

Component : CA PPM PROJECT MANAGEMENT

Resolution

This is DE58957, fixed in 15.9.1

 

WORKAROUND:

PART 1

Set the Status module NOT to be default module to open - This will avoid any further bad records until the bug is fixed

PART 2

(remove all bad records in CMN_UI_PREFERENCES table):

1. Find the record in the CMN_UI_PREFERENCES that causes the issue and delete it.

Example query (please replace the resource_id 5000000 with the correct one)

delete from cmn_ui_preferences where resource_name='projects' and resource_id=5000000;

commit;

This should resolve the problem.

 

If this is a lower environment, you may also truncate the table CMN_UI_PREFERENCES as the preferences get regenerated when accessed. Keep in mind that may affect some of the existing views so only use this if you're ok if those are lost.