Moodle
  1. Moodle
  2. MDL-32851

Moodle 1.9 -> 2.2 upgrade Data too long for column 'mainfile' error in mod_resource

    Details

    • Testing Instructions:
      Hide

      1. Create a 1.9 instance.
      2. Create a resource with a ratty filename (the example in the description should work).
      3. Upgrade to 2.2. Verify that the resource is skipped.

      Show
      1. Create a 1.9 instance. 2. Create a resource with a ratty filename (the example in the description should work). 3. Upgrade to 2.2. Verify that the resource is skipped.
    • Affected Branches:
      MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Pull from Repository:

      Description

      In upgrade from moodle-latest-19 to moodle-latest-22 (2.2.2+ Build: 20120504)

      Debug info: Data too long for column 'mainfile' at row 1
      UPDATE mdl_resource SET tobemigrated = ?,mainfile = ?,filterfiles = ?,legacyfiles = ?,display = ?,displayoptions = ? WHERE id=?
      [array (
      0 => 0,
      1 => '/Mount St. Helens is an active stratovolcano located in Skamania County, Washington, in the Pacific Northwest region of the United States. It is 96 miles (154 km) south of Seattle and 50 miles (80 km) northeast of Portland, Oregon. Mount St. Helens takes i',
      2 => '1',
      3 => 0,
      4 => 6,
      5 => 'a:4:

      {s:12:"printheading";i:0;s:10:"printintro";i:1;s:10:"popupwidth";s:3:"620";s:11:"popupheight";s:3:"450";}

      ',
      6 => '11531',
      )]
      Stack trace:
      line 397 of /lib/dml/moodle_database.php: dml_write_exception thrown
      line 999 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
      line 1031 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->update_record_raw()
      line 173 of /mod/resource/db/upgradelib.php: call to mysqli_native_moodle_database->update_record()
      line 181 of /mod/resource/db/upgrade.php: call to resource_20_migrate()
      line 540 of /lib/upgradelib.php: call to xmldb_resource_upgrade()
      line 271 of /lib/upgradelib.php: call to upgrade_plugins_modules()
      line 1437 of /lib/upgradelib.php: call to upgrade_plugins()
      line 269 of /admin/index.php: call to upgrade_noncore()

        Gliffy Diagrams

          Issue Links

            Activity

            Hide
            Kimball Johnson added a comment -

            Note, database is mysql

            Show
            Kimball Johnson added a comment - Note, database is mysql
            Hide
            Michael de Raadt added a comment -

            This is a little confusing. The mdl_resource table doesn't seem to have a mainfile field, and if it did, it should probably be a number. Perhaps this value is supposed to go to the intro field.

            So I'm not sure if this is a fault in the upgrade code or your data (or both?).

            Could you try removing this resource from the 1.9 course and letting us know the effect?

            Show
            Michael de Raadt added a comment - This is a little confusing. The mdl_resource table doesn't seem to have a mainfile field, and if it did, it should probably be a number. Perhaps this value is supposed to go to the intro field. So I'm not sure if this is a fault in the upgrade code or your data (or both?). Could you try removing this resource from the 1.9 course and letting us know the effect?
            Hide
            Kimball Johnson added a comment -

            How do you suggest I remove the resource?

            As for bugs in the code or data this is the not the first migration I have done, but the first I have seen this error with. However I am not sure how the data could be wrong if you think the field is incorrect, isn't that the code not the data?

            Show
            Kimball Johnson added a comment - How do you suggest I remove the resource? As for bugs in the code or data this is the not the first migration I have done, but the first I have seen this error with. However I am not sure how the data could be wrong if you think the field is incorrect, isn't that the code not the data?
            Hide
            Andrew Nicols added a comment -

            Looks like mainfile existed until MDL-22548 when it was removed.

            Show
            Andrew Nicols added a comment - Looks like mainfile existed until MDL-22548 when it was removed.
            Hide
            Michael de Raadt added a comment -

            It could possibly be an error in the code that created the data (chicken-egg problem).

            You could try removing the resource in the 1.9 site before doing the backup. If you can't do that, you could perhaps hunt through the XML inside the backup and manually remove the entry.

            Show
            Michael de Raadt added a comment - It could possibly be an error in the code that created the data (chicken-egg problem). You could try removing the resource in the 1.9 site before doing the backup. If you can't do that, you could perhaps hunt through the XML inside the backup and manually remove the entry.
            Hide
            Kimball Johnson added a comment -

            I think you misunderstand the issue , this is a full upgrade on the 1.9 site to 2.2. There is no XML involved.

            The course_files and database have been copied to the test server, upgraded to 1.9 latest, tested and then moodle files upgraded to 2.2, where I preform the inbuilt upgrade process from 1.9 to 2.2.

            I have no idea which resource it is in the 1.9 site, but I can try and find it and remove it.

            Show
            Kimball Johnson added a comment - I think you misunderstand the issue , this is a full upgrade on the 1.9 site to 2.2. There is no XML involved. The course_files and database have been copied to the test server, upgraded to 1.9 latest, tested and then moodle files upgraded to 2.2, where I preform the inbuilt upgrade process from 1.9 to 2.2. I have no idea which resource it is in the 1.9 site, but I can try and find it and remove it.
            Hide
            Kimball Johnson added a comment -

            I have looked further and I cannot find the resource in the moodle, is it possible to remove it directly from the 1.9 database? Does the id of '11531' listed in the error bear ans resemblance to the 1.9 database at all?

            Show
            Kimball Johnson added a comment - I have looked further and I cannot find the resource in the moodle, is it possible to remove it directly from the 1.9 database? Does the id of '11531' listed in the error bear ans resemblance to the 1.9 database at all?
            Hide
            Kimball Johnson added a comment -

            Finally found it - it does look like the source data is broken, any idea how this could have happened?

            mysql> select id,course,reference from mdl_resource where id=11531;
            ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            id course reference

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

            11531 1543 Mount St. Helens is an active stratovolcano located in Skamania County, Washington, in the Pacific Northwest region of the United States. It is 96 miles (154 km) south of Seattle and 50 miles (80 km) northeast of Portland, Oregon. Mount St. Helens takes i

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

            Oh, right, got it - there is no validation on the modedit.php for the Location field under Link to a file or web site. So anything can be typed in, and you get an error if you click the resource, hiding a more serious upgrade error.

            As far as I am concerned this bug can be closed.

            Show
            Kimball Johnson added a comment - Finally found it - it does look like the source data is broken, any idea how this could have happened? mysql> select id,course,reference from mdl_resource where id=11531; ------ ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- id course reference ------ ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 11531 1543 Mount St. Helens is an active stratovolcano located in Skamania County, Washington, in the Pacific Northwest region of the United States. It is 96 miles (154 km) south of Seattle and 50 miles (80 km) northeast of Portland, Oregon. Mount St. Helens takes i ------ ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Oh, right, got it - there is no validation on the modedit.php for the Location field under Link to a file or web site. So anything can be typed in, and you get an error if you click the resource, hiding a more serious upgrade error. As far as I am concerned this bug can be closed.
            Hide
            Terry Gibbons added a comment - - edited

            So what is the solution or workaround? I've run into a similar problem with an upgrade from 1.9 to 2.2 and am in the process of troubleshooting.

            See info below:

            Debug info: Data too long for column 'mainfile' at row 1
            UPDATE mdl_resource SET tobemigrated = ?,mainfile = ?,filterfiles = ?,legacyfiles = ?,display = ?,displayoptions = ? WHERE id=?
            [array (
            0 => 0,
            1 => '/<object width="640" height="26" classid="clsid: D27CDB6E-AE6D-11cf-96B8-444553540000"><param value="true" name="allowfullscreen"/><param value="always" name="allowscriptaccess"/><param value="high" name="quality"/><param value="true" name="cachebusting"/><',
            2 => '0',
            3 => 0,
            4 => 6,
            5 => 'a:4:

            {s:12:"printheading";i:0;s:10:"printintro";i:1;s:10:"popupwidth";s:3:"620";s:11:"popupheight";s:3:"450";}

            ',
            6 => '2975',
            )]
            Stack trace:
            •line 397 of \lib\dml\moodle_database.php: dml_write_exception thrown
            •line 999 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()
            •line 1031 of \lib\dml\mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->update_record_raw()
            •line 173 of \mod\resource\db\upgradelib.php: call to mysqli_native_moodle_database->update_record()
            •line 181 of \mod\resource\db\upgrade.php: call to resource_20_migrate()
            •line 540 of \lib\upgradelib.php: call to xmldb_resource_upgrade()
            •line 271 of \lib\upgradelib.php: call to upgrade_plugins_modules()
            •line 1437 of \lib\upgradelib.php: call to upgrade_plugins()
            •line 269 of \admin\index.php: call to upgrade_noncore()

            Show
            Terry Gibbons added a comment - - edited So what is the solution or workaround? I've run into a similar problem with an upgrade from 1.9 to 2.2 and am in the process of troubleshooting. See info below: Debug info: Data too long for column 'mainfile' at row 1 UPDATE mdl_resource SET tobemigrated = ?,mainfile = ?,filterfiles = ?,legacyfiles = ?,display = ?,displayoptions = ? WHERE id=? [array ( 0 => 0, 1 => '/<object width="640" height="26" classid="clsid: D27CDB6E-AE6D-11cf-96B8-444553540000"><param value="true" name="allowfullscreen"/><param value="always" name="allowscriptaccess"/><param value="high" name="quality"/><param value="true" name="cachebusting"/><', 2 => '0', 3 => 0, 4 => 6, 5 => 'a:4: {s:12:"printheading";i:0;s:10:"printintro";i:1;s:10:"popupwidth";s:3:"620";s:11:"popupheight";s:3:"450";} ', 6 => '2975', )] Stack trace: •line 397 of \lib\dml\moodle_database.php: dml_write_exception thrown •line 999 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end() •line 1031 of \lib\dml\mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->update_record_raw() •line 173 of \mod\resource\db\upgradelib.php: call to mysqli_native_moodle_database->update_record() •line 181 of \mod\resource\db\upgrade.php: call to resource_20_migrate() •line 540 of \lib\upgradelib.php: call to xmldb_resource_upgrade() •line 271 of \lib\upgradelib.php: call to upgrade_plugins_modules() •line 1437 of \lib\upgradelib.php: call to upgrade_plugins() •line 269 of \admin\index.php: call to upgrade_noncore()
            Hide
            Kimball Johnson added a comment -

            In your case the workaround is to delete the resource, you can do this with DELETE FROM mdl_resource WHERE id=2975;

            However I would suggest you do SELECT id,course,reference FROM mdl_resource WHERE id=2975; and then navigate to the course id returned and have a look at what the resource is trying to do, and then you may find the cause of the bad data.

            Show
            Kimball Johnson added a comment - In your case the workaround is to delete the resource, you can do this with DELETE FROM mdl_resource WHERE id=2975; However I would suggest you do SELECT id,course,reference FROM mdl_resource WHERE id=2975; and then navigate to the course id returned and have a look at what the resource is trying to do, and then you may find the cause of the bad data.
            Hide
            Terry Gibbons added a comment - - edited

            Much appreciated... that put me on the right track and saved a huge amount of time locating the specific resource and removing the problem. The resource was still rather hard to find but by adding "name" to the query as in "SELECT id,course,name, reference FROM mdl_resource WHERE id=2975; it was easier to spot. The reference cited in the Debug info was likely something that the course creator intended to use for including media within a pop up window but they had mistakenly placed the embed code into the link field which of course threw the error during upgrade. Bad data...no bug... excellent resolution... Thanks so much for clarifying.

            Show
            Terry Gibbons added a comment - - edited Much appreciated... that put me on the right track and saved a huge amount of time locating the specific resource and removing the problem. The resource was still rather hard to find but by adding "name" to the query as in "SELECT id,course,name, reference FROM mdl_resource WHERE id=2975; it was easier to spot. The reference cited in the Debug info was likely something that the course creator intended to use for including media within a pop up window but they had mistakenly placed the embed code into the link field which of course threw the error during upgrade. Bad data...no bug... excellent resolution... Thanks so much for clarifying.
            Hide
            Michael de Raadt added a comment -

            Thanks for persisting with this guys. I'm glad this got sorted out.

            Show
            Michael de Raadt added a comment - Thanks for persisting with this guys. I'm glad this got sorted out.
            Hide
            Charles Fulton added a comment -

            I think this is actually a bug in the upgrade script and should be patched. The upgrade already tries to migrate the file and updates the mainfile if it's successful. It seems to me that it should skip over the resource if the migration fails. Patch attached.

            Show
            Charles Fulton added a comment - I think this is actually a bug in the upgrade script and should be patched. The upgrade already tries to migrate the file and updates the mainfile if it's successful. It seems to me that it should skip over the resource if the migration fails. Patch attached.
            Hide
            Michael de Raadt added a comment -

            Never say die! Thanks for finding the source of the problem, Charles.

            Show
            Michael de Raadt added a comment - Never say die! Thanks for finding the source of the problem, Charles.
            Hide
            Dan Poltawski added a comment -

            Hi Charles,

            Inline ifs are against the Moodle coding style ( http://docs.moodle.org/dev/Coding_style#If_.2F_else ), so that continue should be wrapped in braces and on a new line .

            Otherwise amkes sense I think!

            Show
            Dan Poltawski added a comment - Hi Charles, Inline ifs are against the Moodle coding style ( http://docs.moodle.org/dev/Coding_style#If_.2F_else ), so that continue should be wrapped in braces and on a new line . Otherwise amkes sense I think!
            Hide
            Charles Fulton added a comment -

            Thanks Dan. Updated and rebased.

            Show
            Charles Fulton added a comment - Thanks Dan. Updated and rebased.
            Hide
            Dan Poltawski added a comment -

            Thanks Charles, submitting for integration.

            Please could you fill out testing instructions,

            cheers,
            dan

            Show
            Dan Poltawski added a comment - Thanks Charles, submitting for integration. Please could you fill out testing instructions, cheers, dan
            Hide
            Sam Hemelryk added a comment -

            Thanks guys, this has been integrated now

            Show
            Sam Hemelryk added a comment - Thanks guys, this has been integrated now
            Hide
            David Monllaó added a comment -

            Problem reproduced and patch tested, both migrating from 1.9 (latest stable) to 2.2; with the patch it skips the file. Passes

            Show
            David Monllaó added a comment - Problem reproduced and patch tested, both migrating from 1.9 (latest stable) to 2.2; with the patch it skips the file. Passes
            Hide
            Sam Hemelryk added a comment -

            Congratulations your code is upstream - gold star for you!

            This issue + 79 others made it in in time for the minor releases.
            Thank you everyone involved for your exuberant efforts.

            Show
            Sam Hemelryk added a comment - Congratulations your code is upstream - gold star for you! This issue + 79 others made it in in time for the minor releases. Thank you everyone involved for your exuberant efforts.

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: