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

Update to random questions during course restore causing database locking

XMLWordPrintable

    • MOODLE_31_STABLE
    • MOODLE_31_STABLE, MOODLE_32_STABLE
    • Hide

      Please test this on a real database (Postgres) and a useless one (MySQL).

      1. Restore the backup file from MDL-45763
      2. Verify that there are no errors during the restore process.
      3. Find the new entries in mdl_question, and confirm that there are two random questions, one with questiontext '0', one with '1'.
      Show
      Please test this on a real database (Postgres) and a useless one (MySQL). Restore the backup file from MDL-45763 Verify that there are no errors during the restore process. Find the new entries in mdl_question, and confirm that there are two random questions, one with questiontext '0', one with '1'.

      A school is backing up courses and restoring into new courses in order to transfer content from the current term to courses for the upcoming term. These restore attempts often fail repeatedly or take up to 30 minutes to complete if they are successful.

      Using MONyog to monitor the database during the course restores consistently reveals locking on the mdl_question table caused by this query:

      UPDATE mdl_question SET questiontext = '0' WHERE qtype = 'random' AND questiontext = '' AND id IN (SELECT bi.newitemid FROM mdl_backup_ids_temp bi WHERE bi.backupid = '4f244f111cef3c2340215c87131f0be2' AND bi.itemname = 'question_created')

      This query was added as part of MDL-45763 to ensure the proper setting of questiontext on random questions. My reading of that issue is that the fix addressed the question creation process to ensure questiontext is set correctly on new random questions and also added the above query to the upgrade code. Thus any random question created since 2.7.1 will be properly configured, and any site that has been upgraded from a release prior to 2.7.1 would have had all random questions updated to the appropriate configuration. Based on this understanding the need to execute this query during a course restore should only occur when restoring a backup created against a site on a version earlier than 2.7.1. Therefore, I would propose that the course restore process check the moodle_backup.xml file to identify the version of the Moodle site from which the course backup was created and only execute the update query on the mdl_question table when the backup originated from prior to 2.7.1.

      STEPS TO REPLICATE
      1. Create a backup of a course containing numerous random questions.
      2. Go to Site administration > Courses > Manage courses and categories.
      3. Locate a course and go to Restore.
      4. Upload the backup file created in step 1 and click Restore.
      5. Select Restore into this course > Merge the backup course into this course.
      6. Proceed through the rest of the restore wizard using the default settings and perform the restore.

      OBSERVED BEHAVIOR:
      Restore takes several minutes to complete or does not complete at all. Monitoring software shows locking occurring on the mdl_question table caused by the query UPDATE mdl_question SET questiontext = '0' WHERE qtype = 'random' AND questiontext = '' AND id IN (SELECT bi.newitemid FROM mdl_backup_ids_temp bi WHERE bi.backupid = '4f244f111cef3c2340215c87131f0be2' AND bi.itemname = 'question_created')

      EXPECTED BEHAVIOR:
      Course restores complete consistently and in a timely fashion by not execute unnecessary database updates.

            timhunt Tim Hunt
            brian.winstead Brian Winstead
            Eric Merrill Eric Merrill
            David Monllaó David Monllaó
            Adrian Greeve Adrian Greeve
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved:

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