[LON-CAPA-users] score upload and user ids

[Stuart Raeburn]

Ray,

This post is probably more of interest to the lon-capa-admin list than  
the lon-capa-users list, so people who only subscribe to the latter  
may wish to stop reading at this point.

>           Instead of finding two usernames with the same student ID,  
>  what we have actually found is that the dump of ids.db showed a   
> *different* student ID associated with the particular username

Yes, I would only expect a single username for a given ID in ids.db

> So, either there are two IDs in that db and only one is shown in the  
>  dump, or else there is a second place where IDs are stored?

Within LON-CAPA IDs are stored in:

(a) the environment.db file for each user
(b) the classlist.db file for each course
(c) the ids.db file for each library server in the domain
(d) the allusers table in the loncapa database in MySQL.

Of these (a) and (d) will contain the same information; (a) should be  
considered definitive.

Because the ids.db GDBM db file found on each library server in a  
domain is a simple key => value database file (where each key is  
unique, and is a student/employee ID), only one username can be stored  
at any time for a given ID.  Use of ids.db was implemented in  
lonnet.pm rev 1.70 (November 2000) to provide a way to do a quick  
look-up of the username, given a student/employee ID.

However, there is a deficiency in this approach, as you have  
encountered, in cases where different users are assigned the same ID  
in a particular domain.

In particular if I create a new LON-CAPA user in my domain as:
username | ID
frasersi | 123456

and then later I (or someone else) adds another new user as:

username | ID
burnabym | 123456

the current entry in ids.db would be
123456 => burnabym

while the user management interface (which reads from either  
classlist.db, or environment.db, depending on context - course or  
domain) would still report the ID for frasersi as: 123456

You can check which usernames share the same ID in a domain by using  
the MySQL queries I described in my previous post, e.g.,

select username from allusers where id='a12345678';

to determine which users share the id: a12345678

or:

select username,id, count(id) as num from allusers group by id order
by num desc;

to determine which IDs in your domain are currently not unique.

*********************************
General comments on best practice
*********************************

Given that IDs may be used as a user's identifier for some operations  
in LON-CAPA (e.g., grading bubblesheets or uploading scores from a csv  
file containing IDs and points as column data), it is desirable that  
IDs are unique for users in a domain (as is so for usernames).

LON-CAPA, by default, does not force use of unique IDs in a domain,  
but as Domain Coordinator you can configure things to make it  
difficult for Course Coordinators and/or Authors to assign non-unique  
IDs to users they add to their courses or add as co-authors to their  
authoring spaces.

You may wish to assign new IDs for any users with non-unique IDs, and  
then modify the Domain Configuration to disallow setting of IDs when  
Course Coordinators and/or authors create or modify users.

If you do that you will likely also want to implement a mechanism to  
keep user information up to date using the nightly Autoupdate, and  
allow automatic setting of IDs when new users are added.  Both of  
these rely on the ability to access your institution's centralized  
information systems, and also require customization of localenroll.pm,  
as discussed in the Domain Coordination manual.


Stuart Raeburn
LON-CAPA Academic Consortium

Quoting Raymond Batchelor <batchelo at sfu.ca>:

> Hi Stuart,
>           Instead of finding two usernames with the same student ID,  
>  what we have actually found is that the dump of ids.db showed a   
> *different* student ID associated with the particular username than   
> what is found in the user management interface.  Thus I infer that   
> the ids.db has incorrect info somehow.  Possibly associated with a   
> reassigned username in about 2004.
>
> So, either there are two IDs in that db and only one is shown in the  
>  dump, or else there is a second place where IDs are stored?
>
> Suggestion?
>
> Thanks,
> Ray
>
> ----- Original Message -----
> From: "Stuart Raeburn" <raeburn at msu.edu>
> To: lon-capa-users at mail.lon-capa.org
> Sent: Wednesday, 17 October, 2012 21:00:04
> Subject: Re: [LON-CAPA-users] score upload and user ids
>
> Ray,
>
>> My working hypothesis, at this point, is that perhaps there is
>> another user who might have been given the same userid (presumably
>> by override... possibly by some other course coordinator).
>
> Not an unreasonable hypothesis since, if only student/employee IDs are
> included (no usernames) in the uploaded csv data, LON-CAPA (when
> attempting to process the grade data) will retrieve the usernames via
> a look-up of the student/employee IDs in the GDBM file:
> /home/httpd/lonUsers/<domain>/ids.db on your library server, and a
> given ID will only occur once.
>
> If a particular username:domain is not found in the classlist,
> LON-CAPA should alert you to this, with the message:
>
> No scores stored for the following username(s):
> followed by the list of users who were not in the classlist.
>
> Anyway, if you have command line access to your library server you
> could check for the current mapping in ids.db of a given id to a
> single username with the command:
>
> /home/httpd/perl/debug/dump_db.pl -u
> /home/httpd/lonUsers/<domain>/ids.db |grep <id>
>
> replacing <domain> with your domain, and <id> with the id for which
> you suspected a duplicate.
>
> Another way to do this is to query the allusers table in the MySQL
> database, again from the command line, by starting a MySQL session:
>
> /usr/bin/mysql -uroot -p loncapa
>
> (You would need to enter the password you set for the MySQL root user
> when you set-up the MySQL database).  If you don't remember the
> password for that user then you can be the MySQL www user instead:
>
> /usr/bin/mysql -uwww -p loncapa
>
> (Contact me off-list for the password used for the MySQL www user, if
> you do not currently know it).
>
> Once you have authenticated, and have the mysql prompt:
> mysql>
>
> enter (for example):
>
> select username from allusers where id='a12345678';
>
> to find out the usernames of all users who have the ID: a12345678.
>
> If you are curious to find out just how may IDs have multiple
> usernames in your domain you could enter the following query to
> display which IDs are associated with more than one username (lookng
> for the entries early in the output, where num > 1).
>
> select username,id, count(id) as num from allusers group by id order
> by num desc;
>
> For any where num is more than 1, you can use another query to find
> out what those usernames are for a particular ID (e.g., a23548923)
>
> select username from allusers where id = 'a23548923';
>
> When done use:
>
> quit;
>
> to end your MySQL session.
>
> Note: ids stored in the ids.db GDBM file, and in the allusers table
> have any letters present as lower case. Similarly, if the IDs in the
> uploaded file included any upper case letters, they will be lower
> cased before being passed by the score upload process to the LON-CAPA
> look-up in the ids.db file.
>
>
> Stuart Raeburn
> LON-CAPA Academic Consortium
>
>
> Quoting Raymond Batchelor <batchelo at sfu.ca>:
>
>> On the odd occasion, when uploading marks to a "score upload special
>>  document", I have found that the upload fails for some individual
>> student, when the students are identified by their "Student/employee
>>  ID" rather than their "Student username".   In these cases the
>> actual Student ID in both LON-CAPA AND in my csv file being uploaded
>>  are identical.
>>
>> My working hypothesis, at this point, is that perhaps there is
>> another user who might have been given the same userid (presumably
>> by override... possibly by some other course coordinator).
>>
>> My question is:  How do I locate, in LON-CAPA, the other student
>> whose id is in conflict with my own student's?
>>
>> --
>> Ray
>> _______________________________________________
>> LON-CAPA-users mailing list
>> LON-CAPA-users at mail.lon-capa.org
>> http://mail.lon-capa.org/mailman/listinfo/lon-capa-users
>
> _______________________________________________
> LON-CAPA-users mailing list
> LON-CAPA-users at mail.lon-capa.org
> http://mail.lon-capa.org/mailman/listinfo/lon-capa-users
> _______________________________________________
> LON-CAPA-users mailing list
> LON-CAPA-users at mail.lon-capa.org
> http://mail.lon-capa.org/mailman/listinfo/lon-capa-users