Faculty login fails with reference to tempdb.dbo.#SecurityGroupNodes

[sn...@bsd.uchicago.edu]

version 2.9

We have a faculty member unable to authenticate and edit her profile with known good credentials, receiving the following error message:

"Message: Cannot insert the value NULL into column 'SecurityGroupNode', table 'tempdb.dbo.#SecurityGroupNodes__...000000010973'; column does not allow nulls. INSERT Fails. The statement has terminated. Null value is eliminated by an aggregate or other SET operation. SOURCE: Profiles FORM: QUERYSTRING:"

I should add, as an authenticated proxy editor (for all faculty), when I view the profile I do not see an 'edit this profile' link as expected. So far I haven't found a second profile with this issue.

Finally, we've upgraded to 3.1.0 in our downstream test environment, using an upgraded clone of the prod 2.9 db and I see the same unexpected behavior there as well.

Next place to look?

Thanks, 

Stephan

[Brown, Nicholas William]

Stephan

 

I don’t remember ever seeing this error before. I would guess that there is an error in the node / triple table for a triple related to that user. This is leading to a null being returned by the [RDF.Security].[GetSessionSecurityGroupNodes] stored procedure.

 

The next steps I would take in troubleshooting would be to confirm this by looking up a sessionID for this user and running the [RDF.Security].[GetSessionSecurityGroupNodes] proc in SSMS with that sessionID to confirm that a null is returned in the list of node IDs. If so, I would attempt to step through each of the select queries that are unioned together in that stored procedure to identify where the null is coming from. Hopefully this would give enough information to identify the error.

 

Nick

--
You received this message because you are subscribed to the Google Groups "ProfilesRNS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to profilesrns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/profilesrns/ce4ab4cb-9cab-44ca-8a1f-033e30d8faa5n%40googlegroups.com.

[sn...@bsd.uchicago.edu]

Nick --

Thanks for this. We deconstructed the procedure as instructed and the second part of the union revealed a total of 17 cases (1 reported, 16 unreported) where faculty have null values for node_id in [RDF.Stage].InternalNodeMap, but we're unsure of how to proceed, i.e. attempt to populate these versus removing the offending rows.

On a side note, [ProfilesRNS].[User.Session].[Session] currently contains 71+ million rows. I seem to recall we can clean this up?

Stephan

[Brown, Nicholas William]

Stephan

 

The first thing you need to do is to see whether the internalNodeMapID for these users is stored in the Node table

 

select top 100 * from [RDF.Stage].[InternalNodeMap] a

       join [RDF.].Node n

       on a.InternalNodeMapID = n.InternalNodeMapID and class = 'http://xmlns.com/foaf/0.1/Person' and a.NodeID is null

 

If it is, you can update the InternalNodeMap table from the NodeID Table

 

Update a set a.NodeID = n.NodeID, a.ValueHash = n.ValueHash

       from [RDF.Stage].[InternalNodeMap] a

              join [RDF.].Node n

              on a.InternalNodeMapID = n.InternalNodeMapID and class = 'http://xmlns.com/foaf/0.1/Person' and a.NodeID is null

 

If not, you will need to work out the NodeID for these 17 users (I think the easiest way to do this would be to search for the users in profiles and get their NodeID from the URL), and update the nodeID in the [RDF.Stage].InternalNodeMap table manually.

 

Once you have done this you will need to update the InternalNodeMapID in the node table

 

Update n set n.InternalNodeMapID = a.InternalNodeMapID

       from [RDF.].Node n

              join [RDF.Stage].[InternalNodeMap] a

              on a.NodeID = n.NodeID and a.InternalNodeMapID in (<List Problem internalNodeMapIDs here>)

 

And then fix the valuehash in the internalNodeMap table

 

Update a set a.ValueHash = n.ValueHash

       from [RDF.Stage].[InternalNodeMap] a

              join [RDF.].Node n

              on a.InternalNodeMapID = n.InternalNodeMapID and a.InternalNodeMapID in (<List Problem internalNodeMapIDs here>)

 

 

With respect to the session table, there are no problems with deleting old data from this table. At Harvard we keep the session data so that we can look at historical usage. To prevent the table growing too big we periodically copy this data to another database server periodically and delete old data from this table. The best way to shrink this table is to run the following:

 

Exec [User.Session].[DeleteOldSessionRDF]

 

(This will delete the node and triple records associated with the sessions you are deleting)

 

delete from [User.Session].[Session] where DateDiff(dd,LastUsedDate,GetDate()) >= 7

 

This will delete any sessions more than a week old from the table.

 

If you don’t care about maintaining the historical record in the user.session table, you could make a few modifications to [User.Session].[DeleteOldSessionRDF] to delete the records from the session table at the same time as it deletes the nodes and triples.

To view this discussion on the web visit https://groups.google.com/d/msgid/profilesrns/14dddba6-a03a-4953-8f68-ba2261d1de67n%40googlegroups.com.