要約

While attempting to delete an object type, error with message "Something went wrong. Contact administrator" appears on the top right side of the screen, and the object type is not deleted.

環境

Insight versions above 8.4

診断

• In the GUI, the object type to be deleted has no attributes and objects.
  
  
• In atlassian-jira.log, SQL exceptions below during the object type deletion can be seen.

2021-07-12 10:58:09,072+0000 http-nio-8080-exec-21 ERROR admin 658x5404734x1 wjwkfb 10.0.0.1 /rest/insight/1.0/objecttype/1234 [c.r.j.p.i.services.core.ObjectTypeServiceImpl] RuntimeException:There was a SQL exception thrown by the Active Objects library:
    Database:
      - name:MySQL
      - version:5.7.34
      - minor version:7
      - major version:5
    Driver:
      - name:MySQL Connector Java
      - version:mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 )
    
    com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`public`.`AO_8542F1_IFJ_OBJ`, CONSTRAINT `fk_ao_8542f1_ifj_obj_object_type_id` FOREIGN KEY (`OBJECT_TYPE_ID`) REFERENCES `AO_8542F1_IFJ_OBJ_TYPE` (`ID`))
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
  - name:MySQL
  - version:5.7.34
  - minor version:7
  - major version:5
Driver:
  - name:MySQL Connector Java
  - version:mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 )
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`public`.`AO_8542F1_IFJ_OBJ`, CONSTRAINT `fk_ao_8542f1_ifj_obj_object_type_id` FOREIGN KEY (`OBJECT_TYPE_ID`) REFERENCES `AO_8542F1_IFJ_OBJ_TYPE` (`ID`))
  at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.delete(EntityManagedActiveObjects.java:120)
  at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.delete(TenantAwareActiveObjects.java:282)

原因

1. Incomplete upgrade from Insight versions below 8.4. For some database types, there are some additional steps to be executed to change a table constraint before the upgrade as explained in Preparing for Insight Version 8.4.

2. This may happen when the first attempt to delete an object type fails due to race conditions or the deletion is interrupted in any way. There are some data in the database associated with the object type which has to be deleted first due to constraints.

   Expand here for a relation diagram of AO_8542F1_OBJ_TYPE

   

ソリューション

For Cause 1, follow the steps in this page to fix the table constraints according to your database type.

For Cause 2, here are some SQL queries to be executed to check for data to be removed in order to allow the object type to be deleted successfully. Due to the table constraints, it's recommended to run the SELECT and DELETE queries following the order below.

 These queries were written for MySQL and may have to be updated to work on another database:

1. Replace <object_type_id> with the ID of the object type to be deleted and execute all SQL queries below separately or as a script. 

    Object type ID can be retrieved from the URL in the GUI while browsing the object type e.g. <Jira_base_URL/secure/ObjectSchema.jspa?id=1&typeId=2&objectId=2.

   Queries for MySQL DB

   To check if any object-related data needs to be removed before deleting the object type

   /*This is to check if there's any object history needs to be deleted before an object can be deleted. Delete any row returned from the SQL.*/
   select * from AO_8542F1_IFJ_OBJ_HIST where object_id in (select id from AO_8542F1_IFJ_OBJ where object_type_id in (<object_type_id>));
   
   /*This is to check if there's any object attachment that needs to be deleted before an object can be deleted. Delete any row returned from the SQL.*/
   select * from AO_8542F1_IFJ_OBJ_ATTACH where object_id in (select id from AO_8542F1_IFJ_OBJ where object_type_id in (<object_type_id>));
   
   /*This is to check if there's any object watcher that needs to be deleted before an object can be deleted. Delete any row returned from the SQL.*/
   select * from AO_8542F1_IFJ_OBJ_WATCH where object_id in (select id from AO_8542F1_IFJ_OBJ where object_type_id in (<object_type_id>));
   
   /*This is to check if there's any object linked to a Jira issue which needs to be deleted before an object can be deleted. Delete any row returned from the SQL.*/
   select * from AO_8542F1_IFJ_OBJ_JIRAISSUE where object_id in (select id from AO_8542F1_IFJ_OBJ where object_type_id in (<object_type_id>));
   
   /*This is to check if there's any object comment that needs to be deleted before an object can be deleted. Delete any row returned from the SQL.*/
   select * from AO_8542F1_IFJ_COMMENT where object_id in (select id from AO_8542F1_IFJ_OBJ where object_type_id in (<object_type_id>));
   
   /*This is to make sure there's no object attribute value and object attribute left behind. Delete any row returned from these SQLs.*/
   select * from AO_8542F1_IFJ_OBJ_ATTR_VAL where referenced_object_id in (select id from AO_8542F1_IFJ_OBJ where referenced_object_id in (<object_type_id>));
   select * from AO_8542F1_IFJ_OBJ_ATTR_VAL where object_attribute_id in (select id from AO_8542F1_IFJ_OBJ_ATTR where object_id in (select id from AO_8542F1_IFJ_OBJ where object_type_id in (<object_type_id>)));
   select * from AO_8542F1_IFJ_OBJ_ATTR where object_id in (select id from AO_8542F1_IFJ_OBJ where object_type_id in (<object_type_id>));
   
   /*This is to check if there's any object of the object type that should have been deleted. Delete any row returned from the SQL.*/
   select * from AO_8542F1_IFJ_OBJ where object_type_id in (<object_type_id>);

   The following checks for the data to be removed before an object type can be removed successfully

   /*This is to check if there's any object type attributes in the import configurations that should have been deleted. Delete any row returned from the SQL.*/
   SELECT * FROM AO_8542F1_IFJ_IMPORT_SRC_OT WHERE OBJECT_TYPE_ID in (<object_type_id>);
   
   /*This is to check if there's any object type roles that should have been deleted. Delete any row returned from the SQL.*/
   SELECT * FROM AO_8542F1_IFJ_ROLE WHERE OBJECT_TYPE_ID in (<object_type_id>) ORDER BY ID ASC;
   
   /*This is to check if there's any object type attributes that should have been deleted. Delete any row returned from the SQL.*/
   SELECT * FROM AO_8542F1_IFJ_OBJ_TYPE_ATTR WHERE OBJECT_TYPE_ID in (<object_type_id>) OR REFERENCE_OBJECT_TYPE_ID in (<object_type_id>);
   
   /*This is to check if there's any Assets custom field configurations that the object type is used in. Before deleting any of the returned row, go through each custom field configurations to decide either you can proceed with the deletion or a modification of the configuration is more suitable.*/ 
   SELECT * FROM AO_8542F1_IFJ_CF_CONNECT WHERE OBJECT_TYPE_ID in (<object_type_id>);

   Queries for PostgreSQL DB

   /*This is to check if there's any object history needs to be deleted before an object can be deleted. Delete any row returned from the SQL.*/
   select * from "AO_8542F1_IFJ_OBJ_HIST" where "OBJECT_ID" in (select "ID" from "AO_8542F1_IFJ_OBJ" where "OBJECT_TYPE_ID" in (<object_type_id>));
   
   /*This is to check if there's any object linked to a Jira issue which needs to be deleted before an object can be deleted. Delete any row returned from the SQL.*/
   select * from "AO_8542F1_IFJ_OBJ_JIRAISSUE" where "OBJECT_ID" in (select "ID" from "AO_8542F1_IFJ_OBJ" where "OBJECT_TYPE_ID" in (<object_type_id>));
   
   /*This is to check if there's any object comment that needs to be deleted before an object can be deleted. Delete any row returned from the SQL.*/
   select * from "AO_8542F1_IFJ_COMMENT" where "OBJECT_ID" in (select "ID" from "AO_8542F1_IFJ_OBJ" where "OBJECT_TYPE_ID" in (<object_type_id>));
   
   /*This is to check if there's any object attachment that needs to be deleted before an object can be deleted. Delete any row returned from the SQL.*/
   select * from "AO_8542F1_IFJ_OBJ_ATTACH" where "OBJECT_ID" in (select "ID" from "AO_8542F1_IFJ_OBJ" where "OBJECT_TYPE_ID" in (<object_type_id>));
   
   /*This is to check if there's any object watcher that needs to be deleted before an object can be deleted. Delete any row returned from the SQL.*/
   select * from "AO_8542F1_IFJ_OBJ_WATCH" where "OBJECT_ID" in (select "ID" from "AO_8542F1_IFJ_OBJ" where "OBJECT_TYPE_ID" in (<object_type_id>));
   
   /*This is to make sure there's no object attribute value and object attribute left behind. Delete any row returned from these SQLs.*/
   select * from "AO_8542F1_IFJ_OBJ_ATTR_VAL" where "REFERENCED_OBJECT_ID" in (select "ID" from "AO_8542F1_IFJ_OBJ" where "OBJECT_TYPE_ID" in (<object_type_id>));
   select * from "AO_8542F1_IFJ_OBJ_ATTR_VAL" where "OBJECT_ATTRIBUTE_ID" in (select "ID" from "AO_8542F1_IFJ_OBJ_ATTR" where "OBJECT_ID" in (select "ID" from "AO_8542F1_IFJ_OBJ" where "OBJECT_TYPE_ID" in (<object_type_id>)));
   select * from "AO_8542F1_IFJ_OBJ_ATTR" where "OBJECT_ID" in (select "ID" from "AO_8542F1_IFJ_OBJ" where "OBJECT_TYPE_ID" in (<object_type_id>));
   
   /*This is to check if there's any object of the object type that should have been deleted. Delete any row returned from the SQL.*/
   select * from "AO_8542F1_IFJ_OBJ" where "OBJECT_TYPE_ID" in (<object_type_id>);
   
   /*This is to check if there's any object type attributes in the import configurations that should have been deleted. Delete any row returned from the SQL.*/
   SELECT * FROM "AO_8542F1_IFJ_IMPORT_SRC_OT" WHERE "OBJECT_TYPE_ID" in (<object_type_id>);
   
   /*This is to check if there's any object type roles that should have been deleted. Delete any row returned from the SQL.*/
   SELECT * FROM "AO_8542F1_IFJ_ROLE" WHERE "OBJECT_TYPE_ID" in (<object_type_id>);
   
   /*This is to check if there's any object type attributes that should have been deleted. Delete any row returned from the SQL.*/
   SELECT * FROM "AO_8542F1_IFJ_OBJ_TYPE_ATTR" WHERE "OBJECT_TYPE_ID" in (<object_type_id>) or "REFERENCE_OBJECT_TYPE_ID" in (<object_type_id>);  
   
   /*This is to check if there's any Assets custom field configurations that the object type is used in. Before deleting any of the returned row, go through each custom field configurations to decide either you can proceed with the deletion or a modification of the configuration is more suitable.*/   SELECT * FROM "AO_8542F1_IFJ_CF_CONNECT" WHERE "OBJECT_TYPE_ID" in (<object_type_id>);

2. After deleting all rows returned from each SQL, it's recommended to restart Jira and run a clean re-index of Insight to ensure the index is updated.
3. Now you can try to delete the object type from GUI again. The object type should be deleted successfully.