Problem

There are multiple values stored for a single attribute with a cardinality maximum of 1. You can specify a minimum and maximum number of attribute values that can be associated with the attribute. This is common when you need to set an attribute multiple choices or required / mandatory.

For attributes of type "Default" you can only set maximum cardinality to 1, except "URL" and "Email" where you may space separate. In the following screenshot, the 'Updated' attribute is type "Default" so only one value is valid.

Diagnosis

Environment

• All Insight versions

Diagnostic Steps

• Duplicated attribute values when the attribute has cardinality maximum 1

• Assets clean re-index does not help

Cause

An object has more than one AttributeBean for the same objectTypeAttributeBean. 

Whenever an object is created, there is a record added to the database for every object type attribute if there's at least one value. There shouldn't be any record for any object attribute that is without a value (null) or duplicated object attribute as described in this article. The cause of the duplication is unknown.

Resolution

1. Please launch the hidden health check tool by accessing <Jira_base_URL>/secure/admin/InsightPluginHealthCheck.jspa in the browser as a Jira admin.
   

   
   

   The tool walks through all the values in the database and presents duplicates and which objects are affected. You could then manually inspect that the values are indeed duplicated. Things to consider about the InsightPluginHealthCheck tool:

   1. It's only reachable with the URL and not from any menu item.
   2. On the Insight Health Check page the goal is to identify the duplicates in the section Check duplicated attribute values
   3. if there are duplicated values, click on Delete duplicate values

   Limitations:
   The UI will timeout in 60 seconds meaning that if there are a lot of objects existing in the installation the visual of the tool might not work as expected. If this is the case the REST approach can be used. 
    

   Trigger via REST

   It is possible to trigger the check and the deletion via REST calls. This can sometimes be a preferred way. The endpoint is the same the difference is that you send a GET to check and a DELETE to delete. 
   Check duplicates
   GET /rest/insight/1.0/health/consistency/duplicates/attributevalues
   Delete duplicates
   DELETE /rest/insight/1.0/health/consistency/duplicates/attributevalues

   Logging:

   The atlassian-jira.log will contain log entries when the tool has run on WARN level. The class that does the logging is ConsistencyServiceImpl and one can check for entries such as Finding duplicates to delete, Removed duplicates for X objects
   
   

2. This tool will bypass the index and operates directly on the database level. If any deletion is performed, run an Assets clean re-index.
   
   

3. If the issue with the duplicated attribute values still persists, please follow the steps below.

   1. Identify the object type attribute ID which has duplicated values that shouldn't be allowed due to cardinality constraint. The object type attribute ID can be determined from the UI where the list of object type attributes can be found. For example, the ID of object type attribute, Source, is 381.
      
      

   2. Replace xxxxx in the SQL query below with the affected object type attribute ID and run the SQL query in Jira database.
      
      

      PostgreSQL / Microsoft SQL Server

      SELECT "OBJECT_ID", COUNT("OBJECT_ID") FROM "AO_8542F1_IFJ_OBJ_ATTR" WHERE "OBJECT_TYPE_ATTRIBUTE_ID"=xxxxx GROUP BY "OBJECT_ID" HAVING COUNT("OBJECT_ID") > 1;

      MySQL

      SELECT OBJECT_ID, COUNT(OBJECT_ID) FROM AO_8542F1_IFJ_OBJ_ATTR WHERE OBJECT_TYPE_ATTRIBUTE_ID=xxxxx GROUP BY OBJECT_ID HAVING COUNT(OBJECT_ID) > 1;

   3. The query above might return some results that look like the following.
      
      

      "OBJECT_ID","count("OBJECT_ID")"
      22491,4
      22508,6
      22550,3
      22842,1

   4. Verify if the objects in the query result are indeed the affected objects. 

   5. We'll have to delete these duplicated object attribute values and object attributes using the SQL queries below. 
      

      Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

      How to run these queries?

      1. Run query 1 first, follow by query 2.
      2. Notice how many rows got deleted from each query.
      3. Repeat query 1 again. If there were some rows deleted from query 1, proceed with query 2 again.
      4. Repeat query 1 and query 2 until 0 row got deleted from both. This means there are no longer duplicated object attribute or value.
      5. Run query 3 to confirm that there are no longer any object with duplicated object attribute or value.

      Query 1 - to remove duplicated object attribute value (PostgreSQL / Microsoft SQL Server)

      DELETE FROM "AO_8542F1_IFJ_OBJ_ATTR_VAL" WHERE "OBJECT_ATTRIBUTE_ID" IN (
          SELECT MIN(OA2."ID") AS TO_DELETE
          FROM "AO_8542F1_IFJ_OBJ_ATTR" OA2
          RIGHT JOIN (
              SELECT OTA."ID" AS OTA_ID, OTA."NAME", OA."OBJECT_ID" AS OA_OID, COUNT(*) AS SUBQ_COUNT
              FROM "AO_8542F1_IFJ_OBJ_TYPE_ATTR" OTA
              LEFT JOIN "AO_8542F1_IFJ_OBJ_ATTR" OA ON OTA."ID" = OA."OBJECT_TYPE_ATTRIBUTE_ID"
              GROUP BY OTA."ID", OTA."NAME", OA."OBJECT_ID"
              HAVING COUNT(*) > 1
          ) AS SUBQ ON OA2."OBJECT_TYPE_ATTRIBUTE_ID" = SUBQ.OTA_ID AND OA2."OBJECT_ID" = SUBQ.OA_OID
          GROUP BY OA2."OBJECT_ID", OA2."OBJECT_TYPE_ATTRIBUTE_ID"
      );

      Query 1 - to remove duplicated object attribute value (MySQL)

      DELETE FROM AO_8542F1_IFJ_OBJ_ATTR_VAL WHERE OBJECT_ATTRIBUTE_ID IN (
          SELECT MIN(OA2.ID) AS TO_DELETE
          FROM AO_8542F1_IFJ_OBJ_ATTR OA2
          RIGHT JOIN (
              SELECT OTA.ID AS OTA_ID, OTA.NAME, OA.OBJECT_ID AS OA_OID, COUNT(*) AS SUBQ_COUNT
              FROM AO_8542F1_IFJ_OBJ_TYPE_ATTR OTA
              LEFT JOIN AO_8542F1_IFJ_OBJ_ATTR OA ON OTA.ID = OA.OBJECT_TYPE_ATTRIBUTE_ID
              GROUP BY OTA.ID, OTA.NAME, OA.OBJECT_ID
              HAVING COUNT(*) > 1
          ) AS SUBQ ON OA2.OBJECT_TYPE_ATTRIBUTE_ID = SUBQ.OTA_ID AND OA2.OBJECT_ID = SUBQ.OA_OID
          GROUP BY OA2.OBJECT_ID, OA2.OBJECT_TYPE_ATTRIBUTE_ID
      );

      Query 2 - to remove duplicated object attribute (PostgreSQL / Microsoft SQL Server)

      DELETE FROM "AO_8542F1_IFJ_OBJ_ATTR" WHERE "ID" IN (
          SELECT MIN(OA2."ID") AS TO_DELETE
          FROM "AO_8542F1_IFJ_OBJ_ATTR" OA2
          RIGHT JOIN (
              SELECT OTA."ID" AS OTA_ID, OTA."NAME", OA."OBJECT_ID" AS OA_OID, COUNT(*) AS SUBQ_COUNT
              FROM "AO_8542F1_IFJ_OBJ_TYPE_ATTR" OTA
              LEFT JOIN "AO_8542F1_IFJ_OBJ_ATTR" OA ON OTA."ID" = OA."OBJECT_TYPE_ATTRIBUTE_ID"
              GROUP BY OTA."ID", OTA."NAME", OA."OBJECT_ID"
              HAVING COUNT(*) > 1
          ) AS SUBQ ON OA2."OBJECT_TYPE_ATTRIBUTE_ID" = SUBQ.OTA_ID AND OA2."OBJECT_ID" = SUBQ.OA_OID
          GROUP BY OA2."OBJECT_ID", OA2."OBJECT_TYPE_ATTRIBUTE_ID"
      );

      Query 2 - to remove duplicated object attribute (MySQL)

      DELETE FROM AO_8542F1_IFJ_OBJ_ATTR WHERE ID IN (
          SELECT MIN(OA2.ID) AS TO_DELETE
          FROM (SELECT * FROM AO_8542F1_IFJ_OBJ_ATTR) OA2
          RIGHT JOIN (
              SELECT OTA.ID AS OTA_ID, OTA.NAME, OA.OBJECT_ID AS OA_OID, COUNT(*) AS SUBQ_COUNT
              FROM AO_8542F1_IFJ_OBJ_TYPE_ATTR OTA
              LEFT JOIN AO_8542F1_IFJ_OBJ_ATTR OA ON OTA.ID = OA.OBJECT_TYPE_ATTRIBUTE_ID
              GROUP BY OTA.ID, OTA.NAME, OA.OBJECT_ID
              HAVING COUNT(*) > 1
          ) AS SUBQ ON OA2.OBJECT_TYPE_ATTRIBUTE_ID = SUBQ.OTA_ID AND OA2.OBJECT_ID = SUBQ.OA_OID
          GROUP BY OA2.OBJECT_ID, OA2.OBJECT_TYPE_ATTRIBUTE_ID
      );

      Query 3 - to check for objects with duplicated object attribute

      --Please replace xxxxx with the affected object type attribute ID as per step 3a above.
      SELECT "OBJECT_ID", COUNT("OBJECT_ID") FROM "AO_8542F1_IFJ_OBJ_ATTR" WHERE "OBJECT_TYPE_ATTRIBUTE_ID"=xxxxx GROUP BY "OBJECT_ID" HAVING COUNT("OBJECT_ID") > 1;

      Query 3 - to check for objects with duplicated object attribute

      --Please replace xxxxx with the affected object type attribute ID as per step 3a above.
      SELECT OBJECT_ID, COUNT(OBJECT_ID) FROM AO_8542F1_IFJ_OBJ_ATTR WHERE OBJECT_TYPE_ATTRIBUTE_ID=xxxxx GROUP BY OBJECT_ID HAVING COUNT(OBJECT_ID) > 1;

   6. Run another Assets reindex or clean reindex.
      

   7. Verify if the issue is still persisting.

This issue is being tracked in the following bug ticket. Please refer to it for updates: JSDSERVER-12010 - Getting issue details... STATUS