Upgrade failed: "invalid data detected in the scopes applied to user roles on one or more teams"

[rosslovas]

Severity

Currently 1 known affected customer

Version

2024.2

Latest Version

I could reproduce the problem in the latest build

What happened?

Upgrading an Octopus Server instance failed when upgrading the database. The failure occurred in Script0432ValidateScopedUserRoleProjectGroups. This script exists to validate that the the relationships between scoped user roles and project groups contain no invalid data as we're continuing to add stronger validation of the integrity of the database contents, and invalid data of this sort requires manual intervention to address.

An example of the error is shown below under Error and Stacktrace.

Reproduction

Currently the only known way to reproduce is to manually insert a row into the ScopedUserRoleProjectGroup database table with an empty string in the ProjectGroupId column.

Error and Stacktrace

Unable to move forward with database upgrade due to invalid data detected in the scopes applied to user roles on one or more teams.
You can clean up the data manually, after which the upgrade should succeed.
See https://github.com/OctopusDeploy/Issues/issues/8700 for further information.
Summary of the invalid data detected:
 - Team named "System-scoped Team" in the system space contains user role named "Custom User Role" scoped to one or more invalid project groups
 - Team named "Team 1" in space named "Default" contains user role named "Custom User Role" scoped to one or more invalid project groups
 - Team named "Team 2" in space named "Space 2" contains user role named "Space manager" scoped to one or more invalid project groups

Alternatively, the error may present in a different form like so. If you encounter it in this form and your Octopus Server instance is malfunctioning, please reach out to Octopus Support so that we can assist, as the workaround below will not work:

Octopus.Client.Exceptions.OctopusValidationException: There was a problem with your request.

 - value (Parameter 'Tiny types should never be empty or whitespace. If the value you want to communicate is null/empty/whitespace then just pass a null rather than a tiny type wrapping it.')

Workaround

The upgrade failed and no changes were committed, so you can return to using the version of Octopus Server you were using prior to upgrading. Start the server, then manually navigate to Configuration -> Teams in the appropriate space(s), and find the team(s) exhibiting the problem indicated in the error message.

Switch to the User Roles tab, and locate the user role(s) exhibiting the problem as indicated in the error message. Click the 3 dots on the right of said user role(s) to expand the menu and click Edit.

You should see one or more items highlighted in red with the text Missing Resource.

Click the ❌ next to each to delete them, then click Apply to close the dialog box, then click Save to save the changes to the team. Note that changes here can affect the permissions applied to users in the team, so tread carefully, especially when deleting the last item of a type (e.g. project group) — this implicitly changes the scope to include all items of this type.

Once all affected teams have had their Missing Resource items removed, re-attempt the upgrade. If there are no erroneous items remaining then the check for invalid data should succeed this time, and the upgrade should complete successfully.

[rosslovas]

The following SQL query can be used to determine ahead of time whether this upgrade will fail, and help identify the scoped user roles that need addressing:

SELECT DISTINCT
    [Space].[Name] as 'Space Name',
    [Team].[Name] as 'Team Name',
    [UserRole].[Name] as 'User Role Name',
    [ScopedUserRole].[Id] as 'ScopedUserRole Id'
FROM [dbo].[ScopedUserRole]
LEFT OUTER JOIN [dbo].[Team] on [ScopedUserRole].[TeamId] = [Team].[Id]
LEFT OUTER JOIN [dbo].[UserRole] on [ScopedUserRole].[UserRoleId] = [UserRole].[Id]
LEFT OUTER JOIN [dbo].[Space] on [Team].[SpaceId] = [Space].[Id]
CROSS APPLY OPENJSON([ScopedUserRole].[JSON], '$.ProjectGroupIds')
    WITH ([ProjectGroupId] nvarchar(max) '$') AS [ProjectGroupIds]
WHERE NULLIF(LTRIM([ProjectGroupIds].[ProjectGroupId]), '') IS NULL