Column 'DBUserSid' does not belong to table DBUsers.

Mar 30, 2010 at 4:19 AM

Hi Andrea! I'm using the latest 3.6.0.5 version and getting the following error under load:

[ArgumentException: Column 'DBUserSid' does not belong to table DBUsers.]
   System.Data.DataRow.GetDataColumn(String columnName) +1775301
   System.Data.DataRow.get_Item(String columnName) +13
   NetSqlAzMan.SqlAzManDBUser..ctor(DataRow DBUserDataRow) +63
   NetSqlAzMan.SqlAzManApplication.GetDBUser(String userName) +87
   NsamManager.GetItemsForUser(String userName, Nullable`1 type) +53

Do you know what can cause this error? Everything worked fine in development, but it started to happen when we moved to production. Thanks!

Mar 30, 2010 at 7:33 AM

There is something wrong into the dbo.GetDBUsers() sql function or in dbo.UsersDemo table.

Please check.

Regards,
Andrea.

__________________________________
Andrea Ferendeles
NetSqlAzMan Project Coordinator  
E-mail aferende@hotmail.com Web http://netsqlazman.codeplex.com

Mar 30, 2010 at 9:14 AM

The problem is this error occurs not always, only sometimes, when several users browse application simultaneously. For example this error occurs, user refreshes the page and everything works fine. My GetDBUsers() sql function is following:

ALTER FUNCTION [dbo].[netsqlazman_GetDBUsers] (@StoreName nvarchar(255), @ApplicationName nvarchar(255), @DBUserSid VARBINARY(85) = NULL, @DBUserName nvarchar(255) = NULL)  
RETURNS TABLE
AS  
RETURN
    SELECT TOP 100 PERCENT CONVERT(VARBINARY(85), UserId) AS DBUserSid, UserName AS DBUserName FROM dbo.aspnet_Users
    WHERE
        (@DBUserSid IS NOT NULL AND CONVERT(VARBINARY(85), UserId) = @DBUserSid OR @DBUserSid  IS NULL)
        AND
        (@DBUserName IS NOT NULL AND UserName = @DBUserName OR @DBUserName IS NULL)
    ORDER BY UserName

Beside this error I'm sometimes getting another one:

[InvalidCastException: Specified cast is not valid.]
   System.Data.SqlClient.SqlBuffer.get_Int32() +4838981
   System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i) +38
   Read_ItemsResult(ObjectMaterializer`1 ) +82
   System.Data.Linq.SqlClient.ObjectReader`2.MoveNext() +29
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +7665172
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +61
   NetSqlAzMan.SqlAzManApplication.GetItems(ItemType itemType) +793

I'm not sure if this is NetSQLAzMan issues or not, but I'm getting both errors only when invoking NetSQLAzMan functions. Before upgrading to 3.6.0.5, I was getting "Invalid attempt to call Read when reader is closed." and "There is already an open DataReader associated with this Command which must be closed first.", after upgrade this errors seemed to go away, but these new ones appeared. Maybe they are connected somehow?

Also, I have a class where I store StorageCache, Storage and Application between requests using singleton pattern, is it possible this is causing this error because of thread unsafety or something else? Here is the code:

    private static StorageCache _storageCache;
    public static StorageCache StorageCache
    {
        get
        {
            if (_storageCache == null)
            {
                _storageCache = new StorageCache(connectionString);
                _storageCache.BuildStorageCache(storeName, applicationName);
            }

            return _storageCache;
        }
    }

	private static IAzManStorage _Storage = null;
	public static IAzManStorage Storage {
		get {
			if (_Storage == null) {
				_Storage = StorageCache.Storage;
     			}
			return _Storage;
		}
	}

    private static IAzManApplication _application;
	public static IAzManApplication Application {
	    get {
                if (_application == null)
                {
                    _application = Storage.GetStore(storeName).GetApplication(applicationName);
                }
                return _application;
	    }
	}

    public static void RefreshCache()
    {
        StorageCache.BuildStorageCache(storeName, applicationName);
        _application = null;
    }
Thanks!

Mar 30, 2010 at 9:19 AM

Hi,

I think that you have a thread safe issue.

Try to change this method:

    public static void RefreshCache()
    {
        StorageCache.BuildStorageCache(storeName, applicationName);
        _application = null;
    }

As the following:

          private static object locker = new object();

    public static void RefreshCache()
    {
        lock (locker)
        {
                StorageCache.BuildStorageCache(storeName, applicationName);
               _application = null;
        }
    }

Let me know.

Regards,

Andrea.

__________________________________
Andrea Ferendeles
NetSqlAzMan Project Coordinator  
E-mail aferende@hotmail.com Web http://netsqlazman.codeplex.com

Mar 31, 2010 at 6:44 AM

Hi Andrea,

Seems I've partially fixed the errors. But still not sure if this is NetSQLAzMan or LINQ or maybe SQL Server issue.

There are about 5 different errors that randomly occur under load. I made a load test with AutomateQA  TestComplete 7 with up to 5 virtual users making same requests simultaneously.

I reduced number of errors from 30-40 to 5-15 per test session by doing following things:

1) Added "MultipleActiveResultsSet=true" to connection string

2) Replaced Application.GetDBUser() with invoking Storage.DBUsers once (which actually caches all users in the system) and then getting single user directly from this cache like:

 

Storage.DBUsers.Single(u => u.Key.ToLower() == userName.ToLower()).Value

3) Added lock to StorageCache.BuildStorageCache, like you suggested

If you like, I can email you or post here stack traces of errors I'm still getting.

 

Thank you,

Andrey

 

Mar 31, 2010 at 1:15 PM
Edited Apr 2, 2010 at 8:09 AM

Good job.

Yes … please post your code changes so other users could benefit of this changes.

 

What about

TY.

 

__________________________________
Andrea Ferendeles

Apr 1, 2010 at 9:29 PM

I just ran into this error for the first time myself.

I am not using any wrapper classes, just the code provided.  This issue and the other issues I just posted seem to come from the RoleProvider class.

I have this call in the master page:

string[] currentUserRoles = Roles.GetRolesForUser();

It generated this error:

[ArgumentException: Column 'DBUserSid' does not belong to table DBUsers.]
System.Data.DataRow.GetDataColumn(String columnName) +1777717
System.Data.DataRow.get_Item(String columnName) +13
NetSqlAzMan.SqlAzManDBUser..ctor(DataRow DBUserDataRow) +103
NetSqlAzMan.SqlAzManApplication.GetDBUser(String userName) +226
NetSqlAzMan.Providers.NetSqlAzManRoleProvider.GetRolesForUser(String username) +830
System.Web.Security.RolePrincipal.GetRoles() +158
System.Web.Security.Roles.GetRolesForUser(String username) +365
System.Web.Security.Roles.GetRolesForUser() +14

Then the error turns into, this (which may because of other failure)?

[InvalidCastException: Specified cast is not valid.]
System.Data.SqlClient.SqlBuffer.get_Int32() +4841797
System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i) +38
Read_ItemsResult(ObjectMaterializer`1 ) +82
System.Data.Linq.SqlClient.ObjectReader`2.MoveNext() +29
System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +7667540
System.Linq.Enumerable.ToList(IEnumerable`1 source) +61
NetSqlAzMan.SqlAzManApplication.GetItems(ItemType itemType) +1300
NetSqlAzMan.Providers.NetSqlAzManRoleProvider.GetRolesForUser(String username) +102
System.Web.Security.RolePrincipal.IsInRole(String role) +164
System.Web.Configuration.AuthorizationRule.IsTheUserInAnyRole(StringCollection roles, IPrincipal principal) +123
System.Web.Configuration.AuthorizationRule.IsUserAllowed(IPrincipal user, String verb) +256
System.Web.Configuration.AuthorizationRuleCollection.IsUserAllowed(IPrincipal user, String verb) +199
System.Web.Security.UrlAuthorizationModule.OnEnter(Object source, EventArgs eventArgs) +8776748
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +68
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75

Apr 2, 2010 at 5:59 AM

Duke84,

  I have found the issue at the bottom of all of this....and I can say this is going to be quite a bit of work and a re-design of how everything hooks together and such in this project.

I create a single webpage that would reproduce the above issues every time it was opened.....I then did a proof of concept change on this and I went to ZERO errors.

I am looking into the best way to fix this completely, I will let you know....

Andrea has not agreed to post my last enhancement feature yet.  Evidently on this project if you provide a feature that he deems does not impact many people (even though its non-use is a no impact change), he will not commit it.   (I could use your support and evidently require votes to get it committed? http://netsqlazman.codeplex.com/WorkItem/View.aspx?WorkItemId=5806 ) I have never had this issue with any other open source contributions, so I am reluctant to do all the changes that are about to be required for fear of the same thing.  Plus my source right now is a branch of the main source with quite a few additional changes, just for the above proof/prototype.  This project is open source, but does not appear to be much in the way of feature contribution and continued enhancement??

 

Apr 2, 2010 at 6:38 AM

Previously I said I fixed these errors, but they seemed to be appearing again. Fortunately, I've finally found the reason of these errors and fixed them all.

Previously, one load testing session (5 virtual users) gave me from 20 up to 40 errors, now number of errors is ZERO. Here is my suggestions:

1) You should never share Storage, Store or Application objects between reqests. Also, if you share StorageCache object between requests, never use StorageCache.Storage property to get storage. The problem is Storage, Store and Application share one datacontext object, and if several users are trying to use same datacontext, those errors appears. In my oppinion, this is a design issue. Everywhere on LINQ related community is told: "Use one datacontext for each atomic operation".

2)  GetDBUser method is not thread-safe. You can't use this method by several users simultaneously, otherwise you'll get transaction error.

3) GetItems method is not thread-safe. I'm not sure why, but I was getting errors on this method under load.

My conclusion is: current design of NetSQLAzMan library is not suitable for multi-threading.

To fix all errors I created synchronized wrapper method for each NetSQLAzMan function, for example:

    public static IAzManDBUser GetDBUserSync(string userName) 
    {
        lock (getDBUserLock)
        {
            return Application.GetDBUser(userName);
        }
    }
    public static IAzManItem[] GetRoles() {
        lock (itemLock)
        {
            IAzManItem[] items = Application.GetItems(ItemType.Role);
        }
    }

and so on...
The problem has gone away. Also, I removed MultipleActiveResultSets attribute from connection string.

Hope this helps. If anybody knows better solution, please post it here. Thanks!

Apr 2, 2010 at 6:51 AM

100% agree - this is a total design issue.

However, if you take something like the RoleProvider - it is just completely broken as it uses all the above things inside itself.

It is not my intention to do the locking you have done, I intend on fixing the library.  :-)

I threw a fix into the library for the application object already to not share context (which mostly fixes roleprovider), now just trying to figure out the best way to do it across everything.

However, I need to see how the 'contributions' thing is going to play out in this project....I may just be branching off on my own code (which has already started to happen).  I would rather contribute the changes back to the community though.

Thanks for your information Duke!

Apr 2, 2010 at 8:10 AM

Resolved with changeset 46429.

Apr 2, 2010 at 8:27 AM

Waynebrantley,

I do not understand why you're doing this unnecessary controversy.
1) The fact that a project is open source does not mean that everyone can modify the source code on the master project. Those who wish may do so freely, licensed under the Ms-Pl, on your PC.

2) The decision on acceptance or not of a patch proposed by users remains indergbilmente to the project owner (me);
CodePlex offers this opportunity but with two buttons (for owners): Accept and Reject.

3) The fact that a patch in my opinion should be applied only if really necessary and requested by different users remains a my prerogative.

4) No one is doubting your skill, so I do not understand why you feel offended don't from my refusal to publish the patch.
The patch remains visible to all users and everyone can enjoy, even if this is not part of the main distribution.

5) You say you have contributed to many open-source projects. For me this does not seems a collaborative behavior.
I spend several hours of my time per day to keep alive, free, and update my project and I don't think this is an attitude of gratitude towards me (given your words).

6) You can contribute to an open-source project in many ways: writing into the forum, giving ideas, disseminating the project and providing useful information to all.
Being able to modify the source code on the master project is just a detail. If you thing this is essential for you, rather than continue with this polemical tone,
you can always abandon the project and use another product.

Regarding the specific problem about NetSqlAzManRoleProvider, this morning I made the class thread safe, according to the suggestions from all users.

I invite all (you too) to do the necessary tests (tests contribute to the project) and let me know how it goes.

Best Regards,
Andrea.
__________________________________
Andrea Ferendeles
NetSqlAzMan Project Coordinator 
E-mail aferende@hotmail.com Web http://netsqlazman.codeplex.com

 

Apr 2, 2010 at 3:29 PM

Resolved with changeset....that is GREAT....thanks.

1-6) Agree with you.

I am sorry for appearing controversial - I do not mean it to come over that way.  Email/Forums are a poor conveyor of 'tone'.

I worked hard to make my additions where they would not have any impact on any user that did not use them, so there would be no reason to not commit - so right now I am just a bit frustrated and confused.

I am just trying to figure out what to do on this project going forward.

I apologize for offending you and all the great work you have done.

Apr 2, 2010 at 5:20 PM
Re-read my comment, it does appear controversial and again I apologize for that, it was 1 am in the morning. :-)