Unable to create Space in Confluence after upgrade or migration -ORA-00001: unique constraint Error - Due to Database Sequence being corrupted

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community


Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

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


Not able to create spaces in Confluence

Environment

Oracle Database

Diagnosis

Below errors can be observed in the logs

2023-04-25 09:57:20,101 ERROR [http-nio-8090-exec-1] [plugins.createcontent.exceptions.RestExceptionMapper] toResponse 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
    - minor version:0
    - major version:19
Driver:
    - name:Oracle JDBC driver
    - version:12.2.0.1.0

java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (CONFLNCP.SYS_C0051471) violated

 -- referer: https://kbdev.cn.ca/ | url: /rest/create-dialog/1.0/space-blueprint/dialog/web-items | traceId: 55bee42d14751aef | userName: xt32734
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
    - minor version:0
    - major version:19
Driver:
    - name:Oracle JDBC driver
    - version:12.2.0.1.0

java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (CONFLNCP.SYS_C0051471) violated


     at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:94)
EntityManagedActiveObjects.java:92)
    ... 446 more
Caused by: Error : 1, Position : 0, Sql = INSERT INTO "AO_54C900_C_TEMPLATE_REF" ("ID") VALUES (DEFAULT) RETURNING ID INTO :1 , OriginalSql = INSERT INTO "AO_54C900_C_TEMPLATE_REF" ("ID") VALUES (DEFAULT) RETURNING ID INTO ?, Error Msg = ORA-00001: unique constraint (CONFLNCP.SYS_C0051471) violated

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
    ... 466 more 
Caused by: Error : 1, Position : 0, Sql = INSERT INTO "AO_4789DD_TASK_MONITOR" ("TASK_ID","PROGRESS_MESSAGE","TASK_MONITOR_KIND","NODE_ID","TASK_STATUS","SERIALIZED_WARNINGS","CREATED_TIMESTAMP","SERIALIZED_ERRORS","PROGRESS_PERCENTAGE","CLUSTERED_TASK_ID") VALUES (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ) RETURNING ID INTO :11 , OriginalSql = INSERT INTO "AO_4789DD_TASK_MONITOR" ("TASK_ID","PROGRESS_MESSAGE","TASK_MONITOR_KIND","NODE_ID","TASK_STATUS","SERIALIZED_WARNINGS","CREATED_TIMESTAMP","SERIALIZED_ERRORS","PROGRESS_PERCENTAGE","CLUSTERED_TASK_ID") VALUES (?,?,?,?,?,?,?,?,?,?) RETURNING ID INTO ?, Error Msg = ORA-00001: unique constraint (CONFLNCP.SYS_C0051263) violated

Search for similar stack traces throughout the logs, to identify which tables have corrupted sequences.  Eg - AO_54C900_C_TEMPLATE_REF, AO_4789DD_TASK_MONITOR

Cause

  • Database operations like database migration, upgrade, etc. cause DB corruption.
  • Sequences are not correctly synced (it is set to a value lower than the biggest value on the table) this results in problem with sequences for AO tables

Solution

Fix for Table AO_4789DD_TASK_MONITOR 

  1. Get the table's sequence name.
SELECT TABS.TABLE_NAME
     , TRIGS.TRIGGER_NAME
     , SEQS.SEQUENCE_NAME
  FROM ALL_TABLES TABS
  JOIN ALL_TRIGGERS TRIGS ON (TRIGS.TABLE_OWNER = TABS.OWNER AND TRIGS.TABLE_NAME = TABS.TABLE_NAME)
  JOIN ALL_DEPENDENCIES DEPS ON (DEPS.OWNER = TRIGS.OWNER AND DEPS.NAME = TRIGS.TRIGGER_NAME)
  JOIN ALL_SEQUENCES SEQS ON (SEQS.SEQUENCE_OWNER = DEPS.REFERENCED_OWNER AND SEQS.SEQUENCE_NAME = DEPS.REFERENCED_NAME)
 WHERE TABS.TABLE_NAME = 'AO_4789DD_TASK_MONITOR' ;

2. Get the MAX ID from the table

SELECT COALESCE(MAX(ID), 0) FROM AO_4789DD_TASK_MONITOR 

3. Now let's see what's the sequence's  next value (sequence name retrieved from the first query):

SELECT <sequence_name>.NEXTVAL FROM DUAL ;
  • Ideally, the sequence value will be higher than the maximum ID value on that table.
  • If they're the same or the maximum ID is higher than the sequence value, we'll need to reset the sequence, as this could be causing the constraint violation errors.
  • We can do it by updating the sequence's value higher than the table.
  • For example, let's say the maximum ID is equal to 65 and the sequence is equal to 64
  • So in this scenario, say we have the sequence with a value of 64, and we want to set it to 70 or higher (just to be on the safe side).
  • Example (based on the scenario above):
ALTER SEQUENCE <sequence_name> INCREMENT BY 6 ;
SELECT <sequence_name>.NEXTVAL FROM DUAL ;
COMMIT ;
  • Adjust the values above based on the output of the SELECT queries (max ID, Sequence)  outlined above in this update.
  • Reset the sequence accordingly and then check in the logs if this error keeps popping up.
  • If this helps with the AO_4789DD_TASK_MONITOR table, you can follow the same steps for other tables that are mentioned above to fix their sequence issues as well.

(warning) Please ensure to take a complete backup of the database, before implementing the above steps in production

Last modified on Jul 11, 2023

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.