Uploaded image for project: '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
    • Status: Closed
    • Priority: 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

      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.

        Gliffy Diagrams

          Issue Links

            Activity

            Hide
            rajeshtaneja Rajesh Taneja added a comment -

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

            Show
            rajeshtaneja Rajesh Taneja added a comment - Assigning this to Mark as he has recently worked on similar issue.
            Hide
            markn 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
            markn 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
            markn 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
            markn 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
            rajeshtaneja 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
            rajeshtaneja 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
            salvetore Michael de Raadt added a comment -

            Shifting to the next sprint.

            Show
            salvetore Michael de Raadt added a comment - Shifting to the next sprint.
            Hide
            markn 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
            markn 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
            stronk7 Eloy Lafuente (stronk7) added a comment -

            Integrated, thanks!

            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - Integrated, thanks!
            Hide
            andyjdavis 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
            andyjdavis 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
            rajeshtaneja 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
            rajeshtaneja 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
            andyjdavis 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
            andyjdavis 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
            poltawski Dan Poltawski added a comment -

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

            Show
            poltawski Dan Poltawski added a comment - I think the thing you need for this is in posgres.conf: standard_conforming_strings = off
            Hide
            poltawski 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
            poltawski 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
            stronk7 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
            stronk7 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
            simoncoggins 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
            simoncoggins 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:
                  Fix Release Date:
                  11/Mar/13