U4-10244 - Umbraco 7.7-beta - Migration fails when upgrading users

Created by Mikkel Holck Madsen 03 Aug 2017, 07:42:26 Updated by Mikkel Holck Madsen 10 Aug 2017, 08:43:49

Subtask of: U4-8632

When upgrading an existing site, that contains a number of users, the migration will fail, if two users has the same username. The userName isnt unique, its the userLogin that is. This means that it will try to insert dublicates into the new umbracoUserGroup table.

i.e. we have two users called Peter Nielsen(userName), who is created with two different emails(userLogin).

This is the exception shown in the log(sorry for the danish exceptions):

 2017-08-03 08:50:29,131 [P79936/D4/T40] ERROR Umbraco.Web.Install.Controllers.InstallApiController - Installation step DatabaseUpgrade failed.
System.Reflection.TargetInvocationException: Destinationen for en aktivering udløste en undtagelse. ---> Umbraco.Web.Install.InstallException: The database failed to upgrade. ERROR: The database configuration failed with the following message: A duplicate value cannot be inserted into a unique index. [ Table name = umbracoUserGroup,Constraint name = IX_umbracoUserGroup_userGroupAlias ]
 Please check log file for additional information (can be found in '/App_Data/Logs/UmbracoTraceLog.txt')
   ved Umbraco.Web.Install.InstallSteps.DatabaseUpgradeStep.Execute(Object model)

And it seems to come from the SQL in Umbraco.Core.Persistence.Migrations.Upgrades.TargetVersionSevenSevenZero.AddUserGroupTables.MigrateUserPermissions

            // Create user group records for all non-admin users that have specific permissions set
            Execute.Sql(@"INSERT INTO umbracoUserGroup(userGroupAlias, userGroupName)
                SELECT userName + 'Group', 'Group for ' + userName
                FROM umbracoUser
                WHERE (id IN (
	                SELECT " + SqlSyntax.GetQuotedColumnName("user") + @"
	                FROM umbracoUser2app
                ) OR id IN (
	                SELECT userid
	                FROM umbracoUser2NodePermission
                AND id > 0");

Where the Select will return dublicates if there are two of the same usernames

1 Attachments


Mikkel Holck Madsen 07 Aug 2017, 07:21:58

Yeah exactly, it was the umbraco.com site i did it on. but really any site could do, if you can create two users with the same name, and different emails

Shannon Deminick 08 Aug 2017, 01:06:23

PR: https://github.com/umbraco/Umbraco-CMS/pull/2109

This also fixes the requirement to create a custom user group for ever user. Now a custom user group will only be created per-user if the user has granular content permissions assigned. For section access, we will now create "Migrated Section Access Group"s for each unique section access combination and assign users appropriately. For example, see screenshot - these 2 groups are created for the .com upgrade instead of a group per user.

To test:

  • have a 7.6 site
  • create 2 users with the same section access and the same name but different emails
  • create 2 more users with the same section access but different from the previous 2
  • do the upgrade
  • ensure everything works as expected, no errors during upgrade and section access for users is maintained by these custom groups created

Mikkel Holck Madsen 10 Aug 2017, 08:43:13

Much better, this is awesome.

Took the umbraco website through the upgrader and it worked like a charm, only two groups were added, and the ones with the same names were migrated just fine!

Merging this!

Priority: Normal

Type: Bug

State: Fixed


Difficulty: Normal


Backwards Compatible: True

Fix Submitted:

Affected versions: 7.7.0

Due in version: 7.7.0

Sprint: Sprint 65

Story Points: 1