CMS Snapshot Analysis - Synchronize Assessment Model - Fatal Error - duplicate key value violates unique contraint dss_me_val_typ_pk

This page will help troubleshoot the following fatal error as shown in the screenshot below:

Or messages in the CAST-MS log that occur during the synchronize assessment model like the messages below:

ERR: 2018-10-09 02:57:15: ERROR: duplicate key value violates unique constraint "dss_me_val_typ_pk"
Detail: Key (metric_id, metric_value_index)=(1039020, 0) already exists.
com.castsoftware.java.ExternalException:ERROR: duplicate key value violates unique constraint "dss_me_val_typ_pk"
Detail: Key (metric_id, metric_value_index)=(1039020, 0) already exists.
...
Connection Error: insert into <Central DB>.DSS_METRIC_VALUE_TYPES (METRIC_ID, METRIC_VALUE_INDEX, METRIC_VALUE_NAME, METRIC_VALUE_DESCRIPTION, METRIC_VALUE_PROCEDURE_NAME, METRIC_AGGREGATE_OPERATOR, METRIC_VALUE_PROCEDURE_NAME_2, METRIC_AGGREGATE_OPERATOR_2, METRIC_VALUE_TYPE) values (?,?,?,?,?,?,?,?,?)
com.castsoftware.java.InternalException:Connection Error: insert into <Central DB>.DSS_METRIC_VALUE_TYPES (METRIC_ID, METRIC_VALUE_INDEX, METRIC_VALUE_NAME, METRIC_VALUE_DESCRIPTION, METRIC_VALUE_PROCEDURE_NAME, METRIC_AGGREGATE_OPERATOR, METRIC_VALUE_PROCEDURE_NAME_2, METRIC_AGGREGATE_OPERATOR_2, METRIC_VALUE_TYPE) values (?,?,?,?,?,?,?,?,?)
...
INF: 2018-10-09 02:57:15: Task message: ERROR: duplicate key value violates unique constraint "dss_me_val_typ_pk"
Detail: Key (metric_id, metric_value_index)=(1039020, 0) already exists.
INF: 2018-10-09 02:57:15: No associated log file
INF: 2018-10-09 02:57:15: ending Task Synchronize Assessment Model in <Central DB>

1. Open the CAST-MS log file that is located in
2. If the metric id value in the error message is less than 70,000, edit the central schema, in the assessment model tab, run the synchronize assessment model action in CAST-MS to repair the standard assessment model items. Here is the documentation for this action: CMS - Dashboard Service editor
   1. To clarify further, if the error message is the following then this can be applied as 66064 < 70000:
      Detail: Key(metric_id, metric_value_index)=(66064,0) already exists.
3. See if one of the extensions being used is the version of JEE technology rules extension below version 1.0.0-funcrel (beta or alpha 1.0.0 version of this extension). (see this TKB page if you need help with this task: SQL Queries - Common SQL Queries - How to get the list of installed extensions)
   1. If it is then you can upgrade to 1.0.0-funcrel or above to resolve this issue

   2. Alternatively, run the following SQL queries on the central database and then run the snapshot again to resolve the issue:

      DELETE
      FROM   dss_metric_descriptions
      WHERE  metric_id BETWEEN 1039002 AND    1039024;
      
      DELETE
      FROM   dss_metric_type_trees
      WHERE  metric_id BETWEEN 1039002 AND    1039024;
      
      DELETE
      FROM   dss_metric_value_types
      WHERE  metric_id BETWEEN 1039002 AND    1039024;
      
      DELETE
      FROM   dss_metric_types
      WHERE  metric_id BETWEEN 1039002 AND    1039024;
      
      DELETE
      FROM   DSS_METRIC_STATUS_THRESHOLDS
      WHERE  metric_id BETWEEN 1039002 AND    1039024;

4. If the extension being used is JEE technology rules extension 1.0.0-funcrel or greater, then check the CAST MS log file gives clues about the data that are involved in the fatal error:
   
   1. In the above example the data involved : (metric_id, metric_value_index= = (1039020, 0)
   2. The table involved is <CENTRAL DB>.DSS_METRIC_VALUE_TYPES

5. As we can see in the stack trace, the table involved in the issue is the table dss_metric_value_types that is the central DB. Run the following query:

   select * from <CENTRAL_DB>.DSS_METRIC_VALUE_TYPES
   WHERE	 METRI_ID = <THE METRIC ID FOUND>  -- In the above example metric id is 1039020

   1. If there is no rows, run the synchronize assessment model again from CAST MS, the issue shoud not occur again:
      1. In the Execute tab, in the Dashboard Services section, double click the Central DB:
         
      2. In the Central DB editor, go to Assessment Model and click synchronize assessment model
         
         
         
   2. If there is at least one row, you need to clean metric related to the Extension involved in the assessment model. To do so, we need first to identify the extension / UA profile that is leading to that issue:
      1. Get the metric_id that is failing. In the current example, the metric_id is : 1039020
      2. Open Notepad++
      3. Search in your extension folder, with fileter based on the file ADG_ConfigData.xml
6. If the above points do not help you, contact CAST Technical Support and provide the below relevant input in order to reproduce the issue

Relevant Input

• Activate the SQL Traces for CAST MS as described in : CAST Management Studio - Information - How to launch CAST-MS in SHOW SQL mode in order to get the SQL traces, and run the Snapshot by skipping analysis
• CAST Support Tool (CST) - alias Sherlock. Option to select : Export CAST Databases, Export log, Export configuration file.
• Screenshot describing the error/warning

This page will help you solve fatal error that may occur during the Synchronize Assessment Model.