Best practice forum (Archived) - This forum discussion has been removed

Hi John,

I don't think there's any good reason why this should be happening. Could you turn on debugging to see if you get a more detailed error message than just "Error writing to database"? That will help us to track down what is going wrong.

Also what is the underlying database here as that may possibly be a factor - MySQL, Postgres, MSSQL?

Permalink | Show parent | Reply

Hi,

I am in the process of migrating a lot of data from one system to another and am running into the same problem here.

I have 4 files with a total of around 22.000 rows. I really don't want to have to split them up into 110 files....

Running MySQL and Totara 2.5.3 (waiting to update because a lot of migration is going on at the moment).

At first I thought the names were too long but I took the longest rows and imported them seperateley in smaller files without problems.

I have set the site to full debugging and got the Error message in the attached Doc.

Strange that it seems to say the id is to long as the longest ids went in without a problem seperately...

Best regards,

Martin Sandberg

Error.docx

Permalink | Show parent | Reply

Just tried the same locally with Totara 2.5.8 and MySQL. The same problem....

Permalink | Show parent | Reply

OK.... further testing....

By manipulating the file so that I had 6889 rows where name and id for the courses ranged from a_1 to a_6889 it actually imported correctly in my local installation.

So... what are the criteria for the name and id respectivley? How long can they be and which characters are allowed? Don't think I have seen this anywhere...

BR Martin

Permalink | Show parent | Reply

I actually suspect that error message is very misleading, there's nothing in your data that would violate the size of those fields, most of them are varchar(255)

It may be something like the packet size error on mySQL:

http://stackoverflow.com/questions/93128/mysql-error-1153-got-a-packet-bigger-than-max-allowed-packet-bytes

Could you try tweaking the mySQL config on your local install and see if that helps?

Permalink | Show parent | Reply

Thanks for your reply Ciaran,

This would actually explain why the long names got in with a smaller filer and the longer file got in with the shorter names.

Will have to look into this as soon as I get the time.If the fields are varchar(255) then none of the ids or names would be too long.

Best regards,

Martin

Permalink | Show parent | Reply

We have a slightly different issue. We can't get it to accept our csv files. We are testing with only one learner/course record so volume isn't an issue. We're using 2.4.

Under the Site Admin>User>Accounts>Upload RoL function, we've followed the Upload RoL help icon's guidance and have tried to upload a csv Excel file with the following heading fields in one cell separated by a comma (no space) and learner detail in cell below also separated by a comma (no space).

User ID,User Last Name,User First Name,Course Number,Course Name,Completed

CHolswicHolswick,Christian,OJ11,1.1 Welcome to your Orientation Journey,3/02/2014 11:19 WFT

We've then tried the same data but with each field in its own cell. In both instances we get an error saying there is "data or a field missing". The link to the moodle page only gives the same information.

Any ideas anyone?

Thanks

Fiona

Permalink | Show parent | Reply

If you are using a spreadsheet to create file and saving as csv file then

the first row of the csv file needs to contain the field name spelt as provided username,courseshortname,courseidnumber,completiondate,grade

Each field goes in a new column - when saving as csv the , are added between each column

I have attached a sample of this.

grade import.csv

Permalink | Show parent | Reply

The content of this forum post has been removed and can no longer be accessed.

Permalink | Show parent | Reply

Hi Remmert

We've tried but it still hasn't worked. Have also seen examples where it should be all in one column, separated by a comma and no spaces. We've tried that too and still no luck.

Any suggestions?

Thanks

Fiona

Permalink | Show parent | Reply

The content of this forum post has been removed and can no longer be accessed.

Permalink | Show parent | Reply

The content of this forum post has been removed and can no longer be accessed.

Permalink | Show parent | Reply

Hi Remmert

Hmmm. Not sure what I'm doing wrong but still no luck. I've updated my column headings, added additional ones in, deleted them all one by one and uploaded each time. Still the same error: 'error/required column missing'. I know this has been done before here but not in this version (we're currently on 2.4).

Here are my new headings that I've tested. Can you spot anything not quite right?

username	lastname	firstname	courseshortname	courseidnumber	coursename	completiondate	grade
cholswic	Holswick	Christian	Orientation Journey 1.1	OJ11	1.1 Welcome to your Orientation Journey	3/02/2014 11:19 WFT	0

I've tested this in our testing site, not the live site. Is that a possible cause? (ie: can't upload files into testing or something?).

Thanks

Fiona

Permalink | Show parent | Reply

Hi, Fiona

I performed a quick test on this one. It appears that your completiondate column isn't formatted correctly. Ensure that the completiondate format matches the date format you've chosen on the import screen e.g. if mm/dd/yyyy, then the completiondate would be 03/02/2014 (remove everything after the date i.e. " 11:19 WFT").

Also try removing the firstname and lastname columns. If the user exists in the system already, the username will match the user.

The column coursename isn't required either. If a course already exists in the system, the historical data for the user will be imported and the user enrolled onto the existing course. If the course doesn't exist, then the data imported will be recorded as evidence. The course is identified by courseshortname

Let me know how you get on.

Cheers

Tom

Permalink | Show parent | Reply

Hi Tom

Still no luck. I'm trying to confirm the exact language strings used in the user profile but both the profile setting screen and reports show the proper term as opposed to the string. Eg: ID number or User name instead of useridnumber. Do you know where they are found? I've tried looking in language string for the correct email address string that someone else suggested I try but can't find the user profile string php.

Any ideas on what to try next?

Thanks for your help.

Fiona

Permalink | Show parent | Reply

Hi Fiona

I've got some time this morning, would you like me to visit? (ph 890-2353)

Cheers
Austen

Permalink | Show parent | Reply

Hi guys,

This still an issue?

cheers,

George.

Permalink | Show parent | Reply

Hi folks,

There is some guidance here about 26 minutes in, but I must point out this is in 2.5, its not possible to do it in 2.4.

regards,

George.

Permalink | Show parent | Reply

Hi folks, this one seems to have been going on for a while. When reviewing it I saw this from Fiona earlier in the thread

"We're using 2.4. Under the Site Admin>User>Accounts>Upload RoL function, we've followed the Upload RoL help icon's guidance"

The course completion upload functionality was introduced in Totara 2.5 under Site Admin -> Courses -> Upload completion records, and in a normal Totara 2.4 there is no Site Admin>User>Accounts>Upload RoL function, so I'm wondering if this is either a custom addition to Fiona's setup, or an attempted backport of the 2.5 code?

Permalink | Show parent | Reply

Yes, still an issue. We're working with our providers for a resolution. They think it might be a bug in the coding. Hoping for a solution shortly. Thanks for following up George.

Permalink | Show parent | Reply

Has this issue been resolved/ I am still having trouble with Completion Uploads in 2.5.8

Permalink | Show parent | Reply

Hi Kimberly,

There were a variety of bug fixes and improvements to historical course completion uploads in 2.5.9, 2.5.10 and 2.5.12 so you would be best to upgrade to the latest 2.5.12.1 - or 2.5.13 which should be released tomorrow.

Permalink | Show parent | Reply

Sometime in the next day or two I will be submitting a topic in these forums in regards to some enhancements we need to have in the course completion import. One of those things is the ability to import records on the backend, in a batch via CLI. I haven't looked at it yet to see if that functionality already exists. I think that importing any sizable number of completion records will be too prone to error and web server / php timeouts when executing through the user interface. Not that such a feature would necessarily solve this exact problem, but it is one step forward in scaling the course completions import. It would seem that other tuning is also needed for large numbers of records. By large numbers, I mean hundreds of thousands.

Permalink | Show parent | Reply

I've been working on importing completion records and have some scenarios to share.

Not sure I agree with all of the logic but this is the way I have found the importing of completion records to work.

Permalink | Show parent | Reply

Hi Joe,

Nice flowchart!

On your point #4 the logic for the "override" checkbox works as follows:

If the course already has a completion date and you upload a file with a different completion date:

If the date in the upload file is older than the existing completion date the uploaded date will be entered into the completion history

If it is newer than the existing completion date then

a) if the override checkbox is checked, then the completion date will be REPLACED with the uploaded date

b) if the override checkbox is unchecked, the uploaded date will be ignored.

So the status would not change, but the completion date might, if the uploaded date is newer than the existing date.

Permalink | Show parent | Reply

Ciaran,

Sorry for the delayed response. I've updated my document to reflect this information. I still think that #7 and #9 should have the same behavior. Do you agree?

Thanks,

Joe

Permalink | Show parent | Reply

Well on #9 if override is off, then the uploaded completion date would either be entered into completion history or ignored, neither of which should really change the current status?

Permalink | Show parent | Reply

I think that #9 and #7 should behave the same ?

Permalink | Show parent | Reply

Well no, they are subtly different cases.

In #7 there is no existing course completion record. Then you upload a course completion, so it creates one. If you didn't want it to do that, why would you upload course completion records to courses with no status in the first place? "Override existing" here will have no practical effect because there are no existing records to consider.

In #9 there is an existing completion record, and you upload another, but tell it not to override any existing record, so the new uploaded completion date will either go into history or be ignored, hence not changing the status.

Permalink | Show parent | Reply

In #7, there is no completion record. So, when running a global completions report, the user doesn't show up (which is not a good situation for us - so always checking "Completion tracking begins on enrollment" is advisable, which would be #9). So a status of "No Status" is perhaps not a realistic scenario.

Permalink | Show parent | Reply

I have a question about the course enrollment date for a uploaded completion record. How does Totara determine the enrollment date?

For example, I uploaded a completion record on June 6, 2014. The record itself have a completiondate of 1/21/2014. I believe the process is that user is first enrolled in the course before the completion record is applied. For this example, here is the enrollment information:

The enrollment date is Feb 21, 2000. How does Totara determine the enrollment date?

Thanks,
Joe

Permalink | Show parent | Reply

Hi Joe

I had a look though and the enrolments are made in function import_course in totara/completionimport/lib.php - there is a bug as the arguments to mktime() around line 640 are in the wrong order which is why the enrolment date looks odd - it should be the time of the import. I'll file a bug and get that fixed.

In future though bug reports and support queries should go through the ticketed official support Helpdesk system.

Permalink | Show parent | Reply

Hi Joe,

A fix for this issue has been included in Totara 2.6.13 and it will be included in the next 2.5 release.

Please let us know if you have any further issues.

Regards,

Totara Product Support

Permalink | Show parent | Reply

Just reading this now.

>>enrolment date - it should be the time of the import

I think the enrolment date should either be the completion date or be null. Otherwise the enrolment date going to be greater than the completion date - which doesn't make sense. For an rpl, we really don't know the enrolment date.

Permalink | Show parent | Reply