Knowledge Base articles fail to open from the request form in Jira Service Management

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。

    

要約

In a Confluence and Jira Service Management integration, knowledge base articles can be opened from the main portal search, but not from within the request form.

"The Service Desk you are trying to view does not exist" message is shown:

Other symptom is REST API calls don't bring the same service desk portal ID that's observed in when navigating through the Browser.


環境

  • Jira Service Management versions 3.x up to 4.18.x.
  • Knowledge Base integration with Confluence (any version).


診断

Browser check

In the browser, you may follow the steps below:

  1. Search the knowledge base article from the main portal search form
  2. Take note of the &portalId=  value in the URL
  3. Go back, select the affected request type and search for the same knowledge base article from the summary field in the form
  4. The error should show up. Take note of the &portalId= from the URL again
  5. Confirm the two portalId are different

Database check

You can also check the portalId mismatch on the database directly. This approach allows you to identify other affected projects:

SELECT vp."KEY" AS "Original Project Key", vp."PROJECT_ID" AS "Project ID", sd."ID" AS "Service Desk ID", vp."ID" AS "Viewort ID"
FROM "AO_54307E_VIEWPORT" vp
JOIN "AO_54307E_SERVICEDESK" sd ON sd."PROJECT_ID" = vp."PROJECT_ID"
WHERE vp."ID" <> sd."ID"
ORDER BY 2,3,4;

The output should be similar to this:

 Original Project Key | Project ID | Service Desk ID | Viewort ID 
----------------------+------------+-----------------+------------
 ssm                  |      10000 |               1 |          4
 hdesk                |      10200 |               2 |          5

Every project which the Service Desk ID is different from the Viewort ID is affected.


原因

Jira Service Management database scheme has two important domains: the "servicedesk" and the "viewport". Their main tables are, respectively, AO_54307E_SERVICEDESK and AO_54307E_VIEWPORT. Jira requires these tables to have the IDs to function properly. When that doesn't happen, many features still work properly but some don't — like the KB integration and REST API results.

The issue is that these two tables have different sequences, and they might fall out of sync. Possible causes include direct database manipulation, project restore or copy using 3rd party apps, etc.

This root cause is tracked on  JSDSERVER-8385 - Getting issue details... STATUS


ソリューション

Basically, we should update the sequences so they match again and then update the AO_54307E_SERVICEDESK and it's dependencies to match the "viewport side" of the scheme.

You can also update AO_54307E_VIEWPORT and it's dependencies to match the "servicedesk side". It'll also work, but the service desk Portals' URLs will change as they're based off the viewport id.

As with every database update, we advise to take database backups before applying the changes and validating them in a lower environment prior to updating production.

  1. Stop Jira (if Datacenter, stop all nodes )
  2. Select the greater seq ID from both sequences:

    Postgres syntax example
    SELECT vp.last_value AS "Viewport last ID", sd.last_value AS "Servicedesk last ID"
    FROM "AO_54307E_VIEWPORT_ID_seq" vp, "AO_54307E_SERVICEDESK_ID_seq" sd;
  3. Update both sequences with a value greater than both retrieved above (eg. replace new_seq by the higher ID + 10):

    Postgres syntax example
    SELECT setval('"AO_54307E_VIEWPORT_ID_seq"', new_seq, FALSE);
    SELECT setval('"AO_54307E_SERVICEDESK_ID_seq"', new_seq, FALSE);

    This will make new projects be in sync from the start.

  4. List the projects with mismatching IDs:

    Postgres syntax example
    SELECT vp."KEY" AS "Original Project Key", vp."PROJECT_ID" AS "Project ID", sd."ID" AS "Service Desk ID", vp."ID" AS "Viewort ID"
    FROM "AO_54307E_VIEWPORT" vp
    JOIN "AO_54307E_SERVICEDESK" sd ON sd."PROJECT_ID" = vp."PROJECT_ID"
    WHERE vp."ID" <> sd."ID"
    ORDER BY 2,3,4;
  5. Update the AO_54307E_SERVICEDESK ID and it's dependencies to match the Project's "Viewport ID" for each project above (step 4's output).

    Postgres syntax example
    DO $$
    DECLARE
    entry record;
    BEGIN
      FOR entry IN SELECT vp."KEY" AS "Original_Project_Key", vp."PROJECT_ID" AS "Project_ID", sd."ID" AS "Service_Desk_ID", vp."ID" AS "Viewport_ID"
        FROM "AO_54307E_VIEWPORT" vp
        JOIN "AO_54307E_SERVICEDESK" sd ON sd."PROJECT_ID" = vp."PROJECT_ID"
        WHERE vp."ID" <> sd."ID"
        ORDER BY 2,3,4
      LOOP
        WITH NEW_CAPABILITY_ID AS (UPDATE "AO_54307E_CAPABILITY" SET "SERVICE_DESK_ID" = entry."Viewport_ID" WHERE "SERVICE_DESK_ID" = entry."Service_Desk_ID"),
        NEW_CONFLUENCEKB_ID AS (UPDATE "AO_54307E_CONFLUENCEKB" SET "SERVICE_DESK_ID" = entry."Viewport_ID" WHERE "SERVICE_DESK_ID" = entry."Service_Desk_ID"),
        NEW_CONFLUENCEKBENABLED_ID AS (UPDATE "AO_54307E_CONFLUENCEKBENABLED" SET "SERVICE_DESK_ID" = entry."Viewport_ID" WHERE "SERVICE_DESK_ID" = entry."Service_Desk_ID"),
        NEW_CONFLUENCEKBLABELS_ID AS (UPDATE "AO_54307E_CONFLUENCEKBLABELS" SET "SERVICE_DESK_ID" = entry."Viewport_ID" WHERE "SERVICE_DESK_ID" = entry."Service_Desk_ID"),
        NEW_EMAILCHANNELSETTING_ID AS (UPDATE "AO_54307E_EMAILCHANNELSETTING" SET "SERVICE_DESK_ID" = entry."Viewport_ID" WHERE "SERVICE_DESK_ID" = entry."Service_Desk_ID"),
        NEW_EMAILSETTINGS_ID AS (UPDATE "AO_54307E_EMAILSETTINGS" SET "SERVICE_DESK_ID" = entry."Viewport_ID" WHERE "SERVICE_DESK_ID" = entry."Service_Desk_ID"),
        NEW_OUT_EMAIL_SETTINGS_ID AS (UPDATE "AO_54307E_OUT_EMAIL_SETTINGS" SET "SERVICE_DESK_ID" = entry."Viewport_ID" WHERE "SERVICE_DESK_ID" = entry."Service_Desk_ID"),
        NEW_PARTICIPANTSETTINGS_ID AS (UPDATE "AO_54307E_PARTICIPANTSETTINGS" SET "SERVICE_DESK_ID" = entry."Viewport_ID" WHERE "SERVICE_DESK_ID" = entry."Service_Desk_ID"),
        NEW_REPORT_ID AS (UPDATE "AO_54307E_REPORT" SET "SERVICE_DESK_ID" = entry."Viewport_ID" WHERE "SERVICE_DESK_ID" = entry."Service_Desk_ID"),
        NEW_TIMEMETRIC_ID AS (UPDATE "AO_54307E_TIMEMETRIC" SET "SERVICE_DESK_ID" = entry."Viewport_ID" WHERE "SERVICE_DESK_ID" = entry."Service_Desk_ID")
        UPDATE "AO_54307E_SERVICEDESK" SET "ID" = entry."Viewport_ID" WHERE "ID" = entry."Service_Desk_ID";
      END LOOP;
    END$$;
  6. Jira の起動



最終更新日 2021 年 11 月 12 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.