[Building Sakai] Puzzle: how to update SAKAI_USER_ID_MAP when LDAP username changes

[Joshua Swink]

This is essentially what we do, and it works fine. So it comes down to
figuring out what went wrong when you were attempting it.

Some possibilities:

* The user had logged in already with the new EID, so a new Sakai user
ID was allocated and assigned. As a result, when you performed that
UPDATE command, it violated a database constraint (duplicate EID
entries in the table) and was not executed. The solution to this is to
do a bit of swapping (see below).

* You were modifying the wrong database?

The overall procedure we use is as follows:

1) Determine whether user has logged in using new EID

select * from SAKAI_USER_ID_MAP where EID in ('<old-eid>', '<new-eid>');

2a) If only 1 result is returned, the user has not logged in. Just change it.

update SAKAI_USER_ID_MAP set EID='<new-eid>' where EID='<old-eid>';

2b) If two results were returned, do a swap.

delete from SAKAI_USER_ID_MAP where EID='<new-eid>';
update SAKAI_USER_ID_MAP set EID='<new-eid>' where USER_ID='<old-eid>';
insert into SAKAI_USER_ID_MAP (USER_ID, EID) values('<new-eid>', '<old-eid>');

3) Invalidate caches.

Josh

On Fri, Oct 15, 2010 at 10:00 AM, will trillich
<will.trillich at serensoft.com> wrote:
> User "jenny" wants to change her LDAP username to "mrsgump". There's only
> one place in the database where her EID is stored, and that's
> SAKAI_USER_ID_MAP.EID.
> So all it should take is and update on the LDAP side, then in the Sakai
> database:
>    UPDATE sakai_user_id_map SET eid = 'mrsgump' WHERE eid = 'jenny';
> and then possibly an admin > memory > reset caches, right? We've even
> restarted Sakai/Tomcat completely, still no luck.
> Apparently there's something more going on.
> When we visit admin > user membership and search for the OLD eid, it's still
> there, with all its worksite associations. The new eid is also visible, with
> no associations, and it has its own USER_ID hexadecimal string now.
> At this point we can UPDATE sakai_user_id_map to set the new EID to just
> about anything and Sakai ignores the changes no matter whether we
> memory>reset caches or even restart Tomcat completely. The new ID has no
> worksite associations, and the old ID still has all its associations -- even
> tho the old ID isn't in the sakai_user_id_map.eid at all. When there's no
> trace of the old EID via the SQL command line, Sakai still shows it as
> happily active.
> This is on 2.6.x. Any suggestions?
>
> --
> Failure is not important. How you overcome it, is.
> -- Nick Vujicic
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org
> with a subject of "unsubscribe"
>