Unable to export object schema with objects due to duplicated object attribute values
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
The export of object schema with objects would fail with error "Duplicate key" in the log if there are duplicated object attribute values in the database
Environment
Any Insight versions
Diagnosis
Error similar to the following can be seen in <Jira_home or Jira_shared_home>/log/insight_objectschema_export.log
2021-10-21 12:45:48,977 Thread-4965 ERROR insight_admin 765x561922x1 6avqwz 95.142.17.205,192.168.56.101 /rest/insight/1.0/objectschemaexport/export/server [c.r.j.p.i.s.imports.schema.ObjectSchemaExportServiceImpl] Error when exporting object schema and objects, object schema id: 5
java.lang.IllegalStateException: Duplicate key 31706 (attempted merging values ObjectAttributeBean [objectTypeAttributeId=31706, objectId=16472] and ObjectAttributeBean [objectTypeAttributeId=31706, objectId=16472])
at java.base/java.util.stream.Collectors.duplicateKeyException(Collectors.java:133)
at java.base/java.util.stream.Collectors.lambda$uniqKeysMapAccumulator$1(Collectors.java:180)
at java.base/java.util.stream.ReduceOps$3ReducingSink.accept(ReduceOps.java:169)
at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1655)
at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
at com.riadalabs.jira.plugins.insight.services.imports.ObjectExportServiceImpl.getObjectAttributes(ObjectExportServiceImpl.java:153)
at com.riadalabs.jira.plugins.insight.services.imports.ObjectExportServiceImpl.writeObjects(ObjectExportServiceImpl.java:175)
at com.riadalabs.jira.plugins.insight.services.imports.ObjectExportServiceImpl.exportObjects(ObjectExportServiceImpl.java:131)
at com.riadalabs.jira.plugins.insight.services.imports.schema.ObjectSchemaExportServiceImpl.exportData(ObjectSchemaExportServiceImpl.java:383)
at com.riadalabs.jira.plugins.insight.services.imports.schema.ObjectSchemaExportServiceImpl.exportSchemaToServerAsync(ObjectSchemaExportServiceImpl.java:250)
at com.riadalabs.jira.plugins.insight.services.imports.schema.ObjectSchemaExportServiceImpl.lambda$exportSchemaWithObjectsToServer$0(ObjectSchemaExportServiceImpl.java:206)
...
The error above is saying that a duplicate of object type attribute with ID 31706 is found and hence, export failed.
We can use the SQL query below to verify the issue in the database by returning a list of objects that has a duplicated attribute value for object type attribute with ID 31706. Example results are shown below.
jira=# SELECT "OBJECT_ID", count("OBJECT_ID") FROM "AO_8542F1_IFJ_OBJ_ATTR" WHERE "OBJECT_TYPE_ATTRIBUTE_ID"=31706 GROUP BY "OBJECT_ID" HAVING count("OBJECT_ID") > 1;
OBJECT_ID | count
-----------+-------
17225 | 2
13624 | 2
13095 | 2
12860 | 2
16460 | 2
(5 rows)
Alternatively, we could use the built-in Insight health check tool to check for duplicated attribute values. The Insight health check tool can be accessed by any Jira admins on <Jira_base_URL>/secure/admin/InsightPluginHealthCheck.jspa.
Here's an example screenshot from the health check tool when duplicated values are found.
Cause
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 type attribute that is without a value (null) or duplicated object type attribute as shown above.
The cause of the duplication is unknown.
Solution
The duplicated values can be deleted by clicking on the Delete duplicated values button available on the health check page. It's recommended to run an Insight clean re-index or Insight re-index after deleting the duplicated values.
Other Notes
The operation to check for duplicate values and to delete could cause Jira performance issue if the Jira instance is busy and with large amount of Insight objects. It's recommended to perform the operation(s) during non-peak hours.