Unable to create or edit any Assets schema, object type or object-related data due to database exception which says the key already exists
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Admins and users are unable to perform actions like creating new schema/object type/object, editing existing schema/object type/object
Environment
Any Jira Service Management environments with Assets in use and usually with XML backup restored from another instance
Diagnosis
There are errors saying that a key already exists can be found in the atlassian-jira.log.
2024-04-04 14:14:14,444+0400 http-nio-8080-exec-1 ERROR admin 857x63089x1 4dvvzo 192.168.80.16,192.168.51.195 /rest/insight/1.0/objectschema/create [c.r.j.p.i.services.core.ObjectSchemaServiceImpl] RuntimeException:There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:12.11
- minor version:11
- major version:12
Driver:
- name:PostgreSQL JDBC Driver
- version:42.2.23
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_8542F1_IFJ_OBJ_SCHEMA_pkey"
Detail: Key ("ID")=(4) already exists.
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:12.11
- minor version:11
- major version:12
Driver:
- name:PostgreSQL JDBC Driver
- version:42.2.23
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_8542F1_IFJ_OBJ_SCHEMA_pkey"
Detail: Key ("ID")=(4) already exists.
...
2022-02-14 10:00:33,733+0100 http-nio-8080-exec-17 url: /rest/insight/1.0/objecttype/398/delete/objects; user: admin ERROR z00484bw 600x68714x1 72eeji 165.225.123.191,10.130.95.29 /rest/insight/1.0/objecttype/398/delete/objects [c.r.j.p.i.services.core.ObjectServiceImpl] RuntimeException:There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:12.8
- minor version:8
- major version:12
Driver:
- name:PostgreSQL JDBC Driver
- version:42.2.23
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_8542F1_IFJ_OBJ_ATTR_VAL_pkey"
Detail: Key ("ID")=(1) already exists.
The error usually also occurs for other Assets tables. You can use the grep command below to check.
$ grep -h -R -i 'Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint' atlassian-jira.* | grep 'AO_8542F1_' | awk '{print $11}' | sort | uniq
Cause
The root cause is unknown but the error means that the database is trying to save records with keys ("ID" in the examples above) that had already been used
Solution
The solution is to use a new empty database and restore an XML backup file to populate the desired data.
- Create a new empty database according to our recommendations (see Connecting Jira applications to a database).
- Connect the new database to the affected Jira instance.
- Restore the XML backup again into this environment.