Duplicate key value errors in logs in Jira Server using PostgreSQL
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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
Problem
When performing certain actions in Jira e.g. creating a project, errors such as the following may occur in the atlassian-jira.log file:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2018-07-13 06:07:46,553 https-jsse-nio-0.0.0.0-8443-exec-11 ERROR admin 367x1755232x1 hptn7q xx.xx.xx.xx /rest/project-templates/1.0/templates [c.a.jira.project.ProjectCreateRegistrarImpl] The handler with id com.atlassian.jira.project-templates-plugin:apply-project-template-handler threw an exception while handling a notification about a project being created
java.lang.reflect.UndeclaredThrowableException
at com.sun.proxy.$Proxy3307.create(Unknown Source)
at com.atlassian.greenhopper.web.rapid.view.RapidViewHelper.createRapidViewWithProjects(RapidViewHelper.java:380)
at com.atlassian.greenhopper.web.rapid.view.RapidViewHelper.createDefaultRapidViewForProject(RapidViewHelper.java:146)
...
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.atlassian.activeobjects.tx.TransactionalProxy.invoke(TransactionalProxy.java:60)
... 254 more
Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:9.5.10
- minor version:5
- major version:9
- name:PostgreSQL Native Driver
- version:PostgreSQL 9.4.1212
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_60DB71_SWIMLANE_pkey"
Detail: Key ("ID")=(268) already exists.Environment
Jira connected to a PostgreSQL database. This knowledge base article is not applicable to other databases.
Diagnosis
Check the max id currently used in the AO table mentioned in the error e.g. AO_60DB71_SWIMLANE table:
1
select max("ID") from "AO_60DB71_SWIMLANE";Check the table sequence:
1
select last_value from "AO_60DB71_SWIMLANE_ID_seq";If the number in the second query is lower than the one in the first query, then this KB applies.
Cause 1
This is caused by broken Active Object table sequences. The root cause of this issue is still unknown.
Cause 2
You began to see these errors after restoring a Jira XML backup.
Solution
Resolution for Cause 1
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
ℹ️ On Linux servers, you can find out all the impacted tables for which this type of error occurs by navigating to <JIRA_HOME>/log directory and issuing the following command:
1
grep 'violates unique constraint' atlassian-jira.log*|sed -n 's/.*"\([^"]*\)_pkey.*/\1/p'|sort|uniqAfter running the DB queries, restart Jira for the changes to take effect.
For one table
You can dynamically fix the sequence for each affected table mentioned in the logs with the following SQL query:
1
SELECT setval('"<table_name>_ID_seq"', (SELECT MAX("ID") FROM "<table_name>")+10);e.g. for AO_60DB71_SWIMLANE:
1
SELECT setval('"AO_60DB71_SWIMLANE_ID_seq"', (SELECT MAX("ID") FROM "AO_60DB71_SWIMLANE")+10);Alternatively
Run the first 2 SQL queries in the Diagnostic section above
Run the following SQL query to reset the affected table sequence:
1
ALTER SEQUENCE "<table_name>_ID_seq" RESTART WITH <(max ID from first SQL query in diagnostic steps) + 10>;For instance, this would be the query for the previous AO_60DB71_SWIMLANE example:
1
ALTER SEQUENCE "AO_60DB71_SWIMLANE_ID_seq" RESTART WITH 278;For all AO tables
Pull the list of all AO tables in your DB in a text file (e.g., AO_tables.txt) each table name in a line:
1 2 3 4 5 6 7 8 9AO_4B00E6_UPGRADE_BACKUP AO_CBC281_DEF_LIB_FOR_GROUP AO_8542F1_IFJ_ICON_THEME AO_8542F1_IFJ_ON_TYPE_CONFIG AO_F90A7B_ZENDESK_DS AO_F90A7B_ZENDESK_SELL_DS AO_3C6513_XPORTER_PRJ_SETTING AO_3C6513_XPORTER_TEMPLATE AO_3C6513_XPORTER_AUDITIf you're not sure how to obtain the list of AO table names, you can use the following SQL query:
1SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'AO_%' ORDER BY table_name;Based on the list of tables, automatically build SQL queries to update the sequence in a SQL script (e.g.: fix_seq.sql):
1for table in `cat AO_tables.txt`; do echo "SELECT setval('\"${table}_ID_seq\"', (SELECT MAX(\"ID\") FROM \"${table}\")+10);"; done > fix_seq.sqlYou will end up with a list of queries in fix_seq.sql similar to the ones below and ready to be executed:
1 2 3 4 5 6 7 8 9SELECT setval('"AO_4B00E6_UPGRADE_BACKUP_ID_seq"', (SELECT MAX("ID") FROM "AO_4B00E6_UPGRADE_BACKUP")+10); SELECT setval('"AO_CBC281_DEF_LIB_FOR_GROUP_ID_seq"', (SELECT MAX("ID") FROM "AO_CBC281_DEF_LIB_FOR_GROUP")+10); SELECT setval('"AO_8542F1_IFJ_ICON_THEME_ID_seq"', (SELECT MAX("ID") FROM "AO_8542F1_IFJ_ICON_THEME")+10); SELECT setval('"AO_8542F1_IFJ_ON_TYPE_CONFIG_ID_seq"', (SELECT MAX("ID") FROM "AO_8542F1_IFJ_ON_TYPE_CONFIG")+10); SELECT setval('"AO_F90A7B_ZENDESK_DS_ID_seq"', (SELECT MAX("ID") FROM "AO_F90A7B_ZENDESK_DS")+10); SELECT setval('"AO_F90A7B_ZENDESK_SELL_DS_ID_seq"', (SELECT MAX("ID") FROM "AO_F90A7B_ZENDESK_SELL_DS")+10); SELECT setval('"AO_3C6513_XPORTER_PRJ_SETTING_ID_seq"', (SELECT MAX("ID") FROM "AO_3C6513_XPORTER_PRJ_SETTING")+10); SELECT setval('"AO_3C6513_XPORTER_TEMPLATE_ID_seq"', (SELECT MAX("ID") FROM "AO_3C6513_XPORTER_TEMPLATE")+10); SELECT setval('"AO_3C6513_XPORTER_AUDIT_ID_seq"', (SELECT MAX("ID") FROM "AO_3C6513_XPORTER_AUDIT")+10);Some of the queries may fail because the AO table doesn't have an ID column, these failure can be ignored.
Resolution for Cause 2
A broken sequence can be a symptom of a larger underlying problem, so in this case, it's recommended to inspect the XML restore / import logs to see if there any plugin table causing the restore to fail, specially from 3rd party plugins.
3rd partly plugins Structure and Structure.Gantt are known to cause this issue for example, so in this case, you can disable the problematic plugins and run the restore again which should complete without issue as you would get rid of database ID problems.
You may raise a support request with Atlassian so that a support engineer can review the XML restore / import logs.
Was this helpful?