Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-39476

Duplicate rows from assignment migration breaks upgrade

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.5
    • Fix Version/s: 2.5
    • Component/s: Assignment
    • Labels:
    • Testing Instructions:
      Hide

      On all databases:

      1. Install Moodle 24
      2. put make-bad-data.php in the moodle root
      3. load it (should create lots of courses with assignments with bad data)
      4. Remove make-bad-data.php (it's nasty)
      5. Upgrade to 25 (with this patch)
      6. Verify the upgrade completes quickly
      7. Verify that there are no more duplicates in the database after upgrade - here is some SQL for that:
        SELECT userid, groupid, assignment from mdl_assign_submission GROUP BY userid, groupid, assignment HAVING (count(id) > 1);
        SELECT userid, assignment from mdl_assign_grades GROUP BY userid, assignment HAVING (count(id) > 1);
      Show
      On all databases: Install Moodle 24 put make-bad-data.php in the moodle root load it (should create lots of courses with assignments with bad data) Remove make-bad-data.php (it's nasty) Upgrade to 25 (with this patch) Verify the upgrade completes quickly Verify that there are no more duplicates in the database after upgrade - here is some SQL for that: SELECT userid, groupid, assignment from mdl_assign_submission GROUP BY userid, groupid, assignment HAVING (count(id) > 1); SELECT userid, assignment from mdl_assign_grades GROUP BY userid, assignment HAVING (count(id) > 1);
    • Affected Branches:
      MOODLE_25_STABLE
    • Fixed Branches:
      MOODLE_25_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-39476-master
    • Pull Master Diff URL:

      Description

      When testing the Moodle 2.5 Beta+ (20130502) against a copy of our prod DB, we ran into a problem:

      Debug info: ERROR: could not create unique index "mdl_assisubm_assusegroatt_uix"
      DETAIL: Key (assignment, userid, groupid, attemptnumber)=(16514, 184485, 0, 0) is duplicated.
      CREATE UNIQUE INDEX mdl_assisubm_assusegroatt_uix ON mdl_assign_submission (assignment, userid, groupid, attemptnumber)
      Error code: ddlexecuteerror
      Stack trace:
      • line 429 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown
      • line 248 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
      • line 646 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
      • line 88 of /lib/ddl/database_manager.php: call to pgsql_native_moodle_database->change_database_structure()
      • line 77 of /lib/ddl/database_manager.php: call to database_manager->execute_sql()
      • line 819 of /lib/ddl/database_manager.php: call to database_manager->execute_sql_arr()
      • line 342 of /mod/assign/db/upgrade.php: call to database_manager->add_index()
      • line 664 of /lib/upgradelib.php: call to xmldb_assign_upgrade()
      • line 381 of /lib/upgradelib.php: call to upgrade_plugins_modules()
      • line 1591 of /lib/upgradelib.php: call to upgrade_plugins()
      • line 379 of /admin/index.php: call to upgrade_noncore()

      If this occurs, it leaves the site in a seemingly unrecoverable state (attempting to re-run/continue the upgrade files).

      Looking into this more, we had about 20 records that violated this constraint. You can use this SQL to check a site for it (well, at least on postgres):
      SELECT max(assignment), max(userid), COUNT(id) FROM mdl_assign_submission GROUP BY assignment, userid HAVING (COUNT(id) > 1)

      All of the violations we have came from assignments that were upgraded from Assign (2.2) using the migration tool.

      My personal opinion (since this happened with a release version of the migration tool) is that the upgrade code for 2.5 should find and clean up these occurrences.

      UPDATE:
      After fixing those rows, we also found the same errors in mdl_assign_grades:
      Debug info: ERROR: could not create unique index "mdl_assigrad_assuseatt_uix"
      DETAIL: Key (assignment, userid, attemptnumber)=(16514, 184485, 0) is duplicated.
      CREATE UNIQUE INDEX mdl_assigrad_assuseatt_uix ON mdl_assign_grades (assignment, userid, attemptnumber)
      Error code: ddlexecuteerror
      Stack trace:
      • line 429 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown
      • line 248 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
      • line 646 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
      • line 88 of /lib/ddl/database_manager.php: call to pgsql_native_moodle_database->change_database_structure()
      • line 77 of /lib/ddl/database_manager.php: call to database_manager->execute_sql()
      • line 819 of /lib/ddl/database_manager.php: call to database_manager->execute_sql_arr()
      • line 351 of /mod/assign/db/upgrade.php: call to database_manager->add_index()
      • line 664 of /lib/upgradelib.php: call to xmldb_assign_upgrade()
      • line 381 of /lib/upgradelib.php: call to upgrade_plugins_modules()
      • line 1591 of /lib/upgradelib.php: call to upgrade_plugins()
      • line 379 of /admin/index.php: call to upgrade_noncore()

        Attachments

          Issue Links

          has a non-specific relationship to

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-39460 mod_assign upgrade fails when upgrading from 2.3.6 to 2.5beta+ (Build: 20130426)

          • Critical - Crashes server, loss of data, severe memory leak
          • Closed

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  14/May/13