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

Duplicatated activity completion entries in database not handled correctly

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.0.3, 2.1
    • Fix Version/s: 2.0.4
    • Component/s: Activity completion
    • Labels:
      None
    • Testing Instructions:
      Hide

      Full test involves steps that must be complete BEFORE and AFTER the upgrade. It also requires database access.

      You can also just test the AFTER steps.

      BEFORE UPGRADE

      0. Ensure completion system is enabled at system level and on a test course.
      1. Add a new Page on the test course. Use default settings including manual completion tickbox.
      2. Turn editing off. Look at the Page on the course page. Note that there is a tickbox but it is not ticked yet.
      3. Using a totally different browser (eg Internet Explorer), visit the same course page and log in using the same user account.
      4. Still using the different browser, tick the tickbox. (You can now close the different browser)
      5. Back in the first browser, tick the tickbox.
      6. Note the course-module ID of the Page. For example if the URL is /mod/page/view.php?id=319 then the course-module ID is 319.
      7. Access the database directly and run this query (where 'mdl_' is your database prefix and 319 is the course-module ID):

      SELECT * FROM mdl_course_modules_completion WHERE coursemoduleid=319;

      The result will include 2 rows. This is a demonstration of the bug; the database is now in incorrect state. Note the IDs of the two rows (for example 13 and 14).

      AFTER UPGRADE

      1. Rerun the query above. You should find that of those two duplicates, the row with the lower ID (13 in my example) has been deleted and there is only one row now.
      2. Repeat steps 1-7 of the test above (note - this means you will create a second Page activity, not reuse the last one). This time, the query from step 7 should only return one row.

      Show
      Full test involves steps that must be complete BEFORE and AFTER the upgrade. It also requires database access. You can also just test the AFTER steps. BEFORE UPGRADE 0. Ensure completion system is enabled at system level and on a test course. 1. Add a new Page on the test course. Use default settings including manual completion tickbox. 2. Turn editing off. Look at the Page on the course page. Note that there is a tickbox but it is not ticked yet. 3. Using a totally different browser (eg Internet Explorer), visit the same course page and log in using the same user account. 4. Still using the different browser, tick the tickbox. (You can now close the different browser) 5. Back in the first browser, tick the tickbox. 6. Note the course-module ID of the Page. For example if the URL is /mod/page/view.php?id=319 then the course-module ID is 319. 7. Access the database directly and run this query (where 'mdl_' is your database prefix and 319 is the course-module ID): SELECT * FROM mdl_course_modules_completion WHERE coursemoduleid=319; The result will include 2 rows. This is a demonstration of the bug; the database is now in incorrect state. Note the IDs of the two rows (for example 13 and 14). AFTER UPGRADE 1. Rerun the query above. You should find that of those two duplicates, the row with the lower ID (13 in my example) has been deleted and there is only one row now. 2. Repeat steps 1-7 of the test above (note - this means you will create a second Page activity, not reuse the last one). This time, the query from step 7 should only return one row.
    • Affected Branches:
      MOODLE_20_STABLE, MOODLE_21_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE
    • Pull Master Branch:
      MDL-28021-master

      Description

      We have a client heavily using activity completion within their course, and users sometimes get stuck with activities showing as not completed, despite the activity completion report showing them completion. Sometimes the completion state can even spontaneously reverse.

      I tracked this down to having duplicate completion records for each activity, i.e the records in prefix_course_modules_completion are not unique by (userid, cmid) which some of the code seems to assume e.g use of get_record. Our DB is postgres but any DB can return the records in any order it likes, so results are inconsistent.

      I have resolved this in my patch (will github soon) by only using the latest record in the php code, as the database is not indexed on time so an ORDER BY is not a trivial option.

      If it is intended for course_modules_completion to be unique by (userid, cmid) then this must be enforced by code & UNIQUE INDEX, and corrected by an upgrade script. If an audit trail of completion states should exist, then either my fix can be used or the table should be indexed by time and only the most recent records used.

      For now, my patch will resolve the immediate issue and allow students to progress in their courses.

        Gliffy Diagrams

          Attachments

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  1/Aug/11