"Exporting entity Portal Settings <ID> failed" on service project migration
Platform Notice: Cloud and Data Center - This article applies equally to both cloud and data center platforms.
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
Learn how to resolve the "Exporting entity Portal Settings <ID> failed com.querydsl.core.NonUniqueResultException: Only one result is allowed for fetchOne calls" error during your project migration.
Diagnosis
The Jira Cloud Migration Assistant supports migrating JSM (Service Management/former Service Desk) projects. JSM-specific related errors might surface during a JCMA plan execution.
This error is one of the potential ones an admin may face while migrating data from a Jira Server/DC instance along with JSM Projects over to an Atlassian Jira Cloud site.
The error message below will surface when there is a duplicate entry related to a Portal ID on a specific JSM table "AO_54307E_PARTICIPANTSETTINGS".
1
2
3
4
5
6
ERROR ... /rest/migration/latest/check/1q2w3e4r [c.a.j.m.export.framework.DefaultExportFailureHandler] 1q2w3e4r: Exporting entity Portal Settings 3 failed
com.querydsl.core.NonUniqueResultException: Only one result is allowed for fetchOne calls
at com.querydsl.core.support.FetchableQueryBase.uniqueResult(FetchableQueryBase.java:64)
at com.querydsl.sql.ProjectableSQLQuery.fetchOne(ProjectableSQLQuery.java:398)
at com.atlassian.jira.migration.export.db.servicedesk.portalsettings.ParticipantSettingReader.getParticipantSettings$lambda$1(ParticipantSettingReader.kt:30)
...The JCMA will fail during the Data preparation pre-flight check:
Context and cause
Reference: Managing access to your service project (Choose who customers can share requests with section).
The action in the UI that generates entries in this table can be found in Project Settings → Customer Permissions → Choose who customers can share requests with.
The duplicate entries will surface in the combined columns "AUTOCOMPLETE_ENABLED", "MANAGE_ENABLED", and "SERVICE_DESK_ID".
It's unknown which circumstances would cause duplicated entries to be added to that table.
Detection
This SQL query will bring all entries for a given Portal ID with duplicated entries on the "AO_54307E_PARTICIPANTSETTINGS" table.
The query will list only the duplicate IDs, which have a higher value than the original ID for the Portal.
That'll be the ID that we'll use to delete that offending entry.
Use the Portal ID from the error message in the query below. In the example error above, you'll see the Portal ID = 3.
Check which line you need to edit to include the Portal ID in the query below.
PostgreSQL
Edit line 8 to include the <Portal ID>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH duplicate_records AS (
SELECT "AUTOCOMPLETE_ENABLED"
, "MANAGE_ENABLED"
, "SERVICE_DESK_ID"
, "ID"
, ROW_NUMBER() OVER ( PARTITION BY "AUTOCOMPLETE_ENABLED", "MANAGE_ENABLED", "SERVICE_DESK_ID" ORDER BY "ID" ) AS row_number
FROM "AO_54307E_PARTICIPANTSETTINGS"
WHERE "SERVICE_DESK_ID" = <Portal ID>
)
SELECT "AUTOCOMPLETE_ENABLED"
, "MANAGE_ENABLED"
, "SERVICE_DESK_ID"
, "ID"
FROM duplicate_records
WHERE row_number > 1;MySQL
Edit line 12 to include the <Portal ID>
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT a.AUTOCOMPLETE_ENABLED
, a.MANAGE_ENABLED
, a.SERVICE_DESK_ID
, MAX(a.ID)
FROM AO_54307E_PARTICIPANTSETTINGS a
INNER JOIN ( SELECT AUTOCOMPLETE_ENABLED
, MANAGE_ENABLED
, SERVICE_DESK_ID
FROM AO_54307E_PARTICIPANTSETTINGS
GROUP BY AUTOCOMPLETE_ENABLED, MANAGE_ENABLED, SERVICE_DESK_ID
HAVING COUNT(*) > 1 ) b ON (a.AUTOCOMPLETE_ENABLED = b.AUTOCOMPLETE_ENABLED AND a.MANAGE_ENABLED = b.MANAGE_ENABLED AND a.SERVICE_DESK_ID = b.SERVICE_DESK_ID)
WHERE a.SERVICE_DESK_ID = <Portal ID>
ORDER BY a.ID DESC;Oracle
Edit line 8 to include the <Portal ID>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH duplicate_records AS (
SELECT AUTOCOMPLETE_ENABLED
, MANAGE_ENABLED
, SERVICE_DESK_ID
, ID
, ROW_NUMBER() OVER ( PARTITION BY AUTOCOMPLETE_ENABLED, MANAGE_ENABLED, SERVICE_DESK_ID ORDER BY ID ) AS row_number
FROM AO_54307E_PARTICIPANTSETTINGS
WHERE SERVICE_DESK_ID = <Portal ID>
)
SELECT AUTOCOMPLETE_ENABLED
, MANAGE_ENABLED
, SERVICE_DESK_ID
, ID
FROM duplicate_records
WHERE row_number > 1;MSSQL Server
Edit line 8 to include the <Portal ID>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH duplicate_records AS (
SELECT AUTOCOMPLETE_ENABLED
, MANAGE_ENABLED
, SERVICE_DESK_ID
, ID
, ROW_NUMBER() OVER ( PARTITION BY AUTOCOMPLETE_ENABLED, MANAGE_ENABLED, SERVICE_DESK_ID ORDER BY ID ) AS row_number
FROM AO_54307E_PARTICIPANTSETTINGS
WHERE SERVICE_DESK_ID = <Portal ID>
)
SELECT AUTOCOMPLETE_ENABLED
, MANAGE_ENABLED
, SERVICE_DESK_ID
, ID
FROM duplicate_records
WHERE row_number > 1;Example of an affected environment (from the database perspective):
In this example, Portal ID 3 (SERVICE_DESK_ID = 3) is returned in the query. It means there is more than one entry for the combination of AUTOCOMPLETE_ENABLED = 1, MANAGE_ENABLED = 1, and SERVICE_DESK_ID = 3.
The ID on the last column to the right is the highest ID for that duplicated combination of columns on the table AO_54307E_PARTICIPANTSETTINGS.
In this example, the ID of the offending (duplicated) entry is = 2. That's the ID we'll delete.
Solution
Delete the offending entry:
PostgreSQL
1
DELETE FROM "AO_54307E_PARTICIPANTSETTINGS" WHERE "ID" = <ID column value returned in the previous query>;Oracle, MySQL and MSSQL Server
1
DELETE FROM AO_54307E_PARTICIPANTSETTINGS WHERE ID = <ID column value returned in the previous query>;New JCMA plan
Once you've fixed the affected Portal, you should be good to create a new migration plan in JCMA.
Was this helpful?