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

The MDL-69687 upgrade step kills large databases

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      These steps are copied from MDL-69687.

      You need to start with a Moodle 3.9.2 site, preferably one that contains some real data.

      I think that it is sufficient to test this change on just one of the supproted branches. The change is identical on each branch.

      Notes

      • This issue must be tested against MySQL or MariaDB, and Postgres (i.e. MySQL and Postgres, or MariaDB and Postgres)

      Setup

      1. Have a look at your config.php and locate the $CFG->prefix. Make a note of it.
      2. Run the following SQL in your database (replace 'mdl_' with the prefix in use), and make a note of the resulting number: 

        SELECT COUNT(1) FROM mdl_course_modules_completion;

      3. Make a note of the result.
        We will re-run this query a number of times and we'll need this

      Before applying the patch

      1. Enable completion tracking on your Moodle site:
        1. Login as admin
        2. Navigate to Site administration -> Advanced features
        3. Enable completion tracking
      2. Create three new courses (with completion enabled).
      3. Create a new assignment in each course
        1. Under "Activity completion" set "Completion tracking" to "Students can manually mark the activity as completed"
      4. Duplicate the assignment twice in each course (Each course now has three assignments)
      5. Enrol a student in each course
      6. Log in as the test student account and mark each of the activities complete.
      7. Run the following SQL, using the original result you had earlier: 

        SELECT COUNT(1) - [resultfromsetup] FROM mdl_course_modules_completion;
        		 
         
        		 
        -- For example:
        		 
        SELECT COUNT(1) - 308 FROM mdl_course_modules_completion;

        1. Confirm that the result is 9
      8. Log back in as admin
      9. Delete one of the assignments in each course
      10. Run the SQL again using the same value - i.e. in the example 308
        1. Confirm that the result is 6
      11. Delete one of the courses
      12. Run the SQL again using the same value - i.e. in the example 308
        1. Confirm that the result has not changed and is still 6

      Apply the patch

      1. Ensure that you're on the latest version of the code for your branch
      2. Run the Moodle upgrade: 

        php admin/cli/upgrade.php

      3. Run the SQL again using the same value - i.e. in the example 308
        1. Confirm that the result has not changed and is now 4
      4. Delete one of the remaining courses.
      5. Run the SQL again using the same value - i.e. in the example 308
        1. Confirm that the result has not changed and is now 2
      Show
      These steps are copied from MDL-69687 . You need to start with a Moodle 3.9.2 site, preferably one that contains some real data. I think that it is sufficient to test this change on just one of the supproted branches. The change is identical on each branch. Notes This issue must be tested against MySQL or MariaDB, and Postgres (i.e. MySQL and Postgres, or MariaDB and Postgres) Setup Have a look at your config.php and locate the $CFG-> prefix . Make a note of it. Run the following SQL in your database (replace 'mdl_' with the prefix in use), and make a note of the resulting number: SELECT COUNT(1) FROM mdl_course_modules_completion; Make a note of the result. We will re-run this query a number of times and we'll need this Before applying the patch Enable completion tracking on your Moodle site: Login as admin Navigate to Site administration -> Advanced features Enable completion tracking Create three new courses (with completion enabled). Create a new assignment in each course Under "Activity completion" set "Completion tracking" to "Students can manually mark the activity as completed" Duplicate the assignment twice in each course (Each course now has three assignments) Enrol a student in each course Log in as the test student account and mark each of the activities complete. Run the following SQL, using the original result you had earlier: SELECT COUNT(1) - [resultfromsetup] FROM mdl_course_modules_completion;   -- For example: SELECT COUNT(1) - 308 FROM mdl_course_modules_completion; Confirm that the result is 9 Log back in as admin Delete one of the assignments in each course Run the SQL again using the same value - i.e. in the example 308 Confirm that the result is 6 Delete one of the courses Run the SQL again using the same value - i.e. in the example 308 Confirm that the result has not changed and is still 6 Apply the patch Ensure that you're on the latest version of the code for your branch Run the Moodle upgrade: php admin/cli/upgrade.php Run the SQL again using the same value - i.e. in the example 308 Confirm that the result has not changed and is now 4 Delete one of the remaining courses. Run the SQL again using the same value - i.e. in the example 308 Confirm that the result has not changed and is now 2
    • Affected Branches:
      MOODLE_310_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
    • Fixed Branches:
      MOODLE_310_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
    • Pull from Repository:
    • Pull 3.9 Branch:
    • Pull 3.9 Diff URL:
    • Pull 3.10 Branch:
      MDL-70285_310
    • Pull 3.10 Diff URL:
    • Pull 3.11 Branch:
      MDL-70285_311
    • Pull 3.11 Diff URL:
    • Pull Master Branch:
    • Pull Master Diff URL:

      Description

      MDL-69687 tries to delete a lot of records on upgrade (well, on our database 20 503 086 rows out of 46 386 719) using the query

      SELECT count(1)
      		 
      FROM mdl_course_modules_completion
      		 
      WHERE id IN (
      		 
                      SELECT cmc.id
      		 
                        FROM mdl_course_modules_completion cmc
      		 
                   LEFT JOIN mdl_course_modules cm ON cm.id = cmc.coursemoduleid
      		 
                       WHERE cm.id IS NULL
      		 
      )

      The essentialy runs forever (Well 48.5 minutes.)

      That looks like it is eqivalent to

      SELECT count(1)
      		 
      FROM mdl_course_modules_completion
      		 
      WHERE NOT EXISTS (
      		 
              SELECT 1
      		 
              FROM mdl_course_modules cm
      		 
              WHERE cm.id = mdl_course_modules_completion.coursemoduleid
      		 
      )

      which run in 7 seconds on the same DB (though that is just a SLECET, it is not having to delete 20 million rows!)

      I'll do a fix.

        Attachments

          Issue Links

          is a regression caused by

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-69687 Improve removal of course module completion data

          • Minor - Minor loss of function where workaround is possible
          • Closed

            Activity

              People

              Assignee:
              timhunt Tim Hunt
              Reporter:
              timhunt Tim Hunt
              Peer reviewer:
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              Integrator:
              Jake Dallimore Jake Dallimore
              Tester:
              Anna Carissa Sadia Anna Carissa Sadia
              Participants:
              Component watchers:
              Sam Marshall, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Sara Arjona (@sarjona), Matteo Scaramuccia, Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              3 Vote for this issue
              Watchers:
              12 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                18/Jan/21

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 1 hour, 10 minutes
                  1d 1h 10m