Cannot Delete Object Types on Insight Asset Management due to database constraint violation exception

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.

Summary

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.


Environment

Insight versions above 8.4

Diagnosis

  • 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)


Causes

  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 failed due to race condition or aborted. There are some data in the database associated with the object type which has to be deleted first due to constraints.

Solutions

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.

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

  1. Execute each SQL queries below. Replace <object_type_id> with the ID of the object type to be deleted. (info) 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.

    /*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 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 MATCH_IDENTIFIER_IGNORE_CASE,MISSING_OBJ_ATTR_ID,UPDATED,EMPTY_VALUES,CREATED,DESCRIPTION,MISSING_OBJ_THRESHOLD_TYPE,MISSING_OBJ_THRESHOLD,PARENT_IMPORT_SOURCE_OTID,UNKNOWN_VALUES,IMPORT_SOURCE_ID,STATUS,HANDLE_MISSING_OBJ_TYPE,ID,HANDLE_MISSING_OBJ_REFERENCES,SELECTOR_IQL,SELECTOR,MISSING_OBJ_ATTR_VALUE,OBJECT_TYPE_ID FROM AO_8542F1_IFJ_IMPORT_SRC_OT 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 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 type roles that should have been deleted. Delete any row returned from the SQL.*/
    SELECT DESCRIPTION,ID,TYPE,NAME 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 TYPE,NAME,TYPE_VALUE,INCLUDE_CHILD_OBJECT_TYPES,REGEX_VALIDATION,DESCRIPTION,`LABEL`,ADDITIONAL_VALUE,MAXIMUM_CARDINALITY,SUMMABLE,INDEXED,SUFFIX,HIDDEN,REMOVABLE,CREATED,MINIMUM_CARDINALITY,OPTIONS,UPDATED,OTA_POSITION,DEFAULT_TYPE_ID,ID,IQL,UNIQUE_ATTRIBUTE FROM AO_8542F1_IFJ_OBJ_TYPE_ATTR WHERE OBJECT_TYPE_ID in (<object_type_id)
    /*This is to check if there's any Insight custom field configurations that should have been deleted. Delete any row returned from the SQL.*/
    SELECT INCLUDE_CHILD_OBJECT_TYPES,SHOW_ONLY_PROJECT_OBJECTS,FORM_TYPE,EDIT_IQL,SHOW_ONLY_USER_OBJECTS,CUSTOM_FIELD_CONFIG_ID,ADD_ALL_OPTION,ORDER_BY_ATTRIBUTE_ASC,ATTRIBUTE_NAMES_TO_SHOW,ASSIGN_SCOPE_IQL,SHOW_OBJ_PICKER_SDPORTALS,AUTO_FILL_USER_OBJECTS,ORDER_BY_ATTRIBUTE,RESTRICT_USER_TYPE,SHOW_ON_SDCUSTOMER_DETAILS,CUSTOM_FIELD_ID,ID,IQL,ATTRIBUTE_NAMES_FOR_SEARCH,AUTOFILL_STRICT 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.







Last modified on Sep 9, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.