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:

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:

select max("ID") from "AO_60DB71_SWIMLANE";

Check the table sequence:

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.

Resolution for Cause 1

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:

grep 'violates unique constraint' atlassian-jira.log*|sed -n 's/.*"\([^"]*\)_pkey.*/\1/p'|sort|uniq

For one table

You can dynamically fix the sequence for each affected table mentioned in the logs with the following SQL query:

SELECT setval('"<table_name>_ID_seq"', (SELECT MAX("ID") FROM "<table_name>")+10);

e.g. for AO_60DB71_SWIMLANE: 

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:

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:

ALTER SEQUENCE "AO_60DB71_SWIMLANE_ID_seq" RESTART WITH 278;

For all AO tables

1. 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:

   AO_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_AUDIT

   If you're not sure how to obtain the list of AO table names, you can use the following SQL query:

   SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'AO_%' ORDER BY table_name;

2. Based on the list of tables, automatically build SQL queries to update the sequence in a SQL script (e.g.: fix_seq.sql):

   for table in `cat AO_tables.txt`; do echo "SELECT setval('\"${table}_ID_seq\"', (SELECT MAX(\"ID\") FROM \"${table}\")+10);"; done > fix_seq.sql

3. You will end up with a list of queries in fix_seq.sql similar to the ones below and ready to be executed: 

   SELECT 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.