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

XMLWordPrintable

    • Icon: Sub-task Sub-task
    • Resolution: Fixed
    • Icon: Major Major
    • 2.2.8
    • 2.1.10, 2.2.7
    • Installation
    • MOODLE_21_STABLE, MOODLE_22_STABLE
    • MOODLE_22_STABLE
    • 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)

      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.

            markn Mark Nelson
            rajeshtaneja Rajesh Taneja
            Rajesh Taneja Rajesh Taneja
            Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
            Dan Poltawski Dan Poltawski
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.