Knowledge Base articles fail to open from the request form in Jira Service Management with the error "The Service Desk you are trying to view does not exist"

Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.

Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

     

Summary

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:


Another symptom is that REST API calls for the service desk portal ID return a different ID from Viewport ID observed in the browser.

Environment

  • Knowledge Base integration with Confluence (any version)
  • Jira Service Management versions 3.x up to 4.18.x
    • This behavior can still occur in fixed versions, though the root cause is corrected, because the mismatched Portal/Viewport IDs that cause the problem remain in the database after upgrade.  

Diagnosis

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 values are different.

Database check

You can also check the portalId mismatch in the database directly. This approach allows you to identify all 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 "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;

The above query only outputs projects with mismatched portal/viewport IDs:

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

Every resulting project is affected, because the Service Desk ID is different from the Viewport ID.

Cause

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 expects each project to have consistent Portal IDs and Viewport IDs across these tables. When that doesn't happen, many features still work properly but some don't — like the KB integration and REST API results.

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

This root cause is tracked on JSDSERVER-8385 - Jira Service Management knowledge base and REST API functionalities breaks if viewport and servicedesk sequences mismatch

Solution

Upgrade

To prevent this situation from occurring, upgrade your instance of Jira Service Management to any of the fixed versions listed in JSDSERVER-8385 - Jira Service Management knowledge base and REST API functionalities breaks if viewport and servicedesk sequences mismatch (4.13.5, 4.20.3, 4.21.1, 4.22.0) or any later version.

Upgrading to a fixed version will ensure that the sequences stay in sync, but will not correct existing instances of mismatched IDs/broken links.  To correct existing IDs, see Workaround below.

Workaround

Update the sequences so they match again and then update the AO_54307E_SERVICEDESK and its dependencies to match the "viewport side" of the scheme.

You could instead update AO_54307E_VIEWPORT and its dependencies to match the "servicedesk side," but that approach will cause the service desk Portals' URLs to change, as they're based off the viewport ID.  This method eliminates that impact to users.

Please note the following information is provided as-is. Atlassian Support cannot provide further assistance with the SQL queries described below.

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 "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;
  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"),
                         NEW_KB_HELPFUL_AGGR_ID
                             as (UPDATE "AO_0201F0_KB_HELPFUL_AGGR" SET "SERVICE_DESK_ID" = entry."Viewport_ID" WHERE "SERVICE_DESK_ID" = entry."Service_Desk_ID"),
                         NEW_KB_VIEW_AGGR_ID
                             AS (UPDATE "AO_0201F0_KB_VIEW_AGGR" SET "SERVICE_DESK_ID" = entry."Viewport_ID" WHERE "SERVICE_DESK_ID" = entry."Service_Desk_ID"),
                         NEW_CANNED_RESPONSE_ID
                             AS (UPDATE "AO_D530BB_CANNEDRESPONSE" SET "SERVICE_DESK_ID" = entry."Viewport_ID" WHERE "SERVICE_DESK_ID" = entry."Service_Desk_ID"),
                         NEW_CALENDAR_ID AS (UPDATE "AO_7A2604_CALENDAR"
                             SET "CONTEXT" =
                                     jsonb_set("CONTEXT"::jsonb, '{serviceDeskId}', to_jsonb(entry."Viewport_ID")::jsonb)
                             WHERE ("CONTEXT"::jsonb ->> 'serviceDeskId') = entry."Service_Desk_ID"::text)
                    UPDATE "AO_54307E_SERVICEDESK"
                    SET "ID" = entry."Viewport_ID"
                    WHERE "ID" = entry."Service_Desk_ID";
                END LOOP;
        END
    $$;
  6. Start Jira



Last modified on Jan 22, 2025

Was this helpful?

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