問題

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.

診断

環境

• JIRA is connected to a PostgreSQL database. This knowledge base article is not applicable to other databases. 

Diagnostic Steps

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 table sequence:

select last_value from "AO_60DB71_SWIMLANE_ID_seq";

If the number in the second query is lower than max("ID"), this KB applies.

原因

This is caused by broken Active Object table sequences. The root cause of this issue is still unknown.

If you began to see these errors after restoring a Jira XML backup, please raise a support request with Atlassian so that a support engineer can review the XML import logs. A broken sequence can be a symptom of a larger underlying problem, and running the steps in this article could make the problem worse.

ソリューション

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>;

e.g. in this 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, each table name in a line. e.g.: AO_tables.txt

   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.