Moodle 2.0 to 2.0.1+ upgrade error on SCORM

Hi Fred,

1. which DB driver are you using? mssql or sqlsrv ?
2. Does the process halt always in the same exact place?
3. Have you tried with latest Moodle 2.0.1+ available? Or is it an older one (see version.php - build)

I've tried here with the mssql driver, upgrading from 2.0rc4 to 2.0.1+ and worked ok, for reference.

Ciao

I just tested and I am unable to reproduce this problem. At my setup, I have MSSQL 2008 R2 Express. PHP connects via sqlsrv driver (php_sqlsrv_52_ts_vc6.dll). I installed Moodle 2.0 which created the column "forcecompleted" as BIGINT. Then I upgraded to the most recent 2.0.1+ without any problems. After the upgrade, the field was correctly altered to smallint.

Note that the mdl_scorm table was actually empty in my case. I suggest that Fred checks what values they have in the column before they run the upgrade.

As far as we have completed successfully the upgrade both with the mssql and the sqlsrv drivers, I'm lowering this to major, awaiting some time for more information to arrive instead of closing it as "cannot reproduce".

Ciao

Additional Information;
SQL Server 2008 (full version not express 10.0.2351); FreeTDS 0.82 + 20090904 patches (from moodle.org); IIS 7.5.7600; PHP 5.3.3;
Windows Server 2008 R2 64-bit

I have upgraded our moodle from 1.9.10 to Moodle 2.0 then to Moodle 2.0.1 I tried earlier version of Moodle 2.0.1 and then last weeks update both with the same result.

I have worked around the problem by deleting the field forcecompleted and recreating it as SMALLINT, NOT NULL with a default value of '0'. The same problem occured with 3 other fields in the mdl_scorm table, forcenewattempt, displayattemptstatus & displaycoursestructure.
Before updating the table mdl_scorm I also deleted all content from it.

This is a test server which I was trialling the upgrade process on prior to upgrading our live server so I could make the above changes but I would not be as comfortable making them on the live server.

As we have over 1700 courses and 1300 staff and there is no backup/restore process from Moodle 1.9.10 to Moodle 2.0 and from my reading not likely to be I am going to have to go down the upgrade process so would like to be confident it works reliably.

Thanks for your help.

Hi Fred,

and... instead of deleting (dropping) the column and creating it... can you try to execute:

"ALTER TABLE mdl_scorm ALTER COLUMN forcecompleted SMALLINT NOT NULL"

In the management studio or so? Exactly after running the upgrade again and getting the "terminated error. Just to see if that reveals some other (real) problem or if, instead, it runs ok.

Also I think it could be really interesting to enable the server profiler or so before starting the upgrade to see if it shows more information when the statement above is executed.

TIA and ciao

First I had to disable the Prevent Saving Changes that require table re-creation option.

Go to Tools | Options | Designers | Tables and Designers and uncheck the Prevent Saving Changes that require table re-creation option

Then when I tried the Alter Table command I got the following error;

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'forcecompleted', table 'Moodle.dbo.mdl_scorm'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

Tried adding a default value in many different ways but couldn't find the right syntax.

One suggested answer was: You have NULLs in the column and this is why the ALTER statement fails. You have to update the column first to change the NULLs to some other value and then execute the ALTER statement.

I checked and there were nulls in the field.

I then tried
UPDATE mdl_scorm set forcecompleted = 0 which completed successfully

Then ran

ALTER TABLE mdl_scorm ALTER COLUMN forcecompleted SMALLINT NOT NULL

Finally it worked.

Thanks for the info Fred, that reveals some interesting things!

Since 2008 and up to Jan 19th 2011, the fields have stay defined in upgrade.php (for sites upgrading from 1.9.x) as nullable with default = 0. Sites that installed 2.0dev have had not null default = 1 all the time.

So I guess your site is one site upgraded from 1.9.x to 2.0 before Jan 19th, is that correct?

In the mean time, you ended with some records having null in that column and that was the ultimate reason for the the upgrade after 19th (to 2.0.1) to fail.

So, IMO, the solution here is about to execute the corresponding updates before the alter tables, exactly like you've done manually.

The question is... which default should we be using to fill the nulls? 0 or 1 ? It sounds to me that the proper default to be applied is the 1 (the currently defined one). But, in the other side, the default used to be 0 based on: http://cvs.moodle.org/moodle/mod/scorm/db/upgrade.php?r1=1.45&r2=1.46

So, I'm adding Dan Marsen here hoping he can drop some comments about which default to apply, because I can imagine a lot of sites coming from 1.9.x breaking because of this. Can you tell us if the "update nulls before alter column" is ok, Dan, with which value? To which fields?

Ciao

PS: Also, related to the DB in general... I will research if it's possible to get the "Cannot insert the value NULL into column 'forcecompleted', table 'Moodle.dbo.mdl_scorm'; column does not allow nulls. UPDATE fails" message error from the PHP driver, because the "The statement has been terminated." one is really non-meaningful.

Raising this back to blocker because it is a real and with negative effects on any 1.9 => 2.0 => 2.1 upgrade having SCORM activities.

One more comment:

1) My upgrade above surely worked because I had not any SCORM in my site, hence, no nulls.
2) David's upgrade above worked because he installed 2.0 from scratch and install.xml were correct NOT NULLs already and, perhaps, because he didn't have any SCORM either.

So, After having one patch... I'll retry my upgrade ensuring that I've some "offending scorm" with nulls and so on.

Re-ciao

Hi Eloy, default should be 1 - there was a discrepancy between upgrade.php and install.xml (I've attached the related bug that probably caused this)

Yes, yes that bug (MDL-25792) was the one causing the problem while trying to fix inconsistencies. So all I'm proposing is to add some

UPDATE mdl_scorm SET xxxx = yy WHERE xxxx IS NULL"

just asking which ones are the correct (xxxx, yy) pairs to use in those updates. From your reply I assume that the ones @ install.xml are the correct to apply, yes?

I'll post link to patch here for you to take a look before pull request, sounds ok Dan? Also that will allow Fred to confirm if that fixes the problem on his site.

Ciao

Oki, so, based on install.xml information, I'm going to modify upgrade to perform one update of nulls to these:

• scorm->forcecompleted = 1
• scorm->forcenewattempt = 0
• scorm->lastattemptlock = 0
• scorm->displayattemptstatus = 1
• scorm->displaycoursestructure = 1

I've created one pull request about this, hopefully will be integrated before 2.0.2 release.

Take a look to PULL-323 for more information and also to see the changes in case you can test it in your site.

Ciao

Eloy's PULL request just passed testing, closing. Thanks all!