Summary

When trying to add a Custom Field to any Advanced Roadmap plan via the page ⚙ (Plan Settings) > Configure > Custom Fields, a pop-up with the error "Something went wrong" appears in the UI.

When clicking on the View Log link, we can see the exception below:

Stack Trace
Type: com.atlassian.rm.common.basics.persistence.PersistenceException

com.atlassian.rm.jpo.core.plancustomfield.sql.QueryDslPlanCustomFieldPersistence.insert:153
com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService.lambda$null$2:104
com.atlassian.rm.common.persistence.transaction.Registry.run:43
com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService.lambda$addCustomFieldsToPlan$3:101
com.atlassian.rm.common.persistence.transaction.TransactionHandler.lambda$reuseExistingDatabaseTransaction$2:55
com.atlassian.rm.common.persistence.transaction.TransactionHandler.inTransaction:111
com.atlassian.rm.common.persistence.transaction.TransactionHandler.inNewTransaction:99
com.atlassian.rm.common.persistence.transaction.TransactionHandler.lambda$reuseExistingDatabaseTransaction$0:34
com.atlassian.rm.common.persistence.DefaultDatabaseProvider.lambda$run$0:24
com.atlassian.rm.common.persistence.env.JiraDatabaseProvider.lambda$execute$0:34
com.atlassian.rm.common.bridges.jira.persistence.JiraDatabaseProviderBridgeImpl.execute:59
com.atlassian.rm.common.persistence.env.JiraDatabaseProvider.execute:34
com.atlassian.rm.common.persistence.DefaultDatabaseProvider.run:24
com.atlassian.rm.common.persistence.transaction.TransactionHandler.reuseExistingDatabaseTransaction:33
com.atlassian.rm.common.persistence.transaction.TransactionHandler.reuseExistingDatabaseTransaction:59
com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService.addCustomFieldsToPlan:99
com.atlassian.rm.jpo.rest.service.customfield.plan.PlanCustomFieldRestEndpoint.add:55

(TRIMMED)

The purpose of this KB article is to describe a possible root cause for this error, how to identify it, and how to fix it.

Environment

• Jira Software Server / Data Center on any version 8.0.0
• Advanced Roadmap (formerly called Portfolio) on any version compatible with Jira 8.x

Diagnosis

• Look for the following error in the Jira application logs

  2023-10-30 11:25:31,259+0000 http-nio-8080-exec-17 url: /jira/rest/jpo/1.0/customfield/plan/add; user: admin ERROR admin 685x533x1 q5bs9g XXX.XX.XXX.XX /rest/jpo/1.0/customfield/plan/add [c.a.r.c.r.provider.exception.DefaultExceptionMapper] com.querydsl.core.QueryException: Caught SQLIntegrityConstraintViolationException for insert into "AO_D9132D_PLAN_CUSTOM_FIELD" ("C_KEY", "CUSTOM_FIELD_ID", "PLAN_ID", "FILTERING_ALLOWED") values (?, ?, ?, ?)
  com.atlassian.rm.common.basics.persistence.PersistenceException: com.querydsl.core.QueryException: Caught SQLIntegrityConstraintViolationException for insert into "AO_D9132D_PLAN_CUSTOM_FIELD" ("C_KEY", "CUSTOM_FIELD_ID", "PLAN_ID", "FILTERING_ALLOWED") values (?, ?, ?, ?)
  	at com.atlassian.rm.jpo.core.plancustomfield.sql.QueryDslPlanCustomFieldPersistence.insert(QueryDslPlanCustomFieldPersistence.java:153)
  	at com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService.lambda$null$2(DefaultPlanCustomFieldService.java:104)
  	at com.atlassian.rm.common.persistence.transaction.Registry.run(Registry.java:43)
  	at com.atlassian.rm.jpo.core.plancustomfield.DefaultPlanCustomFieldService.lambda$addCustomFieldsToPlan$3(DefaultPlanCustomFieldService.java:101)
  	at com.atlassian.rm.common.persistence.transaction.TransactionHandler.lambda$reuseExistingDatabaseTransaction$2(TransactionHandler.java:55)
  	at com.atlassian.rm.common.persistence.transaction.TransactionHandler.inTransaction(TransactionHandler.java:111)
  	at com.atlassian.rm.common.persistence.transaction.TransactionHandler.inNewTransaction(TransactionHandler.java:99)
  
  ...
  
  Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("ATLASSIAN"."AO_D9132D_PLAN_CUSTOM_FIELD"."ID")
  
  	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
  	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
  
  
  ...
  
  	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1061)
  	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
  	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
  	at com.atlassian.jira.ofbiz.sql.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:47)
  	at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeUpdate$7(DiagnosticPreparedStatement.java:69)
  	at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:91)
  	at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:62)
  	at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.executeUpdate(DiagnosticPreparedStatement.java:69)
  	at com.querydsl.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:335)
  	... 308 more
  Caused by: Error : 1400, Position : 0, Sql = insert into "AO_D9132D_PLAN_CUSTOM_FIELD" ("C_KEY", "CUSTOM_FIELD_ID", "PLAN_ID", "FILTERING_ALLOWED") values (:1 , :2 , :3 , :4 ) RETURNING ID INTO :5 , OriginalSql = insert into "AO_D9132D_PLAN_CUSTOM_FIELD" ("C_KEY", "CUSTOM_FIELD_ID", "PLAN_ID", "FILTERING_ALLOWED") values (?, ?, ?, ?) RETURNING ID INTO ?, Error Msg = ORA-01400: cannot insert NULL into ("ATLASSIAN"."AO_D9132D_PLAN_CUSTOM_FIELD"."ID")
  
  	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
  	... 330 more

• Check if the Oracle Trigger for the DB table AO_D9132D_PLAN_CUSTOM_FIELD is missing by following the steps below:

  • Log into the Oracle DB as the same user that the Jira application uses to connect to the Database, and run the following SQL query:

    select * from user_triggers where "TRIGGER_NAME" = 'AO_D9132D_PLAN_CUST1651949523';

  • Normally, this query should return a row similar to the one below, which means that there is a trigger in the user_triggers table used for the table AO_D9132D_PLAN_CUSTOM_FIELD:

    SQL> select * from user_triggers where "TRIGGER_NAME" = 'AO_D9132D_PLAN_CUST1651949523';
    
    "TRIGGER_NAME","TRIGGER_TYPE","TRIGGERING_EVENT","TABLE_OWNER","BASE_OBJECT_TYPE","TABLE_NAME","COLUMN_NAME","REFERENCING_NAMES","WHEN_CLAUSE","STATUS","DESCRIPTION","ACTION_TYPE","TRIGGER_BODY","CROSSEDITION","BEFORE_STATEMENT","BEFORE_ROW","AFTER_ROW","AFTER_STATEMENT","INSTEAD_OF_ROW","FIRE_ONCE","APPLY_SERVER_ONLY"
    "AO_D9132D_PLAN_CUST1651949523","BEFORE EACH ROW","INSERT","ATLASSIAN","TABLE","AO_D9132D_PLAN_CUSTOM_FIELD",,"REFERENCING NEW AS NEW OLD AS OLD",,"ENABLED","""AO_D9132D_PLAN_CUST1651949523""
    BEFORE INSERT
        ON ""AO_D9132D_PLAN_CUSTOM_FIELD""   FOR EACH ROW
    ","PL/SQL     ","BEGIN
        SELECT ""AO_D9132D_PLAN_CUST2001258595"".NEXTVAL INTO :NEW.""ID"" FROM DUA","NO","NO","NO","NO","NO","NO","YES","NO"

  • If this query does not return any row, then this KB article is relevant  and you can move on to the Cause and Solution sections.

Cause

The Oracle DB trigger for the table AO_D9132D_PLAN_CUSTOM_FIELD is missing, preventing the Jira application from inserting new rows to this table, and causing the SQL exception "Error Msg = ORA-01400: cannot insert NULL into ("ATLASSIAN"."AO_D9132D_PLAN_CUSTOM_FIELD"."ID")" in the Jira logs.

Normally this trigger should be there by default in any fresh Jira installation using Advanced RoadMap. However, it is possible that the trigger got accidentally deleted manually in the Oracle DB, which is outside of Jira's control.

Solution

To fix this error, we need to manually re-create the missing trigger in the Oracle DB by following the steps below:

• Log into the Oracle DB as the same user that the Jira application uses to connect to the Database, and run the following SQL query:

• Run the following command to re-create the missing trigger:

  create or replace TRIGGER "AO_D9132D_PLAN_CUST1651949523"
  BEFORE INSERT
      ON "AO_D9132D_PLAN_CUSTOM_FIELD" FOR EACH ROW
  BEGIN
      SELECT "AO_D9132D_PLAN_CUST2001258595".NEXTVAL INTO :NEW."ID" FROM DUAL;
  END;

• Run the following SQL query and confirm that you are now getting some result

  select * from user_triggers where "TRIGGER_NAME" = 'AO_D9132D_PLAN_CUST1651949523';

• Try again to add a Custom Field to the Plan in Jira

If these steps do not help resolve the issue, please reach out to Atlassian Support.