U4-10492 - Users failing to list with MySQL

Created by David Mill 03 Oct 2017, 14:59:21 Updated by Michele Di Maria 14 Nov 2017, 14:16:21

Tags: Unscheduled

Is duplicated by: U4-10652

Subtask of: U4-9609

I installed a clean version of Umbraco 7.7.2 and I never really thought of going into the Users section until I had already done a fair chunk of the work I intended too.

The database I'm using is MySQL version 5.7.19-log

'''Edit: I should add, you can create/edit users just fine, it's just listing them that seems to be the issue.''''

Once I went into the Users section, I get the error;

Server error: Contact administrator, see log for full details. Failed to retrieve users paged result

I have attached the error from the log file below

2017-10-03 15:47:57,261 [P15720/D4/T19] ERROR Umbraco.Web.Editors.UsersController - Unhandled controller exception occurred Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: The best overloaded method match for 'System.Collections.Generic.Dictionary<Umbraco.Core.Models.Membership.UserState,int>.Add(Umbraco.Core.Models.Membership.UserState, int)' has some invalid arguments at CallSite.Target(Closure , CallSite , Dictionary2 , UserState , Object ) at Umbraco.Core.Persistence.Repositories.UserRepository.GetUserStates() at Umbraco.Core.Services.UserService.GetUserStates() at Umbraco.Web.Editors.UsersController.GetPagedUsers(Int32 pageNumber, Int32 pageSize, String orderBy, Direction orderDirection, String[] userGroups, UserState[] userStates, String filter) at lambda_method(Closure , Object , Object[] ) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary2 arguments, CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown ---

1 Attachments


Sebastiaan Janssen 03 Oct 2017, 16:14:30

I've tested this and can confirm it is a problem currently.

FYI: MySQL support is unfortunately very low priority on our list so it will likely take a while for this to be fixed.

Michele Di Maria 16 Oct 2017, 07:40:00

Seems the MySQL DataLayer has not being tested at all before the release of this version... :(

Michele Di Maria 30 Oct 2017, 07:54:38

'''Solved.''' Seems MySQL returns a LONG in stead of an INT, and this causes the error.

In Umbraco.Core\Persistence\Repositories\UserRepository.cs from line 194, just add (int) before each object definition to resolve. That block becomes:

{UserState.All, (int)result[0].num}, {UserState.Active, (int)result[1].num}, {UserState.Disabled, (int)result[2].num}, {UserState.LockedOut, (int)result[3].num}, {UserState.Invited, (int)result[4].num}

Can someone please patch the official repository before the next release?

Thanks, Michele

Jimmy 14 Nov 2017, 08:48:32

@sebastiaan any chance this will be fixed in the next version?

We really need the fix, we use MySQL on many of our Umbraco sites :-)

Sebastiaan Janssen 14 Nov 2017, 09:50:34

Sorry, we haven't had time to fix this yet, the fix above looks okay, it would be great if it was submitted as a pull request and if it could be tested to still work on SQL Server as well.

Sebastiaan Janssen 14 Nov 2017, 10:39:18

You're in luck, I was testing anyway, this works great indeed. :-)

Should be fixed with this: https://github.com/umbraco/Umbraco-CMS/commit/d9ed420ed4a0ed30bcd26a872daef169c1e64d44

Michele Di Maria 14 Nov 2017, 11:05:55

Awesome, thanks!! ;)

Jimmy 14 Nov 2017, 11:22:01

Perfect, thank you so much :-)

David Mill 14 Nov 2017, 12:49:23

@sebastiaan Thank you for this!

Michele Di Maria 14 Nov 2017, 13:36:17

Also, you should assign a default value for the field umbracouser.userType, otherwise you will not be able to invite new users. I set the default value to 0: ALTER TABLE xxxxxx.umbracouser CHANGE COLUMN userType userType INT(11) NOT NULL DEFAULT '0'

Sebastiaan Janssen 14 Nov 2017, 13:48:56

Can you please create a new issue for this and the reason why you are not able to invite new users (error message)?

Michele Di Maria 14 Nov 2017, 14:16:21

sure! (edit) Here you are: http://issues.umbraco.org/issue/U4-10683

Thanks, Michele

Priority: Normal

Type: Exception

State: Fixed


Difficulty: Normal


Backwards Compatible: True

Fix Submitted: Patch

Affected versions: 7.7.1, 7.7.2, 7.7.4

Due in version: 7.7.5

Sprint: Sprint 72

Story Points: 0.5