Duplicate primary key value error

Owned by Svetoslav Todorov
Last updated: Jul 15, 2020

Problem

When creating or deploying a snapshot a similar error occurs:

PostgreSQL:

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 "<TABLE NAME>"
  Detail: Key ("ID")=(268) already exists.

MySQL:

Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
name:MySQL
version:5.7.23-enterprise-commercial-advanced
minor version:7
major version:5
Driver:
name:MySQL Connector Java
version:mysql-connector-java-5.1.48 ( Revision: 29734982609c32d3ab7e5cac2e6acee69ff6b4aa )
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0' for key 'PRIMARY'
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:95)
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.create(TenantAwareActiveObjects.java:267)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

These errors can be thrown for multiple tables: 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, AO_3A112F_AUDIT_LOG_ENTRY, AO_3A112F_AUDIT_DETAILS_ENTRY. Theoretically this can occur in any Active Objects table.

Diagnosis

Environment

  • Jira is connected to a PostgreSQL database. The following diagnostic steps are only applicable for PostgreSQL.
  • Jira is connected to a MySQL database. This case is very unlikely, but still possible.

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 * from "AO_60DB71_SWIMLANE_ID_seq";

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

Cause

This is caused by broken Active Object table sequences. The root cause of this issue is still unknown. However, it may relate to errors that occurred during a recent XML system restore. If this holds true, we recommend contacting Atlassian with details around the system restore so they can investigate this.

Resolution

For a PostgreSQL database apply the fix explained in Atlassian's article here - https://confluence.atlassian.com/jirakb/duplicate-key-value-errors-in-logs-in-jira-server-using-postgresql-958771364.html

For a MySQL database, the fix would be similar to the one specified for PostgreSQL, but the AUTO_INCREMENT has to be adjusted to be greater than the highest ID in the table.