Moodle
  1. Moodle
  2. MDL-34086 META: Increase robustness of upgrade from 1.9 to 2.2
  3. MDL-38003

Sites installed with Moodle version < 1.7 using Postgres have null database columns

    Details

    • Type: Sub-task Sub-task
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.1.10, 2.2.7
    • Fix Version/s: 2.2.8
    • Component/s: Installation
    • Labels:
    • Testing Instructions:
      Hide

      1. Install Moodle 1.6 using Postgres.

      I had some issues here with getting Moodle's installer to generate the config.php as it could not connect to the database. I ended up having to create the file myself and specify the port. Eg.

      $CFG->dbtype    = 'postgres7'; // mysql or postgres7 (for now)
      $CFG->dbhost    = 'localhost'; // eg localhost or db.isp.com
      $CFG->dbname    = 'name'; // database name, eg moodle
      $CFG->dbuser    = 'user'; // your database username
      $CFG->dbpass    = 'pass'; // your database password
      $CFG->dbport    = '5432';
      $CFG->prefix    = 'mdl_'; // Prefix to use for all table names
      
      $CFG->dbpersist = false; // Should database connections be reused?
      

      Remove all references of insertion to logs (eg. INSERT INTO prefix_log_display (module, action, mtable, field) VALUES ('forum', 'add', 'forum', 'name')) from lib/db/postgres7.sql and mod/forum/db/postgres7.sql before the upgrade as they will cause database errors. This is an issue in 1.6/1.7 so not reporting.

      2. Upgrade to 1.9.
      3. Visit a course and add a file resource.
      4. Visit the 'resource' table in your database and set the 'reference' column to NULL for the newly created entry.
      5. Perform upgrade to 2.2.
      6. Ensure you do not get the following error regarding the reference column being null.

      !!! Error writing to database !!!
      !! ERROR: null value in column "reference" violates not-null constraint
      INSERT INTO mdl_resource_old (oldid, course, name, type, reference, intro, introformat, alltext, popup, options, timemodified, cmid)
      SELECT r.id, r.course, r.name, r.type, r.reference, r.summary, 0, r.alltext, r.popup, r.options, r.timemodified, cm.id
      FROM mdl_resource r
      LEFT JOIN mdl_course_modules cm ON (r.id = cm.instance AND cm.module = $1)
      [array (
      0 => '13',
      )] !!
      !! Stack trace: * line 394 of /lib/dml/moodle_database.php: dml_write_exception thrown
      
          line 232 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
          line 596 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
          line 258 of /mod/resource/db/upgradelib.php: call to pgsql_native_moodle_database->execute()
          line 46 of /mod/folder/db/upgradelib.php: call to resource_20_prepare_migration()
          line 39 of /mod/folder/db/install.php: call to folder_20_migrate()
          line 504 of /lib/upgradelib.php: call to xmldb_folder_install()
          line 265 of /lib/upgradelib.php: call to upgrade_plugins_modules()
          line 1426 of /lib/upgradelib.php: call to upgrade_plugins()
          line 111 of /admin/cli/upgrade.php: call to upgrade_noncore()
      

      7. Ensure the following columns are set to NOT NULL.

      Table: survey
      Columns: intro, questions
      
      Table: survey_answers
      Columns: time, answer1 and answer2
      
      Table: survey_questions
      Columns: intro
      
      Table: label
      Columns: name and intro
      

      8. Compare the above database tables as well as the resource table to a fresh install of 2.2 and ensure all the attributes are the same (ie, unsigned/default/sequence)

      Show
      1. Install Moodle 1.6 using Postgres. I had some issues here with getting Moodle's installer to generate the config.php as it could not connect to the database. I ended up having to create the file myself and specify the port. Eg. $CFG->dbtype = 'postgres7'; // mysql or postgres7 (for now) $CFG->dbhost = 'localhost'; // eg localhost or db.isp.com $CFG->dbname = 'name'; // database name, eg moodle $CFG->dbuser = 'user'; // your database username $CFG->dbpass = 'pass'; // your database password $CFG->dbport = '5432'; $CFG->prefix = 'mdl_'; // Prefix to use for all table names $CFG->dbpersist = false; // Should database connections be reused? Remove all references of insertion to logs (eg. INSERT INTO prefix_log_display (module, action, mtable, field) VALUES ('forum', 'add', 'forum', 'name')) from lib/db/postgres7.sql and mod/forum/db/postgres7.sql before the upgrade as they will cause database errors. This is an issue in 1.6/1.7 so not reporting. 2. Upgrade to 1.9. 3. Visit a course and add a file resource. 4. Visit the 'resource' table in your database and set the 'reference' column to NULL for the newly created entry. 5. Perform upgrade to 2.2. 6. Ensure you do not get the following error regarding the reference column being null. !!! Error writing to database !!! !! ERROR: null value in column "reference" violates not-null constraint INSERT INTO mdl_resource_old (oldid, course, name, type, reference, intro, introformat, alltext, popup, options, timemodified, cmid) SELECT r.id, r.course, r.name, r.type, r.reference, r.summary, 0, r.alltext, r.popup, r.options, r.timemodified, cm.id FROM mdl_resource r LEFT JOIN mdl_course_modules cm ON (r.id = cm.instance AND cm.module = $1) [array ( 0 => '13', )] !! !! Stack trace: * line 394 of /lib/dml/moodle_database.php: dml_write_exception thrown line 232 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end() line 596 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end() line 258 of /mod/resource/db/upgradelib.php: call to pgsql_native_moodle_database->execute() line 46 of /mod/folder/db/upgradelib.php: call to resource_20_prepare_migration() line 39 of /mod/folder/db/install.php: call to folder_20_migrate() line 504 of /lib/upgradelib.php: call to xmldb_folder_install() line 265 of /lib/upgradelib.php: call to upgrade_plugins_modules() line 1426 of /lib/upgradelib.php: call to upgrade_plugins() line 111 of /admin/cli/upgrade.php: call to upgrade_noncore() 7. Ensure the following columns are set to NOT NULL. Table: survey Columns: intro, questions Table: survey_answers Columns: time, answer1 and answer2 Table: survey_questions Columns: intro Table: label Columns: name and intro 8. Compare the above database tables as well as the resource table to a fresh install of 2.2 and ensure all the attributes are the same (ie, unsigned/default/sequence)
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_22_STABLE
    • Rank:
      47785

      Description

      Original problem was found in user (Which has been fixed by MDL-25948) and resource.

      Upgrade to 2.0.2 fails if reference field is null in resource table.
      This is happening because reference field was default to null in mod/resource/db/postgres7.sql (In moodle 17)

      Grep 17 code shows problem can occur in survey, backup, journal, resource, label, grade_category, grade_item.

        Issue Links

          Activity

          Hide
          Rajesh Taneja added a comment -

          Assigning this to Mark as he has recently worked on similar issue.

          Show
          Rajesh Taneja added a comment - Assigning this to Mark as he has recently worked on similar issue.
          Hide
          Mark Nelson added a comment -

          Here are the list of values I found that are NULL in 1.6 installs - excluding the user table as that was addressed in MDL-25948.

          ========== backup/db/postgres7.sql and oci8po.sql ==========
          
          Table: backup_files
          Columns:
          old_id number(10) default NULL
          new_id number(10) default NULL
          
          Table: backup_ids
          Columns:
          new_id integer default NULL
          
          ========== lib/db/postgres7.sql ==========
          
          Table: grade_category
          Columns:
          name varchar(64) default NULL
          
          Table: grade_item
          Columns:
          courseid integer default NULL
          category integer default NULL
          modid integer default NULL
          cminstance integer default NULL
          
          Table: grade_preferences
          Columns:
          courseid integer default NULL
          
          ========== mod/survey/db/postgres7.sql and oci8po.sql ==========
          
          Table: survey
          Columns:
          questions varchar(255) default NULL
          
          Table: survey_answers
          Columns:
          time integer default NULL
          answer1 text default NULL
          answer2 text default NULL
          
          Table: survey_questions
          Columns:
          intro varchar(50) default NULL
          
          
          ========== mod/journal/db/postgres7.sql and oci8po.sql ==========
          
          Table: journal
          Columns:
          name varchar(255) default NULL
          
          ========= mod/choice/db/oci8po.sql ==========
          
          Table: choice
          Columns:
          answer3 varchar2(255) default NULL
          answer4 varchar2(255) default NULL
          answer5 varchar2(255) default NULL
          answer6 varchar2(255) default NULL
          
          ========= mod/label/postgres7.sql ==========
          
          Table: label
          Columns:
          name varchar(255) default NULL
          
          ========= mod/resource/db/postgres7.sql and oci8po.sql ==========
          
          Table: resource
          Columns:
          reference varchar(255) default NULL
          

          I am going to perform an upgrade to 2.2 and see what columns remain NULL.

          Show
          Mark Nelson added a comment - Here are the list of values I found that are NULL in 1.6 installs - excluding the user table as that was addressed in MDL-25948 . ========== backup/db/postgres7.sql and oci8po.sql ========== Table: backup_files Columns: old_id number(10) default NULL new_id number(10) default NULL Table: backup_ids Columns: new_id integer default NULL ========== lib/db/postgres7.sql ========== Table: grade_category Columns: name varchar(64) default NULL Table: grade_item Columns: courseid integer default NULL category integer default NULL modid integer default NULL cminstance integer default NULL Table: grade_preferences Columns: courseid integer default NULL ========== mod/survey/db/postgres7.sql and oci8po.sql ========== Table: survey Columns: questions varchar(255) default NULL Table: survey_answers Columns: time integer default NULL answer1 text default NULL answer2 text default NULL Table: survey_questions Columns: intro varchar(50) default NULL ========== mod/journal/db/postgres7.sql and oci8po.sql ========== Table: journal Columns: name varchar(255) default NULL ========= mod/choice/db/oci8po.sql ========== Table: choice Columns: answer3 varchar2(255) default NULL answer4 varchar2(255) default NULL answer5 varchar2(255) default NULL answer6 varchar2(255) default NULL ========= mod/label/postgres7.sql ========== Table: label Columns: name varchar(255) default NULL ========= mod/resource/db/postgres7.sql and oci8po.sql ========== Table: resource Columns: reference varchar(255) default NULL I am going to perform an upgrade to 2.2 and see what columns remain NULL.
          Hide
          Mark Nelson added a comment -

          The only issues I found from the list above are the following, the rest were either removed or corrected.

          Table: survey
          Columns:
          questions varchar(255) default NULL
          
          Table: survey_answers
          Columns:
          time integer default NULL
          answer1 text default NULL
          answer2 text default NULL
          
          Table: survey_questions
          Columns:
          intro varchar(50) default NULL
          
          Table: label
          Columns:
          name varchar(255) default NULL
          intro text (this one was not added in 1.6, but is null where as my fresh 2.2 install is not)
          
          
          Show
          Mark Nelson added a comment - The only issues I found from the list above are the following, the rest were either removed or corrected. Table: survey Columns: questions varchar(255) default NULL Table: survey_answers Columns: time integer default NULL answer1 text default NULL answer2 text default NULL Table: survey_questions Columns: intro varchar(50) default NULL Table: label Columns: name varchar(255) default NULL intro text (this one was not added in 1.6, but is null where as my fresh 2.2 install is not)
          Hide
          Rajesh Taneja added a comment -

          Hi! Mark,

          Can you please confirm if resource is not an issue.
          Original issue (MDL-27243) mentioned about resource table and user table. I know user table was fixed, but not sure about resource table.

          Show
          Rajesh Taneja added a comment - Hi! Mark, Can you please confirm if resource is not an issue. Original issue ( MDL-27243 ) mentioned about resource table and user table. I know user table was fixed, but not sure about resource table.
          Hide
          Michael de Raadt added a comment -

          Shifting to the next sprint.

          Show
          Michael de Raadt added a comment - Shifting to the next sprint.
          Hide
          Mark Nelson added a comment - - edited

          Hi Raj, I added a resource and set the reference column to NULL manually and performed the upgrade and was able to replicate the error users are referring to. I suspect somewhere in Moodle NULL values are being inserted. I have updated the testing instructions and added a fix to my patch. Thanks.

          Show
          Mark Nelson added a comment - - edited Hi Raj, I added a resource and set the reference column to NULL manually and performed the upgrade and was able to replicate the error users are referring to. I suspect somewhere in Moodle NULL values are being inserted. I have updated the testing instructions and added a fix to my patch. Thanks.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Integrated, thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - Integrated, thanks!
          Hide
          Andrew Davis added a comment - - edited

          "Perform upgrade to 2.2."

          Where did this get integrated to? Do you actually mean 2.2 or do you mean 2.3?

          So I get 1.6 from the downloads page. Then I get 1.9 from the downloads page. Finally I get 2.2 or maybe 2.3 from integration. Is that correct?

          Show
          Andrew Davis added a comment - - edited "Perform upgrade to 2.2." Where did this get integrated to? Do you actually mean 2.2 or do you mean 2.3? So I get 1.6 from the downloads page. Then I get 1.9 from the downloads page. Finally I get 2.2 or maybe 2.3 from integration. Is that correct?
          Hide
          Rajesh Taneja added a comment -

          Hello Andrew,

          Mark is on leave today.
          This test needs upgrade to 2.2 (Has been only integrated on 2.2)
          FYI: Upgrading to 2.3 require 2.2.x first, so this code is not required for 2.3.

          You have done fine, although you can also checkout 16 & 19 by

          git checkout origin/MOODLE_16_STABLE
          git checkout origin/MOODLE_19_STABLE
          

          @Eloy: Should this be backported to 21, as done for MDL-25948?

          Show
          Rajesh Taneja added a comment - Hello Andrew, Mark is on leave today. This test needs upgrade to 2.2 (Has been only integrated on 2.2) FYI: Upgrading to 2.3 require 2.2.x first, so this code is not required for 2.3. You have done fine, although you can also checkout 16 & 19 by git checkout origin/MOODLE_16_STABLE git checkout origin/MOODLE_19_STABLE @Eloy: Should this be backported to 21, as done for MDL-25948 ?
          Hide
          Andrew Davis added a comment -

          Im giving up for now. Mark, can you please add the entire contents of your 1.6 config.php. I'm not able to get 1.6 to connect to the database.

          Show
          Andrew Davis added a comment - Im giving up for now. Mark, can you please add the entire contents of your 1.6 config.php. I'm not able to get 1.6 to connect to the database.
          Hide
          Dan Poltawski added a comment -

          I think the thing you need for this is in posgres.conf:
          standard_conforming_strings = off

          Show
          Dan Poltawski added a comment - I think the thing you need for this is in posgres.conf: standard_conforming_strings = off
          Hide
          Dan Poltawski added a comment -

          First time I ran the upgrade I forgot to use integration.git and got the error, verifying the problem!

          I ran it again on integration and its all good.

          Great work Mark, hope this will help more people upgrade

          Show
          Dan Poltawski added a comment - First time I ran the upgrade I forgot to use integration.git and got the error, verifying the problem! I ran it again on integration and its all good. Great work Mark, hope this will help more people upgrade
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Because

          A
          MARVELOUS
          A       U
          Z  YOU  P
          I  ARE  E
          N  PPL  R
          G       B
            TNKS! 
          

          Closing, ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Because A MARVELOUS A U Z YOU P I ARE E N PPL R G B TNKS! Closing, ciao
          Hide
          Simon Coggins added a comment -

          This is causing warnings on upgrade from 2.2.7 to 2.2.8 for me, I've filed a new bug here:

          https://tracker.moodle.org/browse/MDL-38450

          Show
          Simon Coggins added a comment - This is causing warnings on upgrade from 2.2.7 to 2.2.8 for me, I've filed a new bug here: https://tracker.moodle.org/browse/MDL-38450

            People

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

              Dates

              • Created:
                Updated:
                Resolved: