[LON-CAPA-admin] Need a way to change all student IDs in our database

[Jeffrey Wilson]

Hi,

I will be gone for a few weeks and will use the Web GUI to do the userid 
update for our 12K users when I get back.  I'll contact you after that 
for the script to remove the old info from the ids.db file.

I am encouraged that we should be able to remove all of the old id info 
eventually.  Fortunately, I don't have to complete that task before the 
Fall semester starts.

Thank you for your help.

Jeff Wilson




On 7/25/13 3:37 PM, Stuart Raeburn wrote:
> Hi,
>
>> I have looked at some individual user directories to see where the
>> offending IDs might appear,
>
> As discussed in an earlier post I made to the lon-capa-users mailing 
> list, see:
> http://mail.lon-capa.org/pipermail/lon-capa-users/2012-October/004201.html 
>
>
>> 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.
>
> The allusers table is completely rebuilt three times weekly by this 
> entry in /etc/cron.d/loncapa
>
> 10 1 * * 2,4,6    www    /home/httpd/perl/searchcat.pl
>
> If you take a look at the code in searchcat.pl you will see that the 
> entries written to the allusers table are read from each user's 
> environment.db GDBM file, which is considered the authoritative source 
> of username => ID mapping.
>
> By following my earlier advice 
> (http://mail.lon-capa.org/pipermail/lon-capa-admin/2013-July/002710.html) 
> you would have replaced the old IDs with the new IDs in the four 
> locations -- (a) through (d) which I itemize above.
>
> However, as you will have seen any old IDs in a domain's ids.db are 
> not removed by this process, although the new IDs would have been 
> written to the file.
>
> The reason why the key => value pairs for the old IDs remain is that 
> the original implementation of ids.db in lonnet.pm rev 1.70 (November 
> 2000) -- devised as a quick look-up of the username, given a 
> student/employee ID -- included a routine to add new key => value 
> pairs, or overwrite the value for an existing key (&idput subroutine 
> in /home/httpd/lib/perl/Apache) but did not provide a mechanism to 
> delete a specific key.
>
> The presence of old id => username pairs in ids.db does not impact the 
> operation of LON-CAPA, because typically look-up is being done based 
> on a student's current ID, in the three situation where ids.db is 
> consulted, and the CSV upload mechanism included in my previous post 
> to this list will have taken care of adding ID => username records for 
> the new student IDs.
>
> In your specific case ...
>
>> For security reasons we must replace the student/employee ID for all 
>> of the existing student records in our LonCapa database
>
> the presence of old IDs could be an issue, if the requirement is to 
> remove all trace of the old student/employee IDs from your LON-CAPA 
> instance.
>
> Anyway, I have now added the necessary routines to handle deletion of 
> unwanted keys from ids.db. The student/employee ID in a user's 
> environment.db file is considered definitive.  One aspect to be aware 
> of is that IDs in ids.db are always lower-cased.
>
> Checking for unwanted keys in ids.db (and their removal) will be 
> performed every other day by searchcat.pl (run as a cron process).
>
> These changes will be included in the forthcoming LON-CAPA 2.11.0 
> release.
>
> If you do not wish to wait for that I can send you a script (offlist) 
> which you can use to remove the unwanted keys from 
> /home/httpd/lonUsers/ids.db
>
> The one other location where you may find the old IDs is within 
> classlist.db files for courses with expired student roles.
>
> Note: the procedure described in my previous reply to you -- 
> http://mail.lon-capa.org/pipermail/lon-capa-admin/2013-July/002710.html -- 
> will have updated old IDs in classlist.db files for courses where a 
> student's role was either currently active or would be active in the 
> future.
>
>> Can I make changes to these files (most notably the .db files) without
>> crashing LonCapa?  What would be the best way to do that?
>
> I can provide a script (offline) which you can run from the command 
> line to modify the ids.db file, and also any classlist.db files 
> containing old IDs.  However, in general I would only recommend 
> modifying LON-CAPA .db files using scripts provided as part of the 
> standard LON-CAPA installation, or via the LON-CAPA web GUI.
>
> Stuart Raeburn
> LON-CAPA Academic Consortium
>
>
> Quoting Jeffrey Wilson <jwilson at sc.edu>:
>
>> Hi Stuart,
>>
>> Just some more info and more questions.
>>
>> I have looked at some individual user directories to see where the
>> offending IDs might appear, for example in
>> /home/httpd/lonUsers/sc/w/i/l/wilsonteststudent I find:
>>
>> activity.log
>> email_status.db
>> email_status.db.lock
>> email_status.db.old
>> email_status.hist
>> environment.db
>> environment.db.lock
>> environment.db.old
>> environment.hist
>> nohist_accesscount.db
>> nohist_accesscount.db.lock
>> nohist_accesscount.db.old
>> nohist_calculatedsheets_sc_4m8323115285b4c74sca2.db
>> nohist_calculatedsheets_sc_4m8323115285b4c74sca2.db.lock
>> nohist_calculatedsheets_sc_4m8323115285b4c74sca2.db.old
>> nohist_resevaldata.db
>> nohist_resevaldata.db.lock
>> nohist_resevaldata.db.old
>> nohist_sc_4m8323115285b4c74sca2_discuss.db
>> nohist_sc_4m8323115285b4c74sca2_discuss.db.lock
>> nohist_sc_4m8323115285b4c74sca2_discuss.db.old
>> passwd
>> roles.db
>> roles.db.lock
>> roles.db.old
>> roles.hist
>> sc_4m8323115285b4c74sca2.db
>> sc_4m8323115285b4c74sca2.db.lock
>> sc_4m8323115285b4c74sca2.db.old
>> sc_4m8323115285b4c74sca2.hist
>>
>> I can see ID information in the environment files.  I don't see IDs
>> anywhere else.
>>
>> Can I make changes to these files (most notably the .db files) without
>> crashing LonCapa?  What would be the best way to do that?
>>
>> Thanks,
>> Jeff
>>
>>
>>
>> _______________________________________________
>> LON-CAPA-admin mailing list
>> LON-CAPA-admin at mail.lon-capa.org
>> http://mail.lon-capa.org/mailman/listinfo/lon-capa-admin
>
>
> _______________________________________________
> LON-CAPA-admin mailing list
> LON-CAPA-admin at mail.lon-capa.org
> http://mail.lon-capa.org/mailman/listinfo/lon-capa-admin