Summary

Assets performs detailed analysis of the failed Assets objects and attempt to explain exactly why the object failed to be indexed.  The following message is is displayed in Jira > Manage Apps > Indexing Assets section.

Environment

• JSM Data Center 10.1 and any higher version

Diagnosis

• In Jira > Manage Apps > Indexing Assets there is a warning message "Unable to find attributes associated to the object - object may need to be deleted"

• In the Atlassian log file, the following stack trace is present:

  atlassian-jira.log

  2024-08-28 14:12:06,846 insight-InsightThreadGroup-worker-thread-12 WARN admin     [i.r.i.i.m.ObjectIndexImpl] Could not load all objects in cache - loading one by one
  com.google.common.cache.CacheLoader$InvalidCacheLoadException: loadAll failed to return a value for 1
  	at com.google.common.cache.LocalCache.getAll(LocalCache.java:4091) ~[guava-33.2.1-jre.jar:?]
  	at com.google.common.cache.LocalCache$LocalLoadingCache.getAll(LocalCache.java:5004) ~[guava-33.2.1-jre.jar:?]
  	at io.riada.insight.index.model.InsightIndexBase.loadAll(InsightIndexBase.java:83) ~[6829635c-2b0e-43be-b422-d11715df9423_213.0:4/:?]
  	at io.riada.insight.index.model.ObjectIndexImpl.reindexObjects(ObjectIndexImpl.java:396) ~[6829635c-2b0e-43be-b422-d11715df9423_213.0:4/:?]
  	at com.riadalabs.jira.plugins.insight.services.core.index.ReindexServiceImpl$ReindexObjectsJob.executeTask(ReindexServiceImpl.java:779) ~[6829635c-2b0e-43be-b422-d11715df9423_213.0:26/:?]
  	at com.riadalabs.jira.plugins.insight.services.core.index.ReindexServiceImpl$ReindexObjectsJob.executeTask(ReindexServiceImpl.java:751) ~[6829635c-2b0e-43be-b422-d11715df9423_213.0:26/:?]
  	at com.riadalabs.jira.plugins.insight.services.core.multithreadservice.InsightServiceJob.call(InsightServiceJob.java:41) ~[6829635c-2b0e-43be-b422-d11715df9423_213.0:26/:?]
  	at com.atlassian.jira.util.thread.JiraThreadLocalUtils.lambda$wrap$2(JiraThreadLocalUtils.java:194) ~[classes/:?]
  	at com.atlassian.sal.core.executor.ThreadLocalDelegateCallable.call(ThreadLocalDelegateCallable.java:38) [jira-sal-plugin-10.1.0-QR-20240822132126_1724297090000.jar:?]
  	at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
  	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
  	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
  	at java.lang.Thread.run(Thread.java:840) [?:?]
  2024-08-28 14:12:07,214 insight-InsightThreadGroup-worker-thread-12 ERROR admin     [i.r.i.i.m.ObjectIndexImpl] Failed to reindex objects with IDs: [1, 86, 87, 88, 89, 90, 91] 
  2024-08-28 14:12:07,245 insight-InsightThreadGroup-worker-thread-10 ERROR admin     [c.r.j.p.i.s.c.d.i.ObjectDalImpl] Cannot index Object ITSM-1 - no attributes associated with object

• To diagnose this problem, we might look for the above-mentioned errors in the Jira logs and run the below query on the Jira database. The provided queries were designed for MySQL and PostgreSQL, so they might need adjustments to function properly on a different database system.

• A row should be returned and if you have null values for all the right tables indicates that object got corrupted and needs to be deleted.

  For Postgres:

  SELECT
    OAV."TEXT_VALUE",
    O."NAME",
    O."OBJECT_TYPE_ID",
    OTA."NAME",
    OA."ID",
    OA."OBJECT_TYPE_ATTRIBUTE_ID",
    OA."OBJECT_ID",
    OAV."ID",
    OAV."OBJECT_ATTRIBUTE_ID" FROM "AO_8542F1_IFJ_OBJ" O
    LEFT OUTER JOIN "AO_8542F1_IFJ_OBJ_ATTR" OA ON O."ID" = OA."OBJECT_ID"
    LEFT OUTER JOIN "AO_8542F1_IFJ_OBJ_TYPE_ATTR" OTA ON OTA."ID" = OA."OBJECT_TYPE_ATTRIBUTE_ID"
    LEFT OUTER JOIN "AO_8542F1_IFJ_OBJ_ATTR_VAL" OAV ON OA."ID" = OAV."OBJECT_ATTRIBUTE_ID"
  WHERE O."ID" = ?;

  For MySQL:

  SELECT
    OAV.TEXT_VALUE,
    O.NAME,
    O.OBJECT_TYPE_ID,
    OTA.NAME,
    OA.ID,
    OA.OBJECT_TYPE_ATTRIBUTE_ID,
    OA.OBJECT_ID,
    OAV.ID,
    OAV.OBJECT_ATTRIBUTE_ID
  FROM AO_8542F1_IFJ_OBJ O
  LEFT OUTER JOIN AO_8542F1_IFJ_OBJ_ATTR OA ON O.ID = OA.OBJECT_ID
  LEFT OUTER JOIN AO_8542F1_IFJ_OBJ_TYPE_ATTR OTA ON OTA.ID = OA.OBJECT_TYPE_ATTRIBUTE_ID
  LEFT OUTER JOIN AO_8542F1_IFJ_OBJ_ATTR_VAL OAV ON OA.ID = OAV.OBJECT_ATTRIBUTE_ID
  WHERE O.ID = ?;

  ? is the object ID found after the message "Cannot index Object X - no attributes associated with object" and needs to be replaced by the value you found. You may have more than one exception, so you will need to perform the same steps for each of the values.

• An example of a corrupted object where null values are displayed for all the right tables as follows. The below object needs to be deleted.

   TEXT_VALUE |    NAME     | OBJECT_TYPE_ID | NAME | ID | OBJECT_TYPE_ATTRIBUTE_ID | OBJECT_ID | ID | OBJECT_ATTRIBUTE_ID 
  ------------+-------------+----------------+------+----+--------------------------+-----------+----+------------
  
        null  | webex        |  97 |  null    | null   |           null               |     null      |  null  |            
  (1 row)

• Before proceeding to delete the corrupted objects, it is essential to also verify the Object Type to determine if there are any issues at the Object Type level. Execute the provided query, which should fetch at least 4 attributes (Key, Created, Updated, and the current Label), along with any other configured additional attributes.

  For Postgres:

  SELECT * FROM "AO_8542F1_IFJ_OBJ_TYPE_ATTR" WHERE "OBJECT_TYPE_ID" = ? AND ("NAME" IN ('Key', 'Created', 'Updated') OR "LABEL"='t');

  ? needs to be replaced by the Object Type ID from the the previous query output. In this example, it would be 97

  • If any of the 4 attributes (Key, Created, Updated, and the current Label) are missing, there is an issue with the Object Type and needs to be removed as well.

Cause

When attempting to delete an object or object type, failures may occur due to race conditions or interruptions in the deletion process. In such cases, certain data linked to the object type within the database must be removed first to address constraints. The underlying reason for this issue remains unidentified.

Solutions

Objects identified in the atlassian-jira logs or in the Assets UI need to be deleted. Below are some approaches described:

Solution 1 - Only Asset Objects are corrupted

Use this solution if only Asset Objects are corrupted and the Object type was is correct stage.

1. Go to Assets > Search for objects
2. Use AQL:  objectId IN()

3. In the example displayed in this article, the item identified was ITSM-1, which is visible on both the logs and user interface. The AQL would be as follows.  If there are more than one object, use commas to separate them.

   Example

    objectId IN(1)

4. If any results are available, please delete the objects manually.

   1. If no objects are found using the AQL, proceed and use Solution #2.

5. Once all the objects from the search are removed. go to Jira administration (gear icon) > Manage Apps > Indexing Assets.

6. Run a Clean Assets Reindex

Solution 2 - Only Asset Objects are corrupted

Only utilize this solution if the AQL search fails to retrieve the object IDs specified in the logs or in Assets UI. In such cases, the objects will need to be manually deleted from the database. 

1. Stop Jira

2. Execute the SQL queries below in Jira database. Due to the table constraints, it's recommended to run the SELECT and DELETE queries following the order below. The provided queries were designed for MySQL and PostgreSQL, so they might need adjustments to function properly on a different database system.
   
   

   Queries for PostgreSQL

   For PostgreSQL

   /*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 (?);
   
   /*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 (?);
   
   /*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 (?);
   
   /*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 (?);
   
   /*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 (?);
   
   /*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 * 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 * from "AO_8542F1_IFJ_OBJ_ATTR" where "OBJECT_ID"  in (?);
   
   /*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 "ID" in (?);

   ? is the object ID found after the message "Cannot index Object X - no attributes associated with object" and needs to be replaced by the value you found. You may have more than one exception, so you will need to perform the same steps for each of the values.

   Queries for MySQL

   For MySQL

   /*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 (?);
   
   /*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 (?);
   
   /*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 (?);
   
   /*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 (?);
   
   /*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 (?);
   
   /*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 * 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 * from AO_8542F1_IFJ_OBJ_ATTR where OBJECT_ID  in (?);
   
   /*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 ID in (?);

   ? is the object ID found after the message "Cannot index Object X - no attributes associated with object" and needs to be replaced by the value you found. You may have more than one exception, so you will need to perform the same steps for each of the values.

3. Start Jira
4. On the Jira UI, go to Jira administration (gear icon) > Manage Apps > Indexing Assets.
5. Run a Clean Assets Reindex

Solution 3 - Both Asset Objects and Object Types are Corrupted

Use this solution when the object type is also corrupted as identified during the Diagnosis steps.

1. Stop Jira

2. Start with Solution #1 to delete the affected objects.

3. Once objects are deleted, the Object Type can be deleted as well.

4. Execute the SQL queries below in Jira database.  Due to the table constraints, it's recommended to run the SELECT and DELETE queries following the order below. The provided queries were designed for MySQL and PostgreSQL, so they might need adjustments to function properly on a different database system.
   

   Queries for PostgreSQL

   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>);
   
   /*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>);

   Replace <object_type_id> with the Object Type ID identified during the Diagnosis steps. In the example, Object Type ID was 97

   Queries for MySQL

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

   Replace <object_type_id> with the Object Type ID identified during the Diagnosis steps. In the example, Object Type ID was 97

5. Start Jira
6. After deleting all rows returned from each SQL, then we can delete the Object Type thought the Assets UI while browsing the object type e.g. http://<Jira_base_URL/secure/ObjectSchema.jspa?id=xx&typeId=xx

   1. Two parameters are required: Id (Schema ID) and typeId (Object Type ID). Here is how you can obtain the Schema ID. Execute the following query:

      Queries for PostgreSQL

      Queries for PostgreSQL DB

      SELECT t."OBJECT_SCHEMA_ID",s."NAME" as "SCHEMA NAME" FROM  "AO_8542F1_IFJ_OBJ_TYPE" t
      inner join "AO_8542F1_IFJ_OBJ_SCHEMA" s on s."ID" = t."OBJECT_SCHEMA_ID"
      WHERE  t."ID" = <object_type_id>

      Queries for MySQL

      Queries for MySQL DB

      SELECT t.OBJECT_SCHEMA_ID, s.NAME AS SCHEMA_NAME
      FROM AO_8542F1_IFJ_OBJ_TYPE t
      INNER JOIN AO_8542F1_IFJ_OBJ_SCHEMA s ON s.ID = t.OBJECT_SCHEMA_ID
      WHERE t.ID = <object_type_id>;

   2. After obtaining the Schema ID, navigate to https://<Jira_base_URL/secure/ObjectSchema.jspa?id=<SchemaID>&typeId=<object_type_id>. Remember to substitute <SchemaID> and <object_type_id> accordingly.

   3. Now you can try to delete the object type from the UI. The object type should be deleted successfully.
7. On the Jira UI, go to Jira administration (gear icon) > Manage Apps > Indexing Assets.
8. Run a Clean Assets Reindex