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

The MDL-69687 upgrade step kills large databases

XMLWordPrintable

    • MOODLE_310_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
    • MOODLE_310_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
    • 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

      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.

        1. MDL-70285.jpg
          MDL-70285.jpg
          20 kB

            timhunt Tim Hunt
            timhunt Tim Hunt
            Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
            Jake Dallimore Jake Dallimore
            Anna Carissa Sadia Anna Carissa Sadia
            Votes:
            3 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved:

                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

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.