Moodle
  1. Moodle
  2. MDL-26361

Moodle 2.0 to 2.0.1+ upgrade error on SCORM

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.0.1
    • Fix Version/s: 2.0.2
    • Labels:
    • Environment:
      MS-SQL 2008, Windows 2008, IIS 7.5
    • Rank:
      16491

      Description

      When trying to upgrad from moodle 2.0 to moodle 2.0.1 I get the error below. I cannot continue with the upgrade it just sends me back to the Plugins Check Page.

      DDL sql execution error

      More information about this error

      Debug info: The statement has been terminated.
      ALTER TABLE mdl_scorm ALTER COLUMN forcecompleted SMALLINT NOT NULL
      Stack trace:
      line 397 of \lib\dml\moodle_database.php: ddl_change_structure_exception thrown
      line 255 of \lib\dml\mssql_native_moodle_database.php: call to moodle_database->query_end()
      line 604 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end()
      line 88 of \lib\ddl\database_manager.php: call to mssql_native_moodle_database->change_database_structure()
      line 75 of \lib\ddl\database_manager.php: call to database_manager->execute_sql()
      line 636 of \lib\ddl\database_manager.php: call to database_manager->execute_sql_arr()
      line 648 of \lib\ddl\database_manager.php: call to database_manager->change_field_type()
      line 491 of \mod\scorm\db\upgrade.php: call to database_manager->change_field_precision()
      line 526 of \lib\upgradelib.php: call to xmldb_scorm_upgrade()
      line 265 of \lib\upgradelib.php: call to upgrade_plugins_modules()
      line 1426 of \lib\upgradelib.php: call to upgrade_plugins()
      line 290 of \admin\index.php: call to upgrade_noncore()

        Issue Links

          Activity

          Hide
          Eloy Lafuente (stronk7) added a comment - - edited

          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

          Show
          Eloy Lafuente (stronk7) added a comment - - edited Hi Fred, which DB driver are you using? mssql or sqlsrv ? Does the process halt always in the same exact place? 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
          Hide
          David Mudrak added a comment -

          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.

          Show
          David Mudrak added a comment - 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.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          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

          Show
          Eloy Lafuente (stronk7) added a comment - 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
          Hide
          Fred Weiss added a comment - - edited

          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.

          Show
          Fred Weiss added a comment - - edited 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.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          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

          Show
          Eloy Lafuente (stronk7) added a comment - 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
          Hide
          Fred Weiss added a comment -

          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.

          Show
          Fred Weiss added a comment - 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.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          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.

          Show
          Eloy Lafuente (stronk7) added a comment - 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.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          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.

          Show
          Eloy Lafuente (stronk7) added a comment - 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.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          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

          Show
          Eloy Lafuente (stronk7) added a comment - 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
          Hide
          Dan Marsden added a comment -

          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)

          Show
          Dan Marsden added a comment - 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)
          Hide
          Eloy Lafuente (stronk7) added a comment -

          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

          Show
          Eloy Lafuente (stronk7) added a comment - 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
          Hide
          Eloy Lafuente (stronk7) added a comment -

          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
          Show
          Eloy Lafuente (stronk7) added a comment - 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
          Hide
          Eloy Lafuente (stronk7) added a comment -

          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

          Show
          Eloy Lafuente (stronk7) added a comment - 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
          Hide
          David Mudrak added a comment -

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

          Show
          David Mudrak added a comment - Eloy's PULL request just passed testing, closing. Thanks all!

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: