Moodle
  1. Moodle
  2. MDL-29262

Moodle 2 backup_controllers table is needlessly massive

    Details

    • Database:
      MySQL
    • Testing Instructions:
      Hide

      NOTE: Testing this requires DB access.
      NOTE: This needs to be tested under 21_STABLE, 22_STABLE and master

      1) With the patch applied, go to admin -> courses -> backup -> general

      2) TEST: Verify that a new setting (backup | loglifetime) is shown and, if upgrade has happened, it shows, by default 30 days.

      3) Change it to 7 days and save changes.

      4) TEST: Verify the setting has been saved ok (7 days).

      5) Look to the contents of the backup_controllers table and annotate the last id there (the table may be empty, annotate 0 in that case).

      6) Perform one backup operation of some course. It doesn't matter if the course is big or no, any course is ok.

      7) TEST: The backup ends ok.

      8) TEST: Look to the contents of the backup_controllers table. One new record must be present there (id +1) and the size of its "controller" column must be 0 (empty string).

      9) Perform one import operation (from one course to another, pick some activity).

      10) TEST: The process ends ok.

      11) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string).

      12) Perform one duplicate activity operation (x2 icon under edit mode).

      13) TEST: The process ends ok.

      14) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string).

      15) Go to admin -> courses -> backup -> automated backups and set the "active" setting to "manual".

      16) CLI execute: admin/cli/automated_backups.php

      17) TEST: The process ends ok.

      18) TEST: A bunch (as many as courses in the site) records have been created in the backup_controllers table. All them with the "controller" column with size 0 (empty string).

      19) Count the number of records in the backup_controllers table.

      20) Update 2 of those records to have timecreated = 1. Annotate their ids.

      21) CLI execute: admin/cli/cron.php (you will need to repeat this execution until getting "Deleted old backup records" on output because the cleanup is performed only 20% - randomly - of the time).

      22) TEST: Look to the backup_controllers table and verify that there are at least 2 records less than then counted @ point 19. Note that the difference may be bigger if you already had records in the table before starting this testing round. Next point will tell us more exactly.

      23) TEST: Verify that the records annotated @ 20 have been deleted from the backup_controllers table.

      24) TEST: Verify that there are no records in the backup_logs table with backupid being the ids annotated.

      25) TEST: Execute controller unit tests:

      • For 21 and 22 (simpletest), in admin -> development -> unit tests, run tests in "backup/controller". Results: 6 passed, 0 exceptions and 0 fails (and no PHP Notice/Warning/Error/Strict problem in output/logs).
      • For master (phpunit), install and configure phpunit and run: "phpunit backup/controller/tests/controller_test.php". Results: OK (1 test, 6 assertions) (and no PHP Notice/Warning/Error/Strict problem in output/logs).

      26) Repeat 1-25 for all fixed branches

      Final note: If testing this under Oracle, note that "empty string" is represented as " " (1-space char), hence the size of the column of some of the tests above will be 1 instead of 0. Only if testing under Oracle.

      Show
      NOTE: Testing this requires DB access. NOTE: This needs to be tested under 21_STABLE, 22_STABLE and master 1) With the patch applied, go to admin -> courses -> backup -> general 2) TEST: Verify that a new setting (backup | loglifetime) is shown and, if upgrade has happened, it shows, by default 30 days. 3) Change it to 7 days and save changes. 4) TEST: Verify the setting has been saved ok (7 days). 5) Look to the contents of the backup_controllers table and annotate the last id there (the table may be empty, annotate 0 in that case). 6) Perform one backup operation of some course. It doesn't matter if the course is big or no, any course is ok. 7) TEST: The backup ends ok. 8) TEST: Look to the contents of the backup_controllers table. One new record must be present there (id +1) and the size of its "controller" column must be 0 (empty string). 9) Perform one import operation (from one course to another, pick some activity). 10) TEST: The process ends ok. 11) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string). 12) Perform one duplicate activity operation (x2 icon under edit mode). 13) TEST: The process ends ok. 14) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string). 15) Go to admin -> courses -> backup -> automated backups and set the "active" setting to "manual". 16) CLI execute: admin/cli/automated_backups.php 17) TEST: The process ends ok. 18) TEST: A bunch (as many as courses in the site) records have been created in the backup_controllers table. All them with the "controller" column with size 0 (empty string). 19) Count the number of records in the backup_controllers table. 20) Update 2 of those records to have timecreated = 1. Annotate their ids. 21) CLI execute: admin/cli/cron.php (you will need to repeat this execution until getting "Deleted old backup records" on output because the cleanup is performed only 20% - randomly - of the time). 22) TEST: Look to the backup_controllers table and verify that there are at least 2 records less than then counted @ point 19. Note that the difference may be bigger if you already had records in the table before starting this testing round. Next point will tell us more exactly. 23) TEST: Verify that the records annotated @ 20 have been deleted from the backup_controllers table. 24) TEST: Verify that there are no records in the backup_logs table with backupid being the ids annotated. 25) TEST: Execute controller unit tests: For 21 and 22 (simpletest), in admin -> development -> unit tests, run tests in "backup/controller". Results: 6 passed, 0 exceptions and 0 fails (and no PHP Notice/Warning/Error/Strict problem in output/logs). For master (phpunit), install and configure phpunit and run: "phpunit backup/controller/tests/controller_test.php". Results: OK (1 test, 6 assertions) (and no PHP Notice/Warning/Error/Strict problem in output/logs). 26) Repeat 1-25 for all fixed branches Final note: If testing this under Oracle, note that "empty string" is represented as " " (1-space char), hence the size of the column of some of the tests above will be 1 instead of 0. Only if testing under Oracle.
    • Workaround:
      Hide

      Having searched the forums and tracker it seems I can probably truncate this table (when a backup isn't running) and cause no problems, but it's likely that this table will grow to excessive size again.

      Show
      Having searched the forums and tracker it seems I can probably truncate this table (when a backup isn't running) and cause no problems, but it's likely that this table will grow to excessive size again.
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Pull from Repository:
    • Pull Master Branch:
    • Rank:
      18805

      Description

      Most relevant forum post: http://moodle.org/mod/forum/discuss.php?d=172859
      Most likely related tracker entry: http://tracker.moodle.org/browse/MDL-29138

      Having done some final checks of our new Moodle 2 install prior to teaching commencing in a few days' time, I noticed that the database was 3.4Gb in size. This was a surprise as our previous Moodle 1.9 install had three times as many courses and well over 20,000 users and had been in place for over 5 years, and it's database ended up at 1.1Gb.

      Closer inspection revealed that the backup_controllers table was 3.0Gb in size and that the actual database size was more like 400Mb.

        Issue Links

          Activity

          Paul Vaughan created issue -
          Michael de Raadt made changes -
          Field Original Value New Value
          Fix Version/s STABLE backlog [ 10463 ]
          Labels triaged
          Michael de Raadt made changes -
          Link This issue will help resolve MDL-29138 [ MDL-29138 ]
          Ray Lawrence made changes -
          Labels triaged partner triaged
          Paul Vaughan made changes -
          Affects Version/s 2.1.2 [ 10851 ]
          Paul Vaughan made changes -
          Component/s Backup [ 10068 ]
          Paul Vaughan made changes -
          Priority Minor [ 4 ] Major [ 3 ]
          Chris Follin made changes -
          Labels partner triaged moodlerooms partner triaged
          Michael de Raadt made changes -
          Priority Major [ 3 ] Critical [ 2 ]
          Paul Vaughan made changes -
          Labels moodlerooms partner triaged backup moodlerooms partner triaged
          Eloy Lafuente (stronk7) made changes -
          Status Open [ 1 ] Development in progress [ 3 ]
          Chris Follin made changes -
          Affects Version/s 2.2 [ 10656 ]
          Ray Lawrence made changes -
          Labels backup moodlerooms partner triaged backup howtomoodle moodlerooms partner triaged
          Adam Olley made changes -
          Labels backup howtomoodle moodlerooms partner triaged backup howtomoodle moodlerooms netspot partner triaged
          Eloy Lafuente (stronk7) made changes -
          Status Development in progress [ 3 ] Waiting for integration review [ 10010 ]
          Pull Master Diff URL https://github.com/stronk7/moodle/compare/master...MDL-29262
          Pull Master Branch MDL-29262
          Pull from Repository git://github.com/stronk7/moodle.git
          Fix Version/s 2.1.6 [ 12052 ]
          Fix Version/s 2.2.3 [ 12053 ]
          Fix Version/s STABLE backlog [ 10463 ]
          Testing Instructions Unsure, use Moodle normally, restore a lot of courses, or back up a lot of courses, regularly.

          I'm not sure if this is relevant but we restored close to 500 courses from other Moodle 1.9 and 2.0 installations, and are currently backing up 560 courses twice per week.
          NOTE: Testing this requires DB access.
          NOTE: This needs to be tested under 21_STABLE, 22_STABLE and master

          1) With the patch applied, go to admin -> courses -> backup -> general

          2) TEST: Verify that a new setting (backup | loglifetime) is shown and, if upgrade has happened, it shows, by default 30 days.

          3) Change it to 7 days and save changes.

          4) TEST: Verify the setting has been saved ok (7 days).

          5) Look to the contents of the backup_controllers table and annotate the last id there (the table may be empty, annotate 0 in that case).

          6) Perform one backup operation of some course. It doesn't matter if the course is big or no, any course is ok.

          7) TEST: The backup ends ok.

          8) TEST: Look to the contents of the backup_controllers table. One new record must be present there (id +1) and the size of its "controller" column must be 0 (empty string).

          9) Perform one import operation (from one course to another, pick some activity).

          10) TEST: The process ends ok.

          11) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string).

          12) Perform one duplicate activity operation (x2 icon under edit mode).

          13) TEST: The process ends ok.

          14) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string).

          15) Go to admin -> courses -> backup -> automated backups and set the "active" setting to "manual".

          16) CLI execute: admin/cli/automated_backups.php

          17) TEST: The process ends ok.

          18) TEST: A bunch (as many as courses in the site) records have been created in the backup_controllers table. All them with the "controller" column with size 0 (empty string).

          19) Count the number of records in the backup_controllers table.

          20) Update 2 of those records to have timecreated = 1. Annotate their ids.

          21) CLI execute: admin/cli/cron.php (you will need to repeat this execution until getting "Deleted old backup records" on output because the cleanup is performed only 20% - randomly - of the time).

          22) TEST: Look to the backup_controllers table and verify that there are at least 2 records less than then counted @ point 19. Note that the difference may be bigger if you already had records in the table before starting this testing round. Next point will tell us more exactly.

          23) TEST: Verify that the records annotated @ 20 have been deleted from the backup_controllers table.

          24) TEST: Verify that there are no records in the backup_logs table with backupid being the ids annotated.

          25) Repeat 1-24 for all fixed branches

          Final note: If testing this under Oracle, note that "empty string" is represented as " " (1-space char), hence the size of the column of some of the tests above will be 1 instead of 0. Only if testing under Oracle.

          Pull 2.1 Branch MDL-29262_21
          Pull 2.2 Diff URL https://github.com/stronk7/moodle/compare/MOODLE_22_STABLE...MDL-29262_22
          Pull 2.1 Diff URL https://github.com/stronk7/moodle/compare/MOODLE_21_STABLE...MDL-29262_21
          Pull 2.2 Branch MDL-29262_22
          Eloy Lafuente (stronk7) made changes -
          Labels backup howtomoodle moodlerooms netspot partner triaged backup docs_required howtomoodle moodlerooms netspot partner triaged ui_change
          Eloy Lafuente (stronk7) made changes -
          Currently in integration Yes [ 10041 ]
          Dan Poltawski made changes -
          Status Waiting for integration review [ 10010 ] Integration review in progress [ 10004 ]
          Integrator poltawski
          Dan Poltawski made changes -
          Status Integration review in progress [ 10004 ] Waiting for testing [ 10005 ]
          Eloy Lafuente (stronk7) made changes -
          Testing Instructions NOTE: Testing this requires DB access.
          NOTE: This needs to be tested under 21_STABLE, 22_STABLE and master

          1) With the patch applied, go to admin -> courses -> backup -> general

          2) TEST: Verify that a new setting (backup | loglifetime) is shown and, if upgrade has happened, it shows, by default 30 days.

          3) Change it to 7 days and save changes.

          4) TEST: Verify the setting has been saved ok (7 days).

          5) Look to the contents of the backup_controllers table and annotate the last id there (the table may be empty, annotate 0 in that case).

          6) Perform one backup operation of some course. It doesn't matter if the course is big or no, any course is ok.

          7) TEST: The backup ends ok.

          8) TEST: Look to the contents of the backup_controllers table. One new record must be present there (id +1) and the size of its "controller" column must be 0 (empty string).

          9) Perform one import operation (from one course to another, pick some activity).

          10) TEST: The process ends ok.

          11) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string).

          12) Perform one duplicate activity operation (x2 icon under edit mode).

          13) TEST: The process ends ok.

          14) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string).

          15) Go to admin -> courses -> backup -> automated backups and set the "active" setting to "manual".

          16) CLI execute: admin/cli/automated_backups.php

          17) TEST: The process ends ok.

          18) TEST: A bunch (as many as courses in the site) records have been created in the backup_controllers table. All them with the "controller" column with size 0 (empty string).

          19) Count the number of records in the backup_controllers table.

          20) Update 2 of those records to have timecreated = 1. Annotate their ids.

          21) CLI execute: admin/cli/cron.php (you will need to repeat this execution until getting "Deleted old backup records" on output because the cleanup is performed only 20% - randomly - of the time).

          22) TEST: Look to the backup_controllers table and verify that there are at least 2 records less than then counted @ point 19. Note that the difference may be bigger if you already had records in the table before starting this testing round. Next point will tell us more exactly.

          23) TEST: Verify that the records annotated @ 20 have been deleted from the backup_controllers table.

          24) TEST: Verify that there are no records in the backup_logs table with backupid being the ids annotated.

          25) Repeat 1-24 for all fixed branches

          Final note: If testing this under Oracle, note that "empty string" is represented as " " (1-space char), hence the size of the column of some of the tests above will be 1 instead of 0. Only if testing under Oracle.

          NOTE: Testing this requires DB access.
          NOTE: This needs to be tested under 21_STABLE, 22_STABLE and master

          1) With the patch applied, go to admin -> courses -> backup -> general

          2) TEST: Verify that a new setting (backup | loglifetime) is shown and, if upgrade has happened, it shows, by default 30 days.

          3) Change it to 7 days and save changes.

          4) TEST: Verify the setting has been saved ok (7 days).

          5) Look to the contents of the backup_controllers table and annotate the last id there (the table may be empty, annotate 0 in that case).

          6) Perform one backup operation of some course. It doesn't matter if the course is big or no, any course is ok.

          7) TEST: The backup ends ok.

          8) TEST: Look to the contents of the backup_controllers table. One new record must be present there (id +1) and the size of its "controller" column must be 0 (empty string).

          9) Perform one import operation (from one course to another, pick some activity).

          10) TEST: The process ends ok.

          11) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string).

          12) Perform one duplicate activity operation (x2 icon under edit mode).

          13) TEST: The process ends ok.

          14) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string).

          15) Go to admin -> courses -> backup -> automated backups and set the "active" setting to "manual".

          16) CLI execute: admin/cli/automated_backups.php

          17) TEST: The process ends ok.

          18) TEST: A bunch (as many as courses in the site) records have been created in the backup_controllers table. All them with the "controller" column with size 0 (empty string).

          19) Count the number of records in the backup_controllers table.

          20) Update 2 of those records to have timecreated = 1. Annotate their ids.

          21) CLI execute: admin/cli/cron.php (you will need to repeat this execution until getting "Deleted old backup records" on output because the cleanup is performed only 20% - randomly - of the time).

          22) TEST: Look to the backup_controllers table and verify that there are at least 2 records less than then counted @ point 19. Note that the difference may be bigger if you already had records in the table before starting this testing round. Next point will tell us more exactly.

          23) TEST: Verify that the records annotated @ 20 have been deleted from the backup_controllers table.

          24) TEST: Verify that there are no records in the backup_logs table with backupid being the ids annotated.

          25) TEST: Execute controller unit tests:
              - For 21 and 22 (simpletest), in admin -> development -> unit tests, run tests in "backup/controller". Results: 6 passed, 0 exceptions and 0 fails (and no PHP Notice/Warning/Error/Strict problem in output/logs).
              - For master (phpunit), install and configure phpunit and run: "phpunit backup/controller/tests/controller_test.php". Results: OK (1 test, 6 assertions)

          26) Repeat 1-25 for all fixed branches

          Final note: If testing this under Oracle, note that "empty string" is represented as " " (1-space char), hence the size of the column of some of the tests above will be 1 instead of 0. Only if testing under Oracle.

          Eloy Lafuente (stronk7) made changes -
          Testing Instructions NOTE: Testing this requires DB access.
          NOTE: This needs to be tested under 21_STABLE, 22_STABLE and master

          1) With the patch applied, go to admin -> courses -> backup -> general

          2) TEST: Verify that a new setting (backup | loglifetime) is shown and, if upgrade has happened, it shows, by default 30 days.

          3) Change it to 7 days and save changes.

          4) TEST: Verify the setting has been saved ok (7 days).

          5) Look to the contents of the backup_controllers table and annotate the last id there (the table may be empty, annotate 0 in that case).

          6) Perform one backup operation of some course. It doesn't matter if the course is big or no, any course is ok.

          7) TEST: The backup ends ok.

          8) TEST: Look to the contents of the backup_controllers table. One new record must be present there (id +1) and the size of its "controller" column must be 0 (empty string).

          9) Perform one import operation (from one course to another, pick some activity).

          10) TEST: The process ends ok.

          11) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string).

          12) Perform one duplicate activity operation (x2 icon under edit mode).

          13) TEST: The process ends ok.

          14) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string).

          15) Go to admin -> courses -> backup -> automated backups and set the "active" setting to "manual".

          16) CLI execute: admin/cli/automated_backups.php

          17) TEST: The process ends ok.

          18) TEST: A bunch (as many as courses in the site) records have been created in the backup_controllers table. All them with the "controller" column with size 0 (empty string).

          19) Count the number of records in the backup_controllers table.

          20) Update 2 of those records to have timecreated = 1. Annotate their ids.

          21) CLI execute: admin/cli/cron.php (you will need to repeat this execution until getting "Deleted old backup records" on output because the cleanup is performed only 20% - randomly - of the time).

          22) TEST: Look to the backup_controllers table and verify that there are at least 2 records less than then counted @ point 19. Note that the difference may be bigger if you already had records in the table before starting this testing round. Next point will tell us more exactly.

          23) TEST: Verify that the records annotated @ 20 have been deleted from the backup_controllers table.

          24) TEST: Verify that there are no records in the backup_logs table with backupid being the ids annotated.

          25) TEST: Execute controller unit tests:
              - For 21 and 22 (simpletest), in admin -> development -> unit tests, run tests in "backup/controller". Results: 6 passed, 0 exceptions and 0 fails (and no PHP Notice/Warning/Error/Strict problem in output/logs).
              - For master (phpunit), install and configure phpunit and run: "phpunit backup/controller/tests/controller_test.php". Results: OK (1 test, 6 assertions)

          26) Repeat 1-25 for all fixed branches

          Final note: If testing this under Oracle, note that "empty string" is represented as " " (1-space char), hence the size of the column of some of the tests above will be 1 instead of 0. Only if testing under Oracle.

          NOTE: Testing this requires DB access.
          NOTE: This needs to be tested under 21_STABLE, 22_STABLE and master

          1) With the patch applied, go to admin -> courses -> backup -> general

          2) TEST: Verify that a new setting (backup | loglifetime) is shown and, if upgrade has happened, it shows, by default 30 days.

          3) Change it to 7 days and save changes.

          4) TEST: Verify the setting has been saved ok (7 days).

          5) Look to the contents of the backup_controllers table and annotate the last id there (the table may be empty, annotate 0 in that case).

          6) Perform one backup operation of some course. It doesn't matter if the course is big or no, any course is ok.

          7) TEST: The backup ends ok.

          8) TEST: Look to the contents of the backup_controllers table. One new record must be present there (id +1) and the size of its "controller" column must be 0 (empty string).

          9) Perform one import operation (from one course to another, pick some activity).

          10) TEST: The process ends ok.

          11) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string).

          12) Perform one duplicate activity operation (x2 icon under edit mode).

          13) TEST: The process ends ok.

          14) TEST: Two new records must be in the backup_controllers table (backup & restore). Both with the "controller" column with size 0 (empty string).

          15) Go to admin -> courses -> backup -> automated backups and set the "active" setting to "manual".

          16) CLI execute: admin/cli/automated_backups.php

          17) TEST: The process ends ok.

          18) TEST: A bunch (as many as courses in the site) records have been created in the backup_controllers table. All them with the "controller" column with size 0 (empty string).

          19) Count the number of records in the backup_controllers table.

          20) Update 2 of those records to have timecreated = 1. Annotate their ids.

          21) CLI execute: admin/cli/cron.php (you will need to repeat this execution until getting "Deleted old backup records" on output because the cleanup is performed only 20% - randomly - of the time).

          22) TEST: Look to the backup_controllers table and verify that there are at least 2 records less than then counted @ point 19. Note that the difference may be bigger if you already had records in the table before starting this testing round. Next point will tell us more exactly.

          23) TEST: Verify that the records annotated @ 20 have been deleted from the backup_controllers table.

          24) TEST: Verify that there are no records in the backup_logs table with backupid being the ids annotated.

          25) TEST: Execute controller unit tests:
              - For 21 and 22 (simpletest), in admin -> development -> unit tests, run tests in "backup/controller". Results: 6 passed, 0 exceptions and 0 fails (and no PHP Notice/Warning/Error/Strict problem in output/logs).
              - For master (phpunit), install and configure phpunit and run: "phpunit backup/controller/tests/controller_test.php". Results: OK (1 test, 6 assertions) (and no PHP Notice/Warning/Error/Strict problem in output/logs).

          26) Repeat 1-25 for all fixed branches

          Final note: If testing this under Oracle, note that "empty string" is represented as " " (1-space char), hence the size of the column of some of the tests above will be 1 instead of 0. Only if testing under Oracle.

          Michael de Raadt made changes -
          Tester salvetore
          Michael de Raadt made changes -
          Tester salvetore
          Michael de Raadt made changes -
          Tester ankit_frenz
          Ankit Agarwal made changes -
          Status Waiting for testing [ 10005 ] Testing in progress [ 10011 ]
          Ankit Agarwal made changes -
          Status Testing in progress [ 10011 ] Tested [ 10006 ]
          Eloy Lafuente (stronk7) made changes -
          Status Tested [ 10006 ] Closed [ 6 ]
          Resolution Fixed [ 1 ]
          Currently in integration Yes [ 10041 ]
          Integration date 27/Apr/12
          Mary Cooch made changes -
          Documentation link http://docs.moodle.org/23/en/Course_backup
          Labels backup docs_required howtomoodle moodlerooms netspot partner triaged ui_change backup howtomoodle moodlerooms netspot partner triaged ui_change
          Eloy Lafuente (stronk7) made changes -
          Link This issue is duplicated by MDL-29505 [ MDL-29505 ]

            People

            • Votes:
              35 Vote for this issue
              Watchers:
              31 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: