Forum .LRN Q&A: Bulk User Account problem

Collapse
Posted by Matthew Coupe on
Hello all!!
I've been trying to sort this problem out for a while now going off the different fixes I've seen in the past but I cannot seem to figure it out.
When we try to use a CSV file to bulk create some new accounts we get the following error message.

Bulk uploading in progress.

The database choked while trying to create the last user in the list above!
The transaction has been aborted, no users have been entered, and no e-mail notifications have been sent.

And no accounts are created. This is a really big problem for us! We are on .LRN version 2.1.1. If this is a known bug with this release can someone point me in the direction of the fix!

Thanks in advance
Matthew

Collapse
Posted by Matthew Burke on
Matthew,

I've run into this problem a couple of times before (and fixed it a couple of times before :) I thought it was fixed for 2.1.1, so one place to start is to double-check your CSV file and make sure it's formatted correctly.

As best I can recall (and I can't check on it right at the moment) the problem _was_ a mismatch between the parameters being sent in users-bulk-upload-2.tcl and the parameters expected in the user creation routines.

If this doesn't get you over the hurdle, post again and I'll look into it in more detail. Also, what db are you using? Oracle or Postgresql?

Matt

Collapse
Posted by Matthew Coupe on
Thanks Matt,

We're on Postgresql. I've had a look at the users-bulk-upload-2.tcl and can see what's going on there. I'm not sure where to find the user creation routines but I'm sure I can follow it through.

I'll have another look when I get a minute and if I still can't figure it out then I'll let you know.
Thanks for the help,
Matthew

Collapse
Posted by Matthew Coupe on
OK, I haven't been able to sort out this problem and would really love some advice. I've had a look at the users-bulk-upload-2.tcl file and the important bit is

set user_id [db_nextval acs_object_id_seq]

auth::create_user \
-user_id $user_id \
-username "$row(username)" \
-email $row(email) \
-first_names $row(first_names) \
-last_name $row(last_name) \
-password $password

I have seen in some bug fixes that it should actually be changed to this instead...

set user_id [ad_user_new $row(email) $row(first_names) $row(last_name) $password "" "" "" "t" "approved"]

Anyone know anything about this and how it should read?

Thanks,
Matthew

Collapse
Posted by Malte Sussdorff on
Okay, the auth::create_user is correct and the way to go.

What I would check though is to write out all variables like user_id, $row(username) and so on into your logfile using ns_log Notice to make sure your CSV file is correct and all data is present. Furthermore make sure that you CSV file does not contain extra "," where they don't belong.

Collapse
Posted by Matthew Coupe on
Thanks for getting back Malte.
I have tried it with the following CSV files:

username,password,first_names,last_name,email,id,type,access_level,guest,notify
joestue,4jfe3,Joe,Student,mailto:joe@_somewhere_.net,1234567890,student,full,f,f

email,first_names,last_name,username
mailto:bob@_blabla_.com,matty,bob,foobar

username,password,first_names,last_name,email,id,type,access_level,guest,notify
joestue,4jfe3,Joe,Student,mailto:joe@_somewhere_.net,123-456-7890,student,full,f,f
al1stein,emc2,Albert,Einstein,mailto:al@_school_.edu,al,professor,full,f,t
syshack,,Systems,Hacker,mailto:syshacker@_company_.com,,admin,,,,
tester,test,Intersted,Onlooker,mailto:onlooker@_somewhere_.net,,external,limited,t,t

I have searched through them with a toothpick and couldn't find anything wrong with any of them. I may be really stupid and have missed something REALLY obvious out of this file...

I'll try the ns_log now and see what I can get. I hope I can get this fixed because we've got 20,000 accounts to create in the next month!

Matthew

Collapse
Posted by Matthew Coupe on
by the way, in my auth::create_user

auth::create_user \
-user_id $user_id \
-username "$row(username)" \
-email $row(email) \
-first_names $row(first_names) \
-last_name $row(last_name) \
-password $password

Is there a reason for the username row being in "" and the others not?

Collapse
Posted by Malte Sussdorff on
I assume that you do not have spaces in your e-mail address CSV, otherwise this would be the first thing to do "regsub -all { } $email {} email".

Furthermore make absolutely sure that there is no blank line or anything like this in your CSV file at the end.

And last but not least, think about getting rid of the db_transaction and just log (instead of stop) on error, so you know which users failed to get inserted.

Collapse
Posted by Matthew Coupe on
Hello,

Ok, I've added in some ns_log notices to see what's going on. The info is being printed out from the first 5 notices and it reading from the csv files I prepared, however, concerningly the 'ns_log "It has gotten past the required data check"' part does not appear in the error log at all and by my reckoning it should. Would you agree?

Matthew

db_transaction {

oacs_util::csv_foreach -file $file_location -array_name row {

ns_log notice "it is reading the file"
ns_log notice $row(email)
ns_log notice $row(first_names)
ns_log notice $row(last_name)
ns_log notice $row(username)

# First make sure the required data is there
if { ![info exists row(email)] || ![info exists row(first_names)] || ![info exists row(last_name) || ![info exists row(username)]] } {
doc_body_append "
Datafile must include at least the email, first_names, last_name, username fields
"
db_abort_transaction
return
}

ns_log notice "it has gotten past the required data check"

# We need to insert the ACS user
if {![info exists row(password)]} {

# We need to insert the ACS user
set password [ad_generate_random_string]
} else {

set password $row(password)
}
.
.
.
.

Collapse
Posted by Matthew Coupe on
FIXED IT!!

OK, there is a bug in the code which was stopping it from working. The square brackets after [info exists row(last_name) are missing and there are 2 closing square brackets after ![info exists row(username)]] and so I closed them both off with 1 bracket and the upload went through and I was able to add the users to groups.

If someone could check this out then perhaps it should be patched or something? I haven't done that before and so I don't know how it works.

Regards,
Matthew

Original code:

oacs_util::csv_foreach -file $file_location -array_name row {

# First make sure the required data is there
if { ![info exists row(email)] || ![info exists row(first_names)] || ![info exists row(last_name) || ![info exists row(username)]] } {
doc_body_append "
Datafile must include at least the email, first_names, last_name, username fields
"
db_abort_transaction
return
}

Changed code:

oacs_util::csv_foreach -file $file_location -array_name row {

# First make sure the required data is there
if { ![info exists row(email)] || ![info exists row(first_names)] || ![info exists row(last_name)] || ![info exists row(username)] } {
doc_body_append "
Datafile must include at least the email, first_names, last_name, username fields
"
db_abort_transaction
return
}

Collapse
Posted by Nima Mazloumi on
can you commit this to cvs?