U4-9198 - Cannot save user with Id > 1 when running on Sql Server 2008 R2 or older

Created by Asbjørn Riis-Knudsen 17 Nov 2016, 10:18:30 Updated by Sebastiaan Janssen 17 Nov 2016, 10:24:33

Duplicates: U4-9195

When saving a user on Umbraco 7.5, I get an error. From the log, the error is this: System.Exception: A user with the login [loginName] already exists at umbraco.BusinessLogic.User.set_LoginName(String value) at umbraco.cms.presentation.user.EditUser.SaveUser_Click(Object sender, EventArgs e)

Now, obviously the user exists, since I am trying to save my changes. So, I dug deeper.

It turns out that issue comes from this paging query, which executed to find out if more than one user exists with that login: SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ([umbracoUser].[userLogin])) peta_rn, umbracoUser.Id FROM [umbracoUser] WHERE (upper([umbracoUser].[userLogin]) = upper(@0)) ) peta_paged WHERE peta_rn>@1 AND peta_rn<=@2

Note that this returns ''two'' columns, {{peta_rn}} and {{Id}}

That would all be fine, except for the fact that the code that's doing the querying only expects one column to be returned, asking for a single {{int}}: public IEnumerable GetPagedResultsByQuery(IQuery query, int pageIndex, int pageSize, out int totalRecords, Expression<Func<IUser, string>> orderBy) { if (orderBy == null) throw new ArgumentNullException("orderBy");

        // get the referenced column name and find the corresp mapped column name
        var expressionMember = ExpressionHelper.GetMemberInfo(orderBy);
        var mapper = MappingResolver.Current.ResolveMapperByType(typeof(IUser));
        var mappedField = mapper.Map(expressionMember.Name);

        if (mappedField.IsNullOrWhiteSpace())
            throw new ArgumentException("Could not find a mapping for the column specified in the orderBy clause");

        var sql = new Sql()
            .Select("umbracoUser.Id")
            .From<UserDto>(SqlSyntax);

        var idsQuery = query == null ? sql : new SqlTranslator<IUser>(sql, query).Translate();

        // need to ensure the order by is in brackets, see: https://github.com/toptensoftware/PetaPoco/issues/177
        idsQuery.OrderBy("(" + mappedField + ")");
        var page = Database.Page<int>(pageIndex + 1, pageSize, idsQuery);
        totalRecords = Convert.ToInt32(page.TotalItems);

        if (totalRecords == 0)
            return Enumerable.Empty<IUser>();

        // now get the actual users and ensure they are ordered properly (same clause)
        var ids = page.Items.ToArray();
        return ids.Length == 0 ? Enumerable.Empty<IUser>() : GetAll(ids).OrderBy(orderBy.Compile());
    }

(UserRepository.cs, line 321)

Specifically, this line asks for a single int, resulting in the row number being returned rather than the Id, as expected: var page = Database.Page(pageIndex + 1, pageSize, idsQuery);

So, when only one user exists, this code always gets row number one rather than the user Id. If the user has Id one, everything works. But if not, you cannot save the user.

This does not occur in SQL Server 2012+, as Umbraco uses different paging queries there (http://issues.umbraco.org/issue/U4-8837)

This issue does also not occur on 7.4. I have not tested older 7.5.x release as of now.

Comments

Priority: Major

Type: Bug

State: Duplicate

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions: 7.5.4

Due in version:

Sprint:

Story Points:

Cycle: