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:

      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()

        Gliffy Diagrams

          Issue Links

            Activity

            Hide
            Petr Skoda added a comment -

            Hello, how can I duplicate this problem?

            Show
            Petr Skoda added a comment - Hello, how can I duplicate this problem?
            Hide
            Petr Skoda 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 Skoda 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 Skoda 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 Skoda 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 Skoda 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 Skoda 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 Skoda added a comment -

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

            Show
            Petr Skoda 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 Skoda 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 Skoda 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 Skoda added a comment -

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

            Show
            Petr Skoda 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 Skoda 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 Skoda 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: