[MDL-39476] Duplicate rows from assignment migration breaks upgrade - Moodle Tracker

XML

Word

Printable

Details

Type: Bug
Status: Closed
Priority: Critical
Resolution: Fixed
Affects Version/s: 2.5
Fix Version/s: 2.5
Component/s: Assignment
Labels:
- triaged

Testing Instructions:
Hide

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);
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:
git://github.com/damyon/moodle.git
Pull Master Branch:
~~MDL-39476~~-master
Pull Master Diff URL:
https://github.com/damyon/moodle/compare/e7ff753cab0762232fceb4b3b2e67315c6e08a97...MDL-39476-master

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

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

make-bad-data.php
2 kB
03/May/13 1:03 PM

Issue Links

has a non-specific relationship to

MDL-39460 mod_assign upgrade fails when upgrading from 2.3.6 to 2.5beta+ (Build: 20130426)

Closed

Activity

People

Assignee:: Damyon Wiese

Reporter:: Eric Merrill

Integrator:: Dan Poltawski

Tester:: Rossiani Wijaya

Participants:: Benjamin SECLIER, Damyon Wiese, Dan Poltawski, Eloy Lafuente (stronk7), Eric Merrill, Rossiani Wijaya

Component watchers:: Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 03/May/13 4:22 AM

Updated:: 03/Jul/15 12:57 AM

Resolved:: 11/May/13 12:37 AM

Fix Release Date:: 14/May/13