Moodle
  1. Moodle
  2. MDL-25031

Upgrade process fails if pagetype null

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.0, 2.2, 2.2.10
    • Fix Version/s: 2.2.11
    • Component/s: Installation
    • Labels:
    • Database:
      MySQL
    • Testing Instructions:
      Hide

      1/ install Moodle 1.9
      2/ create courses with many blocks
      3/ enable blocks support in activities (theme settings) and add some blocks to lesson for example
      4/ use DB editor to delete source course records and lesson records
      5/ execute upgrade to 2.2.x
      6/ verify the upgrade completes without block errors and that the blocks are upgraded properly (including activity blocks)

      Show
      1/ install Moodle 1.9 2/ create courses with many blocks 3/ enable blocks support in activities (theme settings) and add some blocks to lesson for example 4/ use DB editor to delete source course records and lesson records 5/ execute upgrade to 2.2.x 6/ verify the upgrade completes without block errors and that the blocks are upgraded properly (including activity blocks)
    • Affected Branches:
      MOODLE_20_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_22_STABLE
    • Pull from Repository:
    • Rank:
      16458

      Description

      Hi Petr,

      Experienced this issue when upgrading a site from 1.9.10 -> 2.0 RC1.

      Debug info: Column 'pagetype' cannot be null

      INSERT INTO mdl_block_positions (blockinstanceid, contextid, pagetype, subpage, visible, region, weight)
      SELECT id, contextid,
      CASE WHEN pagetypepattern = 'course-view-*' THEN
      (SELECT CONCAT('course-view-', format)
      FROM mdl_course
      JOIN mdl_context ON mdl_course.id = mdl_context.instanceid
      WHERE mdl_context.id = contextid)
      ELSE pagetypepattern END,
      CASE WHEN subpagepattern IS NULL THEN ''
      ELSE subpagepattern END,
      0, defaultregion, defaultweight
      FROM mdl_block_instances WHERE visible = 0 AND pagetypepattern <> 'admin-*'
      [array (
      )]

      Stack trace:

      • line 394 of /lib/dml/moodle_database.php: dml_write_exception thrown
      • line 645 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
      • line 1858 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->execute()
      • line 1309 of /lib/upgradelib.php: call to xmldb_main_upgrade()
      • line 264 of /admin/index.php: call to upgrade_core()

        Issue Links

          Activity

          Hide
          Petr Škoda added a comment -

          Hello, how can I duplicate this problem?

          Show
          Petr Škoda added a comment - Hello, how can I duplicate this problem?
          Hide
          Petr Škoda added a comment -

          NULLs are skipped there now, please test.
          If you find out how the nulls got there please let me know.

          Thanks a lot for the report!

          Petr Skoda

          Show
          Petr Škoda added a comment - NULLs are skipped there now, please test. If you find out how the nulls got there please let me know. Thanks a lot for the report! Petr Skoda
          Hide
          Mark Nelson added a comment -

          Thanks Petr,

          Will review the database prior to the upgrade and attempt to figure out why they are null then perform an upgrade when I get some time in the week and let you know.

          Thanks

          Show
          Mark Nelson added a comment - Thanks Petr, Will review the database prior to the upgrade and attempt to figure out why they are null then perform an upgrade when I get some time in the week and let you know. Thanks
          Hide
          Mark Nelson added a comment -

          Hi Petr,

          Going to re-open this ticket as performing the same upgrade today caused the same error.

          I did some debugging and performed just the select query -

          SELECT id, contextid,
          CASE WHEN pagetypepattern = 'course-view-*' THEN
          (SELECT CONCAT('course-view-', format)
          FROM mdl_course
          JOIN mdl_context ON mdl_course.id = mdl_context.instanceid
          WHERE mdl_context.id = contextid)
          ELSE pagetypepattern END,
          CASE WHEN subpagepattern IS NULL THEN ''
          ELSE subpagepattern END,
          0, defaultregion, defaultweight FROM mdl_block_instances WHERE visible = 0 AND pagetypepattern <> 'admin-*' AND pagetypepattern IS NOT NULL

          and get the following error "Unknown column 'contextid' in 'field list'" - so nothing is returned.

          The fix so far is to comment out the execution of the whole SQL query and the setup proceeds as expected, though I am not sure if there will be some unexpected results later on.

          Show
          Mark Nelson added a comment - Hi Petr, Going to re-open this ticket as performing the same upgrade today caused the same error. I did some debugging and performed just the select query - SELECT id, contextid, CASE WHEN pagetypepattern = 'course-view-*' THEN (SELECT CONCAT('course-view-', format) FROM mdl_course JOIN mdl_context ON mdl_course.id = mdl_context.instanceid WHERE mdl_context.id = contextid) ELSE pagetypepattern END, CASE WHEN subpagepattern IS NULL THEN '' ELSE subpagepattern END, 0, defaultregion, defaultweight FROM mdl_block_instances WHERE visible = 0 AND pagetypepattern <> 'admin-*' AND pagetypepattern IS NOT NULL and get the following error "Unknown column 'contextid' in 'field list'" - so nothing is returned. The fix so far is to comment out the execution of the whole SQL query and the setup proceeds as expected, though I am not sure if there will be some unexpected results later on.
          Hide
          Mark Nelson added a comment -

          Petr,

          Came across another error:

          Debug info: Argument 2 passed to moodle_database::get_recordset() must be an array, string given, called in /home/firstaid/public_html/lib/db/upgrade.php on line 5111 and defined
          Stack trace:

          • line 333 of /lib/setuplib.php: coding_exception thrown
          • line 923 of /lib/dml/moodle_database.php: call to default_error_handler()
          • line 5111 of /lib/db/upgrade.php: call to moodle_database->get_recordset()
          • line 1309 of /lib/upgradelib.php: call to xmldb_main_upgrade()
          • line 264 of /admin/index.php: call to upgrade_core()
          Show
          Mark Nelson added a comment - Petr, Came across another error: Debug info: Argument 2 passed to moodle_database::get_recordset() must be an array, string given, called in /home/firstaid/public_html/lib/db/upgrade.php on line 5111 and defined Stack trace: line 333 of /lib/setuplib.php: coding_exception thrown line 923 of /lib/dml/moodle_database.php: call to default_error_handler() line 5111 of /lib/db/upgrade.php: call to moodle_database->get_recordset() line 1309 of /lib/upgradelib.php: call to xmldb_main_upgrade() line 264 of /admin/index.php: call to upgrade_core()
          Hide
          Mark Nelson added a comment -

          Forgot to mention, though I am sure you figured this straight away:

          Fix for error mentioned above:

          Change the line to $DB->get_recordset('question', array('questiontextformat' => FORMAT_MOODLE));

          Show
          Mark Nelson added a comment - Forgot to mention, though I am sure you figured this straight away: Fix for error mentioned above: Change the line to $DB->get_recordset('question', array('questiontextformat' => FORMAT_MOODLE));
          Hide
          Mark Nelson added a comment -

          HI Petr,

          Another bug

          Sorry, thinking I should wait before posting every bug I find during upgrade then just write one comment:

          Debug info: Unknown column 'qno' in 'on clause'

          SELECT qno.*, q.oldquestiontextformat
          FROM mdl_question_numerical_options qno
          JOIN mdl_question q ON qno = q.id
          [array (
          )]
          Stack trace:

          • line 391 of /lib/dml/moodle_database.php: dml_read_exception thrown
          • line 691 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
          • line 71 of /question/type/numerical/db/upgrade.php: call to mysqli_native_moodle_database->get_recordset_sql()
          • line 358 of /lib/upgradelib.php: call to xmldb_qtype_numerical_upgrade()
          • line 1352 of /lib/upgradelib.php: call to upgrade_plugins()
          • line 302 of /admin/index.php: call to upgrade_noncore()

          This should read

          $rs = $DB->get_recordset_sql('
          SELECT qno.*, q.oldquestiontextformat
          FROM

          {question_numerical_options}

          qno
          JOIN

          {question}

          q ON qno.question = q.id');

          Show
          Mark Nelson added a comment - HI Petr, Another bug Sorry, thinking I should wait before posting every bug I find during upgrade then just write one comment: Debug info: Unknown column 'qno' in 'on clause' SELECT qno.*, q.oldquestiontextformat FROM mdl_question_numerical_options qno JOIN mdl_question q ON qno = q.id [array ( )] Stack trace: line 391 of /lib/dml/moodle_database.php: dml_read_exception thrown line 691 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end() line 71 of /question/type/numerical/db/upgrade.php: call to mysqli_native_moodle_database->get_recordset_sql() line 358 of /lib/upgradelib.php: call to xmldb_qtype_numerical_upgrade() line 1352 of /lib/upgradelib.php: call to upgrade_plugins() line 302 of /admin/index.php: call to upgrade_noncore() This should read $rs = $DB->get_recordset_sql(' SELECT qno.*, q.oldquestiontextformat FROM {question_numerical_options} qno JOIN {question} q ON qno.question = q.id');
          Hide
          Petr Škoda added a comment -

          I have fixed all 3 problems, hopefully. Please note you need to do a fresh upgrade from 1.9.x, upgrades from most previous versions such as RC1 and RC2 are most probably borked.

          Thanks a lot!

          Show
          Petr Škoda added a comment - I have fixed all 3 problems, hopefully. Please note you need to do a fresh upgrade from 1.9.x, upgrades from most previous versions such as RC1 and RC2 are most probably borked. Thanks a lot!
          Hide
          Tim Hunt added a comment -

          Petr, I think code like

          {code sql}

          INSERT INTO

          {block_positions}

          (blockinstanceid, contextid, pagetype, subpage, visible, region, weight)
          SELECT bi.id, bi.contextid,
          CASE WHEN bi.pagetypepattern = 'course-view-*'
          THEN (SELECT " . $DB->sql_concat("'course-view-'", 'c.format') . "
          FROM

          {course}

          c
          JOIN

          {context}

          ctx ON c.id = ctx.instanceid
          WHERE ctx.id = bi.contextid)
          ELSE bi.pagetypepattern END,
          CASE WHEN bi.subpagepattern IS NULL
          THEN ''
          ELSE bi.subpagepattern END,
          0, bi.defaultregion, bi.defaultweight
          FROM

          {block_instances}

          bi
          WHERE bi.visible = 0 AND bi.pagetypepattern <> 'admin-*' AND bi.pagetypepattern IS NOT NULL");

          
          

          is dangerous. I think it breaks at least one of our supported databases to use table aliases in complex insert SQL like this. I am sure I found this out the hard way some time ago. However, I can no longer remember the details like which database it breaks on. Sorry. I guess for now we can leave it until someone reports that it really breaks. Just thought I would pass on the warning though.

          Show
          Tim Hunt added a comment - Petr, I think code like {code sql} INSERT INTO {block_positions} (blockinstanceid, contextid, pagetype, subpage, visible, region, weight) SELECT bi.id, bi.contextid, CASE WHEN bi.pagetypepattern = 'course-view-*' THEN (SELECT " . $DB->sql_concat("'course-view-'", 'c.format') . " FROM {course} c JOIN {context} ctx ON c.id = ctx.instanceid WHERE ctx.id = bi.contextid) ELSE bi.pagetypepattern END, CASE WHEN bi.subpagepattern IS NULL THEN '' ELSE bi.subpagepattern END, 0, bi.defaultregion, bi.defaultweight FROM {block_instances} bi WHERE bi.visible = 0 AND bi.pagetypepattern <> 'admin-*' AND bi.pagetypepattern IS NOT NULL"); is dangerous. I think it breaks at least one of our supported databases to use table aliases in complex insert SQL like this. I am sure I found this out the hard way some time ago. However, I can no longer remember the details like which database it breaks on. Sorry. I guess for now we can leave it until someone reports that it really breaks. Just thought I would pass on the warning though.
          Hide
          Petr Škoda added a comment -

          it used to break stuff in older Postgresql, it should not be a problem any more, in any case I am going to add a unit test for this just in case, thanks

          Show
          Petr Škoda added a comment - it used to break stuff in older Postgresql, it should not be a problem any more, in any case I am going to add a unit test for this just in case, thanks
          Hide
          Petr Škoda added a comment -

          Actually I think it was UPDATE, not INSERT INTO, anyway I am going to add tests for both.

          Show
          Petr Škoda added a comment - Actually I think it was UPDATE, not INSERT INTO, anyway I am going to add tests for both.
          Hide
          Rainer Herbst added a comment -

          Hi *,

          trying to upgrade from 1.8.13->1.9.x->2.0.2+, I stumbled over this problem, too.

          The block instance causing the failure looks like
          id blockname contextid showinsubcontexts blockid pageid pagetypepattern subpagepattern defaultregion defaultweight visible configdata
          43 participants 0 0 20 5 course-view-* NULL side-pre 0 0

          Not sure, where this row comes from, but there is no context with id=0 in mdl_context, so the concat-function (at least in MySQL) gets a NULL value and returns a NULL value.

          One possible solution is to include a check for the contextid like
          FROM

          {block_instances}

          bi join {context) ctx2 on (ctx2.id = bi.contextid)
          into the statement.

          Just for your information, the stack trace:
          !!! Error writing to database !!!
          !! Column 'pagetype' cannot be null
          INSERT INTO mdl_block_positions (blockinstanceid, contextid, pagetype, subpage, visible, region, weight)
          SELECT bi.id, bi.contextid,
          CASE WHEN bi.pagetypepattern = 'course-view-*'
          THEN (SELECT CONCAT('course-view-', c.format)
          FROM mdl_course c
          JOIN mdl_context ctx ON c.id = ctx.instanceid
          WHERE ctx.id = bi.contextid)
          ELSE bi.pagetypepattern END,
          CASE WHEN bi.subpagepattern IS NULL
          THEN ''
          ELSE bi.subpagepattern END,
          0, bi.defaultregion, bi.defaultweight
          FROM mdl_block_instances bi
          WHERE bi.visible = 0 AND bi.pagetypepattern 'admin-*' AND bi.pagetypepattern IS NOT NULL
          [array (
          )] !!
          !! Stack trace: * line 394 of /lib/dml/moodle_database.php: dml_write_exception thrown

          • line 703 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
          • line 1861 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->execute()
          • line 1383 of /lib/upgradelib.php: call to xmldb_main_upgrade()
          • line 106 of /admin/cli/upgrade.php: call to upgrade_core()
            !!
          Show
          Rainer Herbst added a comment - Hi *, trying to upgrade from 1.8.13- >1.9.x ->2.0.2+, I stumbled over this problem, too. The block instance causing the failure looks like id blockname contextid showinsubcontexts blockid pageid pagetypepattern subpagepattern defaultregion defaultweight visible configdata 43 participants 0 0 20 5 course-view-* NULL side-pre 0 0 Not sure, where this row comes from, but there is no context with id=0 in mdl_context, so the concat-function (at least in MySQL) gets a NULL value and returns a NULL value. One possible solution is to include a check for the contextid like FROM {block_instances} bi join {context) ctx2 on (ctx2.id = bi.contextid) into the statement. Just for your information, the stack trace: !!! Error writing to database !!! !! Column 'pagetype' cannot be null INSERT INTO mdl_block_positions (blockinstanceid, contextid, pagetype, subpage, visible, region, weight) SELECT bi.id, bi.contextid, CASE WHEN bi.pagetypepattern = 'course-view-*' THEN (SELECT CONCAT('course-view-', c.format) FROM mdl_course c JOIN mdl_context ctx ON c.id = ctx.instanceid WHERE ctx.id = bi.contextid) ELSE bi.pagetypepattern END, CASE WHEN bi.subpagepattern IS NULL THEN '' ELSE bi.subpagepattern END, 0, bi.defaultregion, bi.defaultweight FROM mdl_block_instances bi WHERE bi.visible = 0 AND bi.pagetypepattern 'admin-*' AND bi.pagetypepattern IS NOT NULL [array ( )] !! !! Stack trace: * line 394 of /lib/dml/moodle_database.php: dml_write_exception thrown line 703 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end() line 1861 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->execute() line 1383 of /lib/upgradelib.php: call to xmldb_main_upgrade() line 106 of /admin/cli/upgrade.php: call to upgrade_core() !!
          Hide
          Mark Nelson added a comment -

          Please review new comment by Rainer Herbst

          Show
          Mark Nelson added a comment - Please review new comment by Rainer Herbst
          Hide
          Mark Nelson added a comment -

          Rainer also sent me this information via email and asked me to re-open ticket.

          "..

          The reason for this failure is the SELECT concat... subquery, which is not sufficiently tied to the main query. I suggested a fix by adding a join to the main querie`s FROM, but the better way is to get rid of the subquery at all.

          Here is the diff:
          Index: upgrade.php
          ===================================================================
          — upgrade.php (Revision 258)
          +++ upgrade.php (Arbeitskopie)
          @@ -1846,18 +1846,17 @@
          if ($oldversion < 2009050618) {
          /// And block instances with visible = 0, copy that information to block_positions
          $DB->execute("INSERT INTO

          {block_positions}

          (blockinstanceid, contextid, pagetype, subpage, visible, region, weight)

          • SELECT bi.id, bi.contextid,
            + SELECT bi.id, bi.contextid,
            CASE WHEN bi.pagetypepattern = 'course-view-*'
          • THEN (SELECT " . $DB->sql_concat("'course-view-'", 'c.format') . "
          • FROM {course} c
            - JOIN {context} ctx ON c.id = ctx.instanceid
            - WHERE ctx.id = bi.contextid)
            + THEN " . $DB->sql_concat("'course-view-'", 'c.format') . "
            ELSE bi.pagetypepattern END,
            CASE WHEN bi.subpagepattern IS NULL
            THEN ''
            ELSE bi.subpagepattern END,
            0, bi.defaultregion, bi.defaultweight
            - FROM {block_instances} bi
            + FROM {block_instances} bi
            + JOIN {context} ctx on (bi.contextid=ctx.id)
            + JOIN {course}

            c on (c.id = ctx.instanceid)
            WHERE bi.visible = 0 AND bi.pagetypepattern <> 'admin-*' AND bi.pagetypepattern IS NOT NULL");
            // note: MDL-25031 all block instances should have a pagetype pattern, NULL is not allowed,
            // if we manage to find out how NULLs get there we should fix them before this step

          .."

          Show
          Mark Nelson added a comment - Rainer also sent me this information via email and asked me to re-open ticket. ".. The reason for this failure is the SELECT concat... subquery, which is not sufficiently tied to the main query. I suggested a fix by adding a join to the main querie`s FROM, but the better way is to get rid of the subquery at all. Here is the diff: Index: upgrade.php =================================================================== — upgrade.php (Revision 258) +++ upgrade.php (Arbeitskopie) @@ -1846,18 +1846,17 @@ if ($oldversion < 2009050618) { /// And block instances with visible = 0, copy that information to block_positions $DB->execute("INSERT INTO {block_positions} (blockinstanceid, contextid, pagetype, subpage, visible, region, weight) SELECT bi.id, bi.contextid, + SELECT bi.id, bi.contextid, CASE WHEN bi.pagetypepattern = 'course-view-*' THEN (SELECT " . $DB->sql_concat("'course-view-'", 'c.format') . " FROM {course} c - JOIN {context} ctx ON c.id = ctx.instanceid - WHERE ctx.id = bi.contextid) + THEN " . $DB->sql_concat("'course-view-'", 'c.format') . " ELSE bi.pagetypepattern END, CASE WHEN bi.subpagepattern IS NULL THEN '' ELSE bi.subpagepattern END, 0, bi.defaultregion, bi.defaultweight - FROM {block_instances} bi + FROM {block_instances} bi + JOIN {context} ctx on (bi.contextid=ctx.id) + JOIN {course} c on (c.id = ctx.instanceid) WHERE bi.visible = 0 AND bi.pagetypepattern <> 'admin-*' AND bi.pagetypepattern IS NOT NULL"); // note: MDL-25031 all block instances should have a pagetype pattern, NULL is not allowed, // if we manage to find out how NULLs get there we should fix them before this step .."
          Hide
          Petr Škoda added a comment -

          Can I please get some dump that demonstrates this? The reason is already described there:
          // note: MDL-25031 all block instances should have a pagetype pattern, NULL is not allowed,
          // if we manage to find out how NULLs get there we should fix them before this step

          I need to test it before and after, if I can not reproduce it I can not help, sorry.

          Show
          Petr Škoda added a comment - Can I please get some dump that demonstrates this? The reason is already described there: // note: MDL-25031 all block instances should have a pagetype pattern, NULL is not allowed, // if we manage to find out how NULLs get there we should fix them before this step I need to test it before and after, if I can not reproduce it I can not help, sorry.
          Hide
          Rainer Herbst added a comment -

          Hi Petr,

          what tables should be included into the dump? mdl_block_instance, mdl_context, mdl_course only? The whole DB dump is several 100MB, so it seems not reasonable to send the whole DB...

          Regards!
          Rainer

          Show
          Rainer Herbst added a comment - Hi Petr, what tables should be included into the dump? mdl_block_instance, mdl_context, mdl_course only? The whole DB dump is several 100MB, so it seems not reasonable to send the whole DB... Regards! Rainer
          Hide
          moodle.com added a comment -

          We need a clear bug to fix to do this in a sprint.

          Show
          moodle.com added a comment - We need a clear bug to fix to do this in a sprint.
          Hide
          Rainer Herbst added a comment -

          Hi *,

          have done some more investigations into the problem.

          It seems, that a block entry exists in mdl_block_instance with an invalid course number. I do think, the block entry was not deleted when the course was deleted. In this case, the SELECT statement returns NULL for the pagetype, because the course does not exists.

          Is this sufficient for you to reproduce the bug? You might simply enter an entry into mdl_block_instance with pagetype ="course-view" but with an invalid pageid and then try to migrate.

          Regards!
          Rainer

          Show
          Rainer Herbst added a comment - Hi *, have done some more investigations into the problem. It seems, that a block entry exists in mdl_block_instance with an invalid course number. I do think, the block entry was not deleted when the course was deleted. In this case, the SELECT statement returns NULL for the pagetype, because the course does not exists. Is this sufficient for you to reproduce the bug? You might simply enter an entry into mdl_block_instance with pagetype ="course-view" but with an invalid pageid and then try to migrate. Regards! Rainer
          Hide
          Ben Faulkner added a comment -

          Hi,

          I'm getting a similar error upgrading from 1.9.15 to 2.0.6+ I have also reset back to 1.9.15 and tried upgrading directly to 2.1.3+ and also 2.2+ with the same/similar result.

          Here's the error I get when upgrading to 2.0.6+

          --------------

          Debug info: Column 'pagetype' cannot be null
          INSERT INTO mdl_block_positions (blockinstanceid, contextid, pagetype, subpage, visible, region, weight)
          SELECT bi.id, bi.contextid,
          CASE WHEN bi.pagetypepattern = 'course-view-*'
          THEN (SELECT CONCAT('course-view-', c.format)
          FROM mdl_course c
          JOIN mdl_context ctx ON c.id = ctx.instanceid
          WHERE ctx.id = bi.contextid)
          ELSE bi.pagetypepattern END,
          CASE WHEN bi.subpagepattern IS NULL
          THEN ''
          ELSE bi.subpagepattern END,
          0, bi.defaultregion, bi.defaultweight
          FROM mdl_block_instances bi
          WHERE bi.visible = 0 AND bi.pagetypepattern <> 'admin-*' AND bi.pagetypepattern IS NOT NULL
          [array (
          )]

          Stack trace: •line 397 of \lib\dml\moodle_database.php: dml_write_exception thrown
          •line 722 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()
          •line 1867 of \lib\db\upgrade.php: call to mysqli_native_moodle_database->execute()
          •line 1423 of \lib\upgradelib.php: call to xmldb_main_upgrade()
          •line 277 of \admin\index.php: call to upgrade_core()

          --------------

          Thanks,

          Ben

          Show
          Ben Faulkner added a comment - Hi, I'm getting a similar error upgrading from 1.9.15 to 2.0.6+ I have also reset back to 1.9.15 and tried upgrading directly to 2.1.3+ and also 2.2+ with the same/similar result. Here's the error I get when upgrading to 2.0.6+ -------------- Debug info: Column 'pagetype' cannot be null INSERT INTO mdl_block_positions (blockinstanceid, contextid, pagetype, subpage, visible, region, weight) SELECT bi.id, bi.contextid, CASE WHEN bi.pagetypepattern = 'course-view-*' THEN (SELECT CONCAT('course-view-', c.format) FROM mdl_course c JOIN mdl_context ctx ON c.id = ctx.instanceid WHERE ctx.id = bi.contextid) ELSE bi.pagetypepattern END, CASE WHEN bi.subpagepattern IS NULL THEN '' ELSE bi.subpagepattern END, 0, bi.defaultregion, bi.defaultweight FROM mdl_block_instances bi WHERE bi.visible = 0 AND bi.pagetypepattern <> 'admin-*' AND bi.pagetypepattern IS NOT NULL [array ( )] Stack trace: •line 397 of \lib\dml\moodle_database.php: dml_write_exception thrown •line 722 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end() •line 1867 of \lib\db\upgrade.php: call to mysqli_native_moodle_database->execute() •line 1423 of \lib\upgradelib.php: call to xmldb_main_upgrade() •line 277 of \admin\index.php: call to upgrade_core() -------------- Thanks, Ben
          Hide
          David Paniagua Ruano added a comment -

          Me too Ben.

          Upgrade 1.9.15 to moodle 2.2.1+.

          Debug info: ERROR: null value in column "pagetype" violates not-null constraint
          INSERT INTO mdl_block_positions (blockinstanceid, contextid, pagetype, subpage, visible, region, weight)
          SELECT bi.id, bi.contextid,
          CASE WHEN bi.pagetypepattern = 'course-view-*'
          THEN (SELECT '' || 'course-view-' || c.format
          FROM mdl_course c
          JOIN mdl_context ctx ON c.id = ctx.instanceid
          WHERE ctx.id = bi.contextid)
          ELSE bi.pagetypepattern END,
          CASE WHEN bi.subpagepattern IS NULL
          THEN ''
          ELSE bi.subpagepattern END,
          0, bi.defaultregion, bi.defaultweight
          FROM mdl_block_instances bi
          WHERE bi.visible = 0 AND bi.pagetypepattern <> 'admin-*' AND bi.pagetypepattern IS NOT NULL
          [array (
          )]
          Stack trace:

          line 397 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 1847 of /lib/db/upgrade.php: call to pgsql_native_moodle_database->execute()
          line 1406 of /lib/upgradelib.php: call to xmldb_main_upgrade()
          line 236 of /admin/index.php: call to upgrade_core()

          Thanks,

          David

          Show
          David Paniagua Ruano added a comment - Me too Ben. Upgrade 1.9.15 to moodle 2.2.1+. Debug info: ERROR: null value in column "pagetype" violates not-null constraint INSERT INTO mdl_block_positions (blockinstanceid, contextid, pagetype, subpage, visible, region, weight) SELECT bi.id, bi.contextid, CASE WHEN bi.pagetypepattern = 'course-view-*' THEN (SELECT '' || 'course-view-' || c.format FROM mdl_course c JOIN mdl_context ctx ON c.id = ctx.instanceid WHERE ctx.id = bi.contextid) ELSE bi.pagetypepattern END, CASE WHEN bi.subpagepattern IS NULL THEN '' ELSE bi.subpagepattern END, 0, bi.defaultregion, bi.defaultweight FROM mdl_block_instances bi WHERE bi.visible = 0 AND bi.pagetypepattern <> 'admin-*' AND bi.pagetypepattern IS NOT NULL [array ( )] Stack trace: line 397 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 1847 of /lib/db/upgrade.php: call to pgsql_native_moodle_database->execute() line 1406 of /lib/upgradelib.php: call to xmldb_main_upgrade() line 236 of /admin/index.php: call to upgrade_core() Thanks, David
          Hide
          Ben Faulkner added a comment -

          Hi David,

          Don't take this the wrong way but I'm glad to see that someone else has exactly the same issue as us!

          Seems like we're stuck on 1.9.15 until a fix is found & added to the upgrade script.

          Thanks,

          Ben

          Show
          Ben Faulkner added a comment - Hi David, Don't take this the wrong way but I'm glad to see that someone else has exactly the same issue as us! Seems like we're stuck on 1.9.15 until a fix is found & added to the upgrade script. Thanks, Ben
          Hide
          Rich Schumaker added a comment -

          I just tried same upgrade and got the same results - man I hope someone can come up with a fix or a work around - I am so pumped for 2.2 but I too am stuck on 1.9.

          Show
          Rich Schumaker added a comment - I just tried same upgrade and got the same results - man I hope someone can come up with a fix or a work around - I am so pumped for 2.2 but I too am stuck on 1.9.
          Hide
          Graeme Mitchell added a comment - - edited

          Tried to update from $release = '1.9.9+ (Build: 20100728)' to moodle-latest-20.zip and moodle-latest-22.zip
          No luck, similar errors as seen above.
          Hoping this can be fixed sometime soon...

          I can email a copy of the sql dump if that would be of assistance (about 600kB)

          Thanks,
          Graeme

          Show
          Graeme Mitchell added a comment - - edited Tried to update from $release = '1.9.9+ (Build: 20100728)' to moodle-latest-20.zip and moodle-latest-22.zip No luck, similar errors as seen above. Hoping this can be fixed sometime soon... I can email a copy of the sql dump if that would be of assistance (about 600kB) Thanks, Graeme
          Hide
          Gunther Lachambre added a comment -

          Upgrading from 1.9.16+ (build 20120202) to 2.2.2+ (build 20120315) :

          !!! Error writing to database !!!
          !! Column 'pagetype' cannot be null
          INSERT INTO mdl_block_positions (blockinstanceid, contextid, pagetype, subpage, visible, region, weight)
          SELECT bi.id, bi.contextid,
          CASE WHEN bi.pagetypepattern = 'course-view-*'
          THEN (SELECT CONCAT('course-view-', c.format)
          FROM mdl_course c
          JOIN mdl_context ctx ON c.id = ctx.instanceid
          WHERE ctx.id = bi.contextid)
          ELSE bi.pagetypepattern END,
          CASE WHEN bi.subpagepattern IS NULL
          THEN ''
          ELSE bi.subpagepattern END,
          0, bi.defaultregion, bi.defaultweight
          FROM mdl_block_instances bi
          WHERE bi.visible = 0 AND bi.pagetypepattern <> 'admin-*' AND bi.pagetypepattern IS NOT NULL
          [array (
          )] !!
          !! Stack trace: * line 397 of /lib/dml/moodle_database.php: dml_write_exception thrown

          • line 728 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
          • line 1847 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->execute()
          • line 1406 of /lib/upgradelib.php: call to xmldb_main_upgrade()
          • line 150 of /admin/cli/upgrade.php: call to upgrade_core()
            !!

          When applying query on database after upgrade stopped, it works...

          Show
          Gunther Lachambre added a comment - Upgrading from 1.9.16+ (build 20120202) to 2.2.2+ (build 20120315) : !!! Error writing to database !!! !! Column 'pagetype' cannot be null INSERT INTO mdl_block_positions (blockinstanceid, contextid, pagetype, subpage, visible, region, weight) SELECT bi.id, bi.contextid, CASE WHEN bi.pagetypepattern = 'course-view-*' THEN (SELECT CONCAT('course-view-', c.format) FROM mdl_course c JOIN mdl_context ctx ON c.id = ctx.instanceid WHERE ctx.id = bi.contextid) ELSE bi.pagetypepattern END, CASE WHEN bi.subpagepattern IS NULL THEN '' ELSE bi.subpagepattern END, 0, bi.defaultregion, bi.defaultweight FROM mdl_block_instances bi WHERE bi.visible = 0 AND bi.pagetypepattern <> 'admin-*' AND bi.pagetypepattern IS NOT NULL [array ( )] !! !! Stack trace: * line 397 of /lib/dml/moodle_database.php: dml_write_exception thrown line 728 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end() line 1847 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->execute() line 1406 of /lib/upgradelib.php: call to xmldb_main_upgrade() line 150 of /admin/cli/upgrade.php: call to upgrade_core() !! When applying query on database after upgrade stopped, it works...
          Hide
          Gunther Lachambre added a comment -

          I mean query runs, not upgrade works

          Show
          Gunther Lachambre added a comment - I mean query runs, not upgrade works
          Hide
          Wojciech Cieszynski added a comment -

          Hi everyone,

          I wondered if you guys had some luck with fixing this issue?

          I'm trying to upgrade from version 1.9.18 to 2.0.9...

          As described by Gunther above, the query runs just fine when I run it from within the MySQL but it fails when I start the upgrade process again.

          I repaired the table, flushed it, dropped it, eventually imported the table from the other M2 working instance... No luck...

          Any help would be much appreciated

          Thanks

          Wojciech

          Show
          Wojciech Cieszynski added a comment - Hi everyone, I wondered if you guys had some luck with fixing this issue? I'm trying to upgrade from version 1.9.18 to 2.0.9... As described by Gunther above, the query runs just fine when I run it from within the MySQL but it fails when I start the upgrade process again. I repaired the table, flushed it, dropped it, eventually imported the table from the other M2 working instance... No luck... Any help would be much appreciated Thanks Wojciech
          Hide
          Rich Schumaker added a comment -

          No solution on my end.

          Show
          Rich Schumaker added a comment - No solution on my end.
          Hide
          Michael de Raadt added a comment -

          Petr: It looks like this issue is still persisting.

          Show
          Michael de Raadt added a comment - Petr: It looks like this issue is still persisting.
          Hide
          Petr Škoda added a comment -

          I was not responsible for blocks refactoring and upgrade code, sorry, reassigning.

          Show
          Petr Škoda added a comment - I was not responsible for blocks refactoring and upgrade code, sorry, reassigning.
          Hide
          Ben Faulkner added a comment -

          Well, it's been a year since I last attempted an upgrade so thought I'd try again.

          This time I tried to move from 1.9.19 to 2.2.7 but I still receive the same issue.

          Debug info: Column 'pagetype' cannot be null
          INSERT INTO mdl_block_positions (blockinstanceid, contextid, pagetype, subpage, visible, region, weight)
          SELECT bi.id, bi.contextid,
          CASE WHEN bi.pagetypepattern = 'course-view-*'
          THEN (SELECT CONCAT('course-view-', c.format)
          FROM mdl_course c
          JOIN mdl_context ctx ON c.id = ctx.instanceid
          WHERE ctx.id = bi.contextid)
          ELSE bi.pagetypepattern END,
          CASE WHEN bi.subpagepattern IS NULL
          THEN ''
          ELSE bi.subpagepattern END,
          0, bi.defaultregion, bi.defaultweight
          FROM mdl_block_instances bi
          WHERE bi.visible = 0 AND bi.pagetypepattern <> 'admin-*' AND bi.pagetypepattern IS NOT NULL
          [array (
          )]

          Stack trace: •line 397 of \lib\dml\moodle_database.php: dml_write_exception thrown
          •line 802 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()
          •line 1863 of \lib\db\upgrade.php: call to mysqli_native_moodle_database->execute()
          •line 1394 of \lib\upgradelib.php: call to xmldb_main_upgrade()
          •line 236 of \admin\index.php: call to upgrade_core()

          Is it likely that someone will take ownership, address & fix this issue or should I break the bad news to our teaching staff that they'll need to start from scratch if they want the latest version of Moodle?

          All the best,

          Ben

          Show
          Ben Faulkner added a comment - Well, it's been a year since I last attempted an upgrade so thought I'd try again. This time I tried to move from 1.9.19 to 2.2.7 but I still receive the same issue. Debug info: Column 'pagetype' cannot be null INSERT INTO mdl_block_positions (blockinstanceid, contextid, pagetype, subpage, visible, region, weight) SELECT bi.id, bi.contextid, CASE WHEN bi.pagetypepattern = 'course-view-*' THEN (SELECT CONCAT('course-view-', c.format) FROM mdl_course c JOIN mdl_context ctx ON c.id = ctx.instanceid WHERE ctx.id = bi.contextid) ELSE bi.pagetypepattern END, CASE WHEN bi.subpagepattern IS NULL THEN '' ELSE bi.subpagepattern END, 0, bi.defaultregion, bi.defaultweight FROM mdl_block_instances bi WHERE bi.visible = 0 AND bi.pagetypepattern <> 'admin-*' AND bi.pagetypepattern IS NOT NULL [array ( )] Stack trace: •line 397 of \lib\dml\moodle_database.php: dml_write_exception thrown •line 802 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end() •line 1863 of \lib\db\upgrade.php: call to mysqli_native_moodle_database->execute() •line 1394 of \lib\upgradelib.php: call to xmldb_main_upgrade() •line 236 of \admin\index.php: call to upgrade_core() Is it likely that someone will take ownership, address & fix this issue or should I break the bad news to our teaching staff that they'll need to start from scratch if they want the latest version of Moodle? All the best, Ben
          Hide
          Wojciech Cieszynski added a comment -

          Hi Ben,

          I've run into a similar problem when upgrading from 1.9.x earlier in 2012 and it looks like you've a problem with one of the mdl_block tables. Please try to run this SQL query on your database before you start the upgrading process again:

          select bi.* from mdl_block_positions bi left join mdl_course c on c.id=bi.pageid where c.id is null and bi.pagetype<>'admin';

          Of course take a backup of a database before running it on the live system

          Hope that helps.

          Wojciech

          Show
          Wojciech Cieszynski added a comment - Hi Ben, I've run into a similar problem when upgrading from 1.9.x earlier in 2012 and it looks like you've a problem with one of the mdl_block tables. Please try to run this SQL query on your database before you start the upgrading process again: select bi.* from mdl_block_positions bi left join mdl_course c on c.id=bi.pageid where c.id is null and bi.pagetype<>'admin'; Of course take a backup of a database before running it on the live system Hope that helps. Wojciech
          Hide
          Ben Faulkner added a comment -

          Hi Wojciech,

          Many thanks for reaching out ...

          If I run the query on the 1.9.19 DB prior to attempting an upgrade I get the following error;

          Error Code: 1146. Table 'mdl_block_positions' doesn't exist

          If I then run the query on the DB after starting the upgrade and getting the issue I described above, I get the following error;

          Error Code: 1054. Unknown column 'bi.pageid' in 'on clause'

          I've checked the DB prior to upgrade and as the query error suggests, the table isn't present. The table then appears to be created during the upgrade process and is present after the upgrade issue is experienced.

          Any ideas?

          Thanks,

          Ben

          Show
          Ben Faulkner added a comment - Hi Wojciech, Many thanks for reaching out ... If I run the query on the 1.9.19 DB prior to attempting an upgrade I get the following error; Error Code: 1146. Table 'mdl_block_positions' doesn't exist If I then run the query on the DB after starting the upgrade and getting the issue I described above, I get the following error; Error Code: 1054. Unknown column 'bi.pageid' in 'on clause' I've checked the DB prior to upgrade and as the query error suggests, the table isn't present. The table then appears to be created during the upgrade process and is present after the upgrade issue is experienced. Any ideas? Thanks, Ben
          Hide
          Rafael Bohrer Avila added a comment -

          I'm having the exact same problem. Upgrading from 1.9.19+ (Build: 20121112) to 2.2.9+ (Build: 20130425). CentOS 6.3, PHP 5.3.3, Postgres 8.4.13, Apache HTTPD 2.2.15.

          I found this error surprising, since I had already performed an upgrade from 1.9 before (on a different server) without any glitch.

          The only thing I've done differently this time is that I haven't put the server into maintenance mode before starting the upgrade – but I'm 99% positive that no one was using it. Could this have any relation to the problem?

          Thanks
          Rafael

          Show
          Rafael Bohrer Avila added a comment - I'm having the exact same problem. Upgrading from 1.9.19+ (Build: 20121112) to 2.2.9+ (Build: 20130425). CentOS 6.3, PHP 5.3.3, Postgres 8.4.13, Apache HTTPD 2.2.15. I found this error surprising, since I had already performed an upgrade from 1.9 before (on a different server) without any glitch. The only thing I've done differently this time is that I haven't put the server into maintenance mode before starting the upgrade – but I'm 99% positive that no one was using it. Could this have any relation to the problem? Thanks Rafael
          Hide
          Rafael Bohrer Avila added a comment -

          Just to report my latest attempts:

          • I tried the upgrade again, this time putting the server in maintenance mode, but got the same error;
          • another thing I tried was to initially upgrade to the latest 1.9 version available (1.9.19+, Build 20130125), but still no good;
          • my last attempt was to use the exact same 2.2 version with which I had success a few months ago, and that was 2.2.7. Still, same problem.

          Rafael

          Show
          Rafael Bohrer Avila added a comment - Just to report my latest attempts: I tried the upgrade again, this time putting the server in maintenance mode, but got the same error; another thing I tried was to initially upgrade to the latest 1.9 version available (1.9.19+, Build 20130125), but still no good; my last attempt was to use the exact same 2.2 version with which I had success a few months ago, and that was 2.2.7. Still, same problem. Rafael
          Hide
          Rogério Duarte added a comment -

          We've just found the cause of our problem (situation reported by Rafael above): our table mdl_block_instance was not consistent, and it contained some rows from two deleted courses. We just identified these two courses and removed the corresponding lines from mdl_block_instance (in the original/pre migration environment). The migration than proceeded without further problems.

          Rogerio

          Show
          Rogério Duarte added a comment - We've just found the cause of our problem (situation reported by Rafael above): our table mdl_block_instance was not consistent, and it contained some rows from two deleted courses. We just identified these two courses and removed the corresponding lines from mdl_block_instance (in the original/pre migration environment). The migration than proceeded without further problems. Rogerio
          Hide
          Petr Škoda added a comment -

          Thanks a lot for the info, I have submitted a patch that adds code that deletes blocks for incorrectly deleted courses and activities, hopefully it will address this upgrade issue. I have also discovered a problem in SQL for activity blocks upgrade, I suppose it did not work at all before.

          Ciao

          Show
          Petr Škoda added a comment - Thanks a lot for the info, I have submitted a patch that adds code that deletes blocks for incorrectly deleted courses and activities, hopefully it will address this upgrade issue. I have also discovered a problem in SQL for activity blocks upgrade, I suppose it did not work at all before. Ciao
          Hide
          Dan Poltawski added a comment -

          Integrated to 22, thanks Petr.

          Show
          Dan Poltawski added a comment - Integrated to 22, thanks Petr.
          Hide
          Mark Nelson added a comment -

          Upgrade worked as expected, did not come across the error specified. Also, blocks for activities that I did not remove from the database manually remained.

          Show
          Mark Nelson added a comment - Upgrade worked as expected, did not come across the error specified. Also, blocks for activities that I did not remove from the database manually remained.
          Hide
          Damyon Wiese added a comment -

          Thanks for your hard work. This issue has now been sent upstream and will soon be downloaded by millions of Moodlers!

          Regards, Damyon

          Show
          Damyon Wiese added a comment - Thanks for your hard work. This issue has now been sent upstream and will soon be downloaded by millions of Moodlers! Regards, Damyon

            People

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

              Dates

              • Created:
                Updated:
                Resolved: