Did anyone else notice this? I'm wondering if this might be one of the reasons for the out of sync users....
When the user is added via the AddUser method in UserController, I noticed that when its marked to add to the membership tables (when is it not?) it adds the user to that table first. That is probably fine and dandy however when it goes against the DataProvider
to add in the user next, running the stored procedure alone I noticed that it fails. Why? The password is a not null field in the database and is at no time required to be entered as a paramater by the stored procedure. Is this right? Was that intended?
Shouldn't the table be remarked to allow a null value there since after all that password isn't what's desired now? Since the user never gets into the DNN Users table, the user is now out of sync.
Someone please tell me that wasn't it or there is more to it.
The bug is if you are deleted from a portal you are deleted from everything. If the portal itself is deleted all users within that portal are deleted from the DNN users table. The probelm is however they are NOT deleted from the membership ones. What this
creates is out of sync users who must be cleared from the membership tables BEFORE they can ever become a user in membership again. This was not resolved around 3.1.0 and I'm wondering was it in 3.1.1. This is an issue because we are having this very problem
now.
The bug in this thread is what I have been talking about.
I hadn't heard of the one you are talking about, has it been logged? http://support.dotnetnuke.com
I recently upgraded a DNN site from 2.1.2 to 3.1.1 and I received an email from someone attempting to register today. He received this message:
The username specified is invalid. Please specify a valid username.
1. I spent the day deleting users in mass. Our site is for paid members and the majority of unapproved accounts were from non-members.
2. I had several a child portal with members when the site was 2.1.2. I did delete several of them.
3. I've run the SQL queries posted earlier with no errors and no change. New Users still get the above message.
I too have had this issue. The problem revolves around the membership tables. What you have to accomplish is after you've sync'ed the users, the invalid usernames you'll have to completely remove from the membership tables as well, not just the old Users
table.
Be aware also that what I would suggest doing is in the Users table, mark the Password field to allow a NULL value - reason behind this is the stored procedures are still attempting to update this table which cannot happen due to no password being fed to the
table. I can't say if your install kept the password field on the Users table but mine did and I had to make that adjustment. This might be the reason your users kept going out of sync is because an error is thrown on the procedure that adds the users to
the older Users tables (Users, UserPortals, UserRoles).
I've noticed that even though I've logged a call for this issue, still not assigned? On this issue I believe I've found another problem regarding assignment to roles in the membership tables only; when attempting to add a user to a role at times various usernames
aren't updating. They do in the UserRoles table as if you edit the user directly and look at his/her role, it will show properly however it does not in the security roles.
I checked the aspnet_membership table and found that the unauthorized users that I deleted (Through a 3rd party module, not through DNN's user accounts module, my bad!) were still showing up.
As my knowledge of the aspnet_ tables isn't that great, could someone provide an example script that would bring balance to these tables? I've backed up my DB but I'm fearful of making the dogs breakfast out of it.
It goes back to the SQL script in this thread. That will remove the out of sync users (users that exist in membership tables but not in the older DNN ones). That's what will clean out the membership tables but to prevent it there are a few issues you got
to be aware of:
check for that password field in the Users table; if you still have it there and its still requiring a non-null value, you'll have to update the table manually to allow a null for the password.
there seems to be a bug in which if you delete a user, you delete them from the Users table which creates a out of sync senario. I'm still waiting on a fixed or not in 3.1.1 though.
there also seems to be an issue if you remove a portal, all users are hence deleted from the Users table again. Again not sure if it was fixed in 3.1.1.
AssemblyVersion: 03.01.01 Method: ASP.Users_ascx.__DataBind__control19 FileName: FileLineNumber: 0 FileColumnNumber: 0 PortalID: 0 PortalName: AMPS UserID: 2 UserName: host ActiveTabID: 11 ActiveTabName: User Accounts AbsoluteURL: /dnn/Default.aspx AbsoluteURLReferrer: http://www.amps-armor.org/dnn/Admin/UserAccounts/tabid/11/Default.aspx ExceptionGUID: a4b8a400-fb20-47ca-8654-5c4f1d2f1c00 DefaultDataProvider: DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider InnerException: Object reference not set to an instance of an object. Message: DotNetNuke.Services.Exceptions.PageLoadException: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object. at ASP.Users_ascx.__DataBind__control19(Object
sender, EventArgs e) at System.Web.UI.Control.OnDataBinding(EventArgs e) at System.Web.UI.Control.DataBind() at System.Web.UI.Control.DataBind() at System.Web.UI.Control.DataBind() at System.Web.UI.WebControls.DataGrid.CreateItem(Int32 itemIndex, Int32 dataSourceIndex,
ListItemType itemType, Boolean dataBind, Object dataItem, DataGridColumn[] columns, TableRowCollection rows, PagedDataSource pagedDataSource) at System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource) at System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs
e) at System.Web.UI.WebControls.BaseDataList.DataBind() at DotNetNuke.Modules.Admin.Users.UserAccounts.BindData(String SearchText, String SearchField) at DotNetNuke.Modules.Admin.Users.UserAccounts.ddlRecordsPerPage_SelectedIndexChanged(Object sender, EventArgs
e) at System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e) at System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() at System.Web.UI.Page.RaiseChangedEvents() at System.Web.UI.Page.ProcessRequestMain()
--- End of inner exception stack trace --- StackTrace: Source: Server Name: DEDI421
I checked the tables and there are 609 rows in the aspnet_user and 388 in the User. There are also duplicates in the aspnet_user tables. This can't be right.
The problem you are describing along with the error screen is still a matter of duplicates in the membership tables that do not exist in the Users table. If a username is part of multiple portals, it will appear more than once in the membership tables as well.
After running the SQL script to clear out the duplicates, you'll have to look up usernames that exist in membership table and not in the Users table and manually remove them. Typically that means removing the profile entry, the roles entries, and then the
user entry itself.
When I looked at this again, the deletes in the SQL script can be changed to the following and should remove all duplicate entries:
delete aspnet_Membership where UserId
IN
(select distinct am.UserId from aspnet_Membership am inner join aspnet_Users au on am.UserId = au.UserId
where au.Username =@UserName)
delete aspnet_Profile where UserId IN
(select distinct ap.UserId from aspnet_Profile ap inner join aspnet_Users au on ap.UserId = au.UserId
where au.Username =@UserName)
delete aspnet_UsersInRoles where UserId
in (select distinct uir.UserId from aspnet_UsersInRoles uir inner join aspnet_Users au on uir.UserId = au.UserId
Thanks again, but no joy on that updated statement. The number of records in the tables didn't change.
Found one name out of sync but when I attempted to delete it from the aspnet_users table after deleting it from the other tables, I got an FK constraint error...
razorphreak
Member
540 Points
108 Posts
Re: User Accounts is currently unavailable
Oct 14, 2005 10:58 PM|LINK
When the user is added via the AddUser method in UserController, I noticed that when its marked to add to the membership tables (when is it not?) it adds the user to that table first. That is probably fine and dandy however when it goes against the DataProvider to add in the user next, running the stored procedure alone I noticed that it fails. Why? The password is a not null field in the database and is at no time required to be entered as a paramater by the stored procedure. Is this right? Was that intended? Shouldn't the table be remarked to allow a null value there since after all that password isn't what's desired now? Since the user never gets into the DNN Users table, the user is now out of sync.
Someone please tell me that wasn't it or there is more to it.
J7Mitch
Star
13145 Points
2632 Posts
Re: User Accounts is currently unavailable
Oct 14, 2005 11:08 PM|LINK
The bug in this thread is what I have been talking about.
I hadn't heard of the one you are talking about, has it been logged? http://support.dotnetnuke.com
razorphreak
Member
540 Points
108 Posts
Re: User Accounts is currently unavailable
Oct 17, 2005 03:10 PM|LINK
DanaNield
Member
35 Points
7 Posts
Re: User Accounts is currently unavailable
Nov 01, 2005 03:09 AM|LINK
Hi Folks,
I recently upgraded a DNN site from 2.1.2 to 3.1.1 and I received an email from someone attempting to register today. He received this message:
The username specified is invalid. Please specify a valid username.
1. I spent the day deleting users in mass. Our site is for paid members and the majority of unapproved accounts were from non-members.
2. I had several a child portal with members when the site was 2.1.2. I did delete several of them.
3. I've run the SQL queries posted earlier with no errors and no change. New Users still get the above message.
Any help would be greatly appreciated.
Dana
razorphreak
Member
540 Points
108 Posts
Re: User Accounts is currently unavailable
Nov 01, 2005 02:37 PM|LINK
Be aware also that what I would suggest doing is in the Users table, mark the Password field to allow a NULL value - reason behind this is the stored procedures are still attempting to update this table which cannot happen due to no password being fed to the table. I can't say if your install kept the password field on the Users table but mine did and I had to make that adjustment. This might be the reason your users kept going out of sync is because an error is thrown on the procedure that adds the users to the older Users tables (Users, UserPortals, UserRoles).
I've noticed that even though I've logged a call for this issue, still not assigned? On this issue I believe I've found another problem regarding assignment to roles in the membership tables only; when attempting to add a user to a role at times various usernames aren't updating. They do in the UserRoles table as if you edit the user directly and look at his/her role, it will show properly however it does not in the security roles.
DanaNield
Member
35 Points
7 Posts
Re: User Accounts is currently unavailable
Nov 01, 2005 03:57 PM|LINK
I checked the aspnet_membership table and found that the unauthorized users that I deleted (Through a 3rd party module, not through DNN's user accounts module, my bad!) were still showing up.
As my knowledge of the aspnet_ tables isn't that great, could someone provide an example script that would bring balance to these tables? I've backed up my DB but I'm fearful of making the dogs breakfast out of it.
Dana
razorphreak
Member
540 Points
108 Posts
Re: User Accounts is currently unavailable
Nov 01, 2005 04:40 PM|LINK
DanaNield
Member
35 Points
7 Posts
Re: User Accounts is currently unavailable
Nov 08, 2005 02:40 PM|LINK
Thanks for the help so far, but I'm still out of luck.
More details:
When I attempt to view more than ten members in user accounts I get the following error:
Method: ASP.Users_ascx.__DataBind__control19
FileName:
FileLineNumber: 0
FileColumnNumber: 0
PortalID: 0
PortalName: AMPS
UserID: 2
UserName: host
ActiveTabID: 11
ActiveTabName: User Accounts
AbsoluteURL: /dnn/Default.aspx
AbsoluteURLReferrer: http://www.amps-armor.org/dnn/Admin/UserAccounts/tabid/11/Default.aspx
ExceptionGUID: a4b8a400-fb20-47ca-8654-5c4f1d2f1c00
DefaultDataProvider: DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider
InnerException: Object reference not set to an instance of an object.
Message: DotNetNuke.Services.Exceptions.PageLoadException: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object. at ASP.Users_ascx.__DataBind__control19(Object sender, EventArgs e) at System.Web.UI.Control.OnDataBinding(EventArgs e) at System.Web.UI.Control.DataBind() at System.Web.UI.Control.DataBind() at System.Web.UI.Control.DataBind() at System.Web.UI.WebControls.DataGrid.CreateItem(Int32 itemIndex, Int32 dataSourceIndex, ListItemType itemType, Boolean dataBind, Object dataItem, DataGridColumn[] columns, TableRowCollection rows, PagedDataSource pagedDataSource) at System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource) at System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) at System.Web.UI.WebControls.BaseDataList.DataBind() at DotNetNuke.Modules.Admin.Users.UserAccounts.BindData(String SearchText, String SearchField) at DotNetNuke.Modules.Admin.Users.UserAccounts.ddlRecordsPerPage_SelectedIndexChanged(Object sender, EventArgs e) at System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e) at System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() at System.Web.UI.Page.RaiseChangedEvents() at System.Web.UI.Page.ProcessRequestMain() --- End of inner exception stack trace ---
StackTrace:
Source:
Server Name: DEDI421
Dana
razorphreak
Member
540 Points
108 Posts
Re: User Accounts is currently unavailable
Nov 08, 2005 03:46 PM|LINK
When I looked at this again, the deletes in the SQL script can be changed to the following and should remove all duplicate entries:
delete aspnet_Membership where UserId IN
(select distinct am.UserId from aspnet_Membership am inner join aspnet_Users au on am.UserId = au.UserId
where au.Username =@UserName)
delete aspnet_Profile where UserId IN
(select distinct ap.UserId from aspnet_Profile ap inner join aspnet_Users au on ap.UserId = au.UserId
where au.Username =@UserName)
delete aspnet_UsersInRoles where UserId
in (select distinct uir.UserId from aspnet_UsersInRoles uir inner join aspnet_Users au on uir.UserId = au.UserId
where au.Username =@UserName)
Try that and see what happens.DanaNield
Member
35 Points
7 Posts
Re: User Accounts is currently unavailable
Nov 08, 2005 05:10 PM|LINK
Found one name out of sync but when I attempted to delete it from the aspnet_users table after deleting it from the other tables, I got an FK constraint error...