Orchestrator 2020.10.7 - PRIMARY KEY constraint during DB migration

Help Orchestrator
,
1

Dear Community, Dear Experts,

I’m a newbee here in the Forum and this is my first post so I hope I will stick to most of the rules I learned in the beginners guide but please be patient in case I missed some in my post :thinking:

We started today the upgrade of our on prem Orchestrator which is currently running on version 2019.10.4. I’m doing such an upgrade for the first time and therefore it went pretty well but after all our packages got migrated successfully on the DB some additonal installation steps started and after a while we received the following error message:

*System.Exception: Error:-1, Message:An error occurred while migrating orchestrator data. Violation of PRIMARY KEY constraint 'PK_AspNetUserLogins'. Cannot insert duplicate key in object 'identity.AspNetUserLogins'. The duplicate key value is (Windows.Scheme, S-1-5-21-632532318-2666670698-1580278117-660243, e4d1c12b-983a-41db-9320-c2d2cd98b7c1).*
*The statement has been terminated.*
*at UiPath.OrchestratorCAs.IdentityDatabaseManager.RunDbMigrationToolObfuscatedLogging(String arguments, String[] tokensToObfuscate)*
*at UiPath.OrchestratorCAs.IdentityDatabaseManager.MigrateUsersFromOrchestratorDb(String webConfigPath, String identityPublicUrl)*
*at UiPath.OrchestratorCAs.IdentityDatabaseManager.ApplyDbConfigMigrationsOnInstalling(String webConfigPath, String identityAppSettingsJsonPath)*
*at UiPath.OrchestratorCAs.CustomActions.Deferred.UpdateConfigurationAndDatabasesAction.ApplyIdentityDbConfigMigrations()*
*at UiPath.OrchestratorCAs.CustomActions.Deferred.UpdateConfigurationAndDatabasesAction.Execute()*
*at UiPath.OrchestratorCAs.CustomActions.Base.BaseCustomAction.InternalExecute()*

I tried to check the entries in the database but when I checked the whole table identity.AspNetUserLogins was empty. Additionally I think this is a new table which was not there in the 2019.10.4 version so I’m wondering why and how it can happen that there are duplicates in this table…

All steps were rolled back afterwards and now I can’t see these identidy tables any longer in our database.

Could you please help me to understand if there is something we did wrong or what can be done to identify the root cause of the issue? The whole upgrade process stopped now and currently the orchestrator is down.

Thank you very much in advance for your help.

Best regards,
Andreas

3

@Munich_Andy

I suggest raising this issue to Technical Support, they can help you out better

Hope this helps you

Thanks

1 Like
4

Hi Community,

I raised this problem with UiPath Technical Support and they pointed me to a KB article. Following the steps in the article we were able to solve our problem. Therefore please find below the steps wich needs to be done in case some others will face the same issue:

Article Number
000002670

Title
Violation of PRIMARY KEY constraint ‘PK_AspNetUserLogins’. Cannot insert duplicate key in object ‘identity.AspNetUserLogins’. The duplicate key value is (Windows.Scheme, S-1-5-21-705512089-1558976605-4064737748-16945, 52da69bb-f3cb-4d90-806d-64bbbe6321bf)

Question
An error occurred while migrating orchestrator data. Violation of PRIMARY KEY constraint ‘PK_AspNetUserLogins’. Cannot insert duplicate key in object ‘identity.AspNetUserLogins’. The duplicate key value is (Windows.Scheme, S-1-5-21-705512089-1558976605-4064737748-16945, 52da69bb-f3cb-4d90-806d-64bbbe6321bf)

Issue Description + Resolution
Please find the below steps for the Solution.

If Upgrade is failed with the above error, request to restore the Database since new identity schema is updated in the target database.

Below query will return the duplicate UserIDs and their count :

select UserId, count(UserId) from dbo.UserLogins
group by UserId, LoginProvider,ProviderKey,Tenantid
having count(UserId) > 1

Below query will give the ID corresponding to duplicate userIDs:

Select ID, userID from dbo.UserLogins_test where userID in 
(
select UserId, count(UserId) from dbo.UserLogins
group by UserId, LoginProvider,ProviderKey,Tenantid
having count(UserId) > 1
)

From above query, you need to keep just 1 record per userID and delete rest of the records. For example. if the result of above query is

ID userID
21 30
23 30
25 30

then run query

delete from dbo.UserLogins_test where id in (21,23)

to delete ID 21 and 23 and keep 25.

5

Unfortunately, we still face an issue with our upgrade and I sent again a ticket to UiPath Technical support. This time the installation stops for no visible reason and undoes all installation steps at the end. There is no pop-up and no error message. I spent some time to analyze the installation log and I think I found the activity which fails:

MSI (s) (B8!60) [14:40:56:430]: Closing MSIHANDLE (495) of type 790531 for thread 2912
MSI (s) (B8!60) [14:40:56:430]: Creating MSIHANDLE (496) of type 790531 for thread 2912
Error Number:2601,State:1,Class:14
MSI (s) (B8!60) [14:40:56:430]: Closing MSIHANDLE (496) of type 790531 for thread 2912
MSI (s) (B8!60) [14:40:58:039]: Creating MSIHANDLE (497) of type 790531 for thread 2912
Exit code -1 returned by command 
UiPath.Orchestrator.Setup.PackagesMigration.Console.exe finish --connection-string "Data Source=*******;Initial Catalog=UiPath;Integrated Security=True" --purge-dirty-definitions
MSI (s) (B8!60) [14:40:58:039]: Closing MSIHANDLE (497) of type 790531 for thread 2912
CustomAction Orchestrator_ImportMigratePackages returned actual error code 1603 (note this may not be 100% accurate if translation happened inside sandbox)
MSI (s) (B8:38) [14:40:58:118]: Closing MSIHANDLE (464) of type 790536 for thread 5464
Action ended 14:40:58: InstallFinalize. Return value 3.
MSI (s) (B8:58) [14:40:58:133]: Note: 1: 2265 2:  3: -2147287035 
MSI (s) (B8:58) [14:40:58:133]: User policy value 'DisableRollback' is 0
MSI (s) (B8:58) [14:40:58:133]: Machine policy value 'DisableRollback' is 0
MSI (s) (B8:58) [14:40:58:133]: Note: 1: 2318 2:  
MSI (s) (B8:58) [14:40:58:149]: Executing op: Header(Signature=1397708873,Version=500,Timestamp=1382511861,LangId=1033,Platform=0,ScriptType=2,ScriptMajorVersion=21,ScriptMinorVersion=4,ScriptAttributes=1)
MSI (s) (B8:58) [14:40:58:149]: Executing op: DialogInfo(Type=0,Argument=1033)
MSI (s) (B8:58) [14:40:58:164]: Executing op: DialogInfo(Type=1,Argument=UiPath Orchestrator)
MSI (s) (B8:58) [14:40:58:164]: Executing op: RollbackInfo(,RollbackAction=Rollback,RollbackDescription=Rolling back action:,RollbackTemplate=[1],CleanupAction=RollbackCleanup,CleanupDescription=Removing backup files,CleanupTemplate=File: [1])
Action 14:40:58: Rollback. Rolling back action:
Rollback: Installing new Activities

But unfortunately we can’t solve the problem without the support from UiPath Technical support. As soon as there is a solution for this problem I will update this post.

7
  1. Find the duplicate SIDs and Tenants

SELECT [TenantId]
,[ProviderKey]
,count()
FROM [dbo].[UserLogins]
GROUP BY [TenantId]
,[ProviderKey]
HAVING count() > 1

  1. Find the users mapped to each one of these pairs:

SELECT *
FROM [dbo].[Users]
WHERE id IN (
SELECT UserID
FROM [dbo].[UserLogins]
WHERE [TenantId] = ‘TENANTID’
AND [ProviderKey] = ‘PROVIDERKEY’
)

  1. Delete the USERLOGINS entry for all but one. That user will be the only one that can do Windows Auth on that tenant.

DELETE
FROM [dbo].[UserLogins]
WHERE userid = USERID
AND [TenantId] = ‘TENANTID’
AND [ProviderKey] = ‘PROVIDERKEY’

  1. Repeat for the rest of the pairs from query 1