Getting ORA-00001: unique constraint error while performing some actions in JIRA Data Center
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
Summary
Scenario 1
Creating a Jira project fails with an error - Hmm... we couldn't create your project due to unknown error. Try refreshing the page to start again.
Scenario 2
Open any Agile board configuration page and get the following error:
1
2
3
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (jiradb.SYS_C00158781) violated
...
Caused by: Error : 1, Position : 0, Sql = INSERT INTO "AO_60DB71_CARDCOLOR" ("POS","RAPID_VIEW_ID","STRATEGY","VAL","COLOR") VALUES (:1 ,:2 ,:3 ,:4 ,:5 ) RETURNING ID INTO :6 , OriginalSql = INSERT INTO "AO_60DB71_CARDCOLOR" ("POS","RAPID_VIEW_ID","STRATEGY","VAL","COLOR") VALUES (?,?,?,?,?) RETURNING ID INTO ?, Error Msg = ORA-00001: unique constraint (jiradb.SYS_C00158781) violatedEnvironment
Jira Data Center 8. x and above.
Diagnosis
The following error appears in the atlassian-jira.log
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2023-10-09 14:57:33,915+0530 http-nio-8080-exec-3 ERROR XXXXXXXX 897x52914x1 6b9nqs 172.26.117.241 /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.$Proxy4232.create(Unknown Source)
at com.atlassian.greenhopper.web.rapid.view.RapidViewHelper.createRapidViewWithProjects(RapidViewHelper.java:337)
at com.atlassian.greenhopper.web.rapid.view.RapidViewHelper.createDefaultRapidViewForProject(RapidViewHelper.java:139)
...
Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:Oracle
- version:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
- minor version:1
Driver:
- name:Oracle JDBC driver
- version:19.3.0.0.0
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (JIRAUSER2.SYS_C0012463) violated
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:102)
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.create(TenantAwareActiveObjects.java:272)
at jdk.internal.reflect.GeneratedMethodAccessor4131.invoke(Unknown Source)
...
Caused by: Error : 1, Position : 0, Sql = INSERT INTO "AO_60DB71_ESTIMATESTATISTIC" ("FIELD_ID","RAPID_VIEW_ID","TYPE_ID") VALUES (:1 ,:2 ,:3 ) RETURNING ID INTO :4 , OriginalSql = INSERT INTO "AO_60DB71_ESTIMATESTATISTIC" ("FIELD_ID","RAPID_VIEW_ID","TYPE_ID") VALUES (?,?,?) RETURNING ID INTO ?, Error Msg = ORA-00001: unique constraint (JIRAUSER2.SYS_C0012463) violated
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
... 359 moreCause
List of probable causes:
Database operations like database migration, upgrades, etc. caused DB corruption.
Sequences are not correctly synced (it is set to a value lower than the biggest value on the table) which results in problems with sequences for AO tables.
The SQLIntegrityConstraintViolationException can also result when the last_number of user_sequence of the AO tables is less than the max (ID) of the AO tables user_ sequencesuser_sequences.
The issue can be with multiple AO tables irrespective of atlassian-jira.log showing only a particular table
Solution
Verify the list of impacted tables by getting the max(ID) and last_number of the user_sequences corresponding to each AO table:
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT max("ID") from AO_60DB71_ESTIMATESTATISTIC;
SELECT max("ID") from AO_60DB71_BOARDADMINS;
SELECT max("ID") from AO_60DB71_COLUMNSTATUS;
SELECT max("ID") from AO_60DB71_DETAILVIEWFIELD;
SELECT max("ID") from AO_60DB71_RAPIDVIEW;
SELECT max("ID") from AO_60DB71_LEXORANK;
SELECT max("ID") from AO_60DB71_QUICKFILTER;
SELECT max("ID") from AO_60DB71_SPRINT;
SELECT max("ID") from AO_60DB71_STATSFIELD;
SELECT max("ID") from AO_60DB71_SWIMLANE;
SELECT max("ID") from AO_60DB71_TRACKINGSTATISTIC;
SELECT max("ID") from AO_60DB71_WORKINGDAYS;
SELECT max("ID") from AO_60DB71_CARDCOLOR;1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_ESTIMATES2121821138';
SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_BOARDADMINS_ID_SEQ';
SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_COLUMNSTATUS_ID_SEQ';
SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_DETAILVIE629041006';
SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_RAPIDVIEW_ID_SEQ';
SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_LEXORANK_ID_SEQ';
SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_QUICKFILTER_ID_SEQ';
SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_SPRINT_ID_SEQ';
SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_STATSFIELD_ID_SEQ';
SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_SWIMLANE_ID_SEQ';
SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_TRACKINGS1474465725';
SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_WORKINGDAYS_ID_SEQ';
SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_CARDCOLOR_ID_SEQ';For each of the tables where last_number of the sequences is less than the max (ID), increment the value of the sequence to be more than the max (ID)
Let's take the example of the table AO_60DB71_BOARDADMINS:
The max (ID) value is 461:
1 2SELECT max("ID") from AO_60DB71_BOARDADMINS; Output is 461The last_number value is 61, which is lower than max (ID):
1 2SELECT last_number FROM user_sequences WHERE sequence_name = 'AO_60DB71_BOARDADMINS_ID_SEQ'; Output is 61In this case, we need to increase the value of last_number, so that it is higher than max ("ID")
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.
Then increase the value of the sequence such that it is more than the max (ID). You can increment the value by any number but it is safe to increment to the value max (ID) + 10
1
2
alter sequence AO_60DB71_BOARDADMINS_ID_SEQ increment by 471;
commit;Was this helpful?