Moodle
  1. Moodle
  2. MDL-14750

Multianswer questions get broken by backup and restore

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.8.5, 1.9
    • Fix Version/s: 1.6.7, 1.7.5, 1.8.6, 1.9.1
    • Component/s: Questions
    • Labels:
      None
    • Affected Branches:
      MOODLE_18_STABLE, MOODLE_19_STABLE
    • Fixed Branches:
      MOODLE_16_STABLE, MOODLE_17_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE
    • Rank:
      30929

      Description

      When multianswer questions are backed up then restored, the question.parent field is not correctly recoded during the restore.

      Fortunately, there is enough information in the mdl_question_multianswer.sequence field that we should be able to fix up any existing broken questions. See http://docs.moodle.org/en/Development:Question_bank_consistency_check for some thoughts.

        Activity

        Hide
        Tim Hunt added a comment -

        I think this function is what we need to fix existing problems in the database:

        /**

        • Due to MDL-14750, subquestions of multianswer questions restored from backup will
        • have the wrong category and parent, and due to MDL-10899 subquestions of multianswer questions
        • that have been moved between categories will be in the wrong category, This code fixes these up.
          */
          function question_multianswer_fix_subquestion_parents_and_categories() {
          $result = true;
          $rs = get_recordset_sql('SELECT q.id, q.category, maq.sequence FROM ' . $CFG->prefix .
          'question q JOIN ' . $CFG->prefix . 'question_multianswer qma ON q.id = qma.question');
          if ($rs)
          Unknown macro: { while ($q = rs_fetch_next_record($rs)) { $result = $result && execute_sql('UPDATE ' . $CFG->prefix . 'question SET parent = ' . $q->id . ', category = ' . $q->category . ' WHERE id IN (' . $q->sequence . ')'); } rs_close($rs); }

          return $result;
          }

        Show
        Tim Hunt added a comment - I think this function is what we need to fix existing problems in the database: /** Due to MDL-14750 , subquestions of multianswer questions restored from backup will have the wrong category and parent, and due to MDL-10899 subquestions of multianswer questions that have been moved between categories will be in the wrong category, This code fixes these up. */ function question_multianswer_fix_subquestion_parents_and_categories() { $result = true; $rs = get_recordset_sql('SELECT q.id, q.category, maq.sequence FROM ' . $CFG->prefix . 'question q JOIN ' . $CFG->prefix . 'question_multianswer qma ON q.id = qma.question'); if ($rs) Unknown macro: { while ($q = rs_fetch_next_record($rs)) { $result = $result && execute_sql('UPDATE ' . $CFG->prefix . 'question SET parent = ' . $q->id . ', category = ' . $q->category . ' WHERE id IN (' . $q->sequence . ')'); } rs_close($rs); } return $result; }
        Hide
        Tim Hunt added a comment -

        This patch should fix the restore problem. Reviews welcome. I have not tested it yet, but I am about to.

        Show
        Tim Hunt added a comment - This patch should fix the restore problem. Reviews welcome. I have not tested it yet, but I am about to.
        Hide
        Tim Hunt added a comment -

        After testing, this fix seems to work so checking it in. I found and fixed a regression in 1.9 caused by the fix for MDL-5482 along the way though.

        Show
        Tim Hunt added a comment - After testing, this fix seems to work so checking it in. I found and fixed a regression in 1.9 caused by the fix for MDL-5482 along the way though.
        Hide
        Bryce Thornton added a comment -

        This fix caused an error when I upgraded my installation. Here is the text from the error:

        (mysql): UPDATE mdl_question SET parent = 71895, category = 661 WHERE id IN () AND parent <> 0 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND parent <> 0' at line 1

        ADOConnection._Execute(UPDATE mdl_question SET parent = 71895, category = 661 WHERE id IN () AND parent <> 0, false) % line 891, file: adodb.inc.php
        ADOConnection.Execute(UPDATE mdl_question SET parent = 71895, category = 661 WHERE id IN () AND parent <> 0) % line 89, file: dmllib.php
        execute_sql(UPDATE mdl_question SET parent = 71895, category = 661 WHERE id IN () AND parent <> 0) % line 57, file: upgrade.php
        question_multianswer_fix_subquestion_parents_and_categories() % line 36, file: upgrade.php
        xmldb_qtype_multianswer_upgrade(2006032200) % line 188, file: adminlib.php

        I've checked and I do have some question_multianswer records without a sequence. I'm not sure how this came to happen, but you might want to add a check for this to ensure the error doesn't happen.

        Show
        Bryce Thornton added a comment - This fix caused an error when I upgraded my installation. Here is the text from the error: (mysql): UPDATE mdl_question SET parent = 71895, category = 661 WHERE id IN () AND parent <> 0 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND parent <> 0' at line 1 ADOConnection._Execute(UPDATE mdl_question SET parent = 71895, category = 661 WHERE id IN () AND parent <> 0, false) % line 891, file: adodb.inc.php ADOConnection.Execute(UPDATE mdl_question SET parent = 71895, category = 661 WHERE id IN () AND parent <> 0) % line 89, file: dmllib.php execute_sql(UPDATE mdl_question SET parent = 71895, category = 661 WHERE id IN () AND parent <> 0) % line 57, file: upgrade.php question_multianswer_fix_subquestion_parents_and_categories() % line 36, file: upgrade.php xmldb_qtype_multianswer_upgrade(2006032200) % line 188, file: adminlib.php I've checked and I do have some question_multianswer records without a sequence. I'm not sure how this came to happen, but you might want to add a check for this to ensure the error doesn't happen.
        Hide
        Tim Hunt added a comment -

        I think you can only get that error if you have broken questions in your database. However, the code needs to be robust, so I have changed it to prevent this error. Try updating /question/type/multianswer/db/upgrade.php to the latest version from the 1.9 branch.

        Show
        Tim Hunt added a comment - I think you can only get that error if you have broken questions in your database. However, the code needs to be robust, so I have changed it to prevent this error. Try updating /question/type/multianswer/db/upgrade.php to the latest version from the 1.9 branch.
        Hide
        Bryce Thornton added a comment -

        Looks good. Thanks!

        Show
        Bryce Thornton added a comment - Looks good. Thanks!
        Hide
        Oleg Sychev added a comment -

        I already described our situation in MDL-14625: the course was backuped, then restored as a new course and old course was deleted.

        I restore questions by manual editing using Pierre's patch, but after upgrading to latest version of 1.9. and applying you patch during upgrade process we get another problem: we can't review attempts, that were attempted in old course, before backuping. The grading remained, but the review.php now show message that question was deleted for all questions.

        Show
        Oleg Sychev added a comment - I already described our situation in MDL-14625 : the course was backuped, then restored as a new course and old course was deleted. I restore questions by manual editing using Pierre's patch, but after upgrading to latest version of 1.9. and applying you patch during upgrade process we get another problem: we can't review attempts, that were attempted in old course, before backuping. The grading remained, but the review.php now show message that question was deleted for all questions.
        Hide
        Oleg Sychev added a comment -

        I stress that problem was not in multianswer quesitons, that was restored using Pierre's patch, but with all questions. Probably the questions id in attempts points to the questions from old course.

        Show
        Oleg Sychev added a comment - I stress that problem was not in multianswer quesitons, that was restored using Pierre's patch, but with all questions. Probably the questions id in attempts points to the questions from old course.
        Hide
        Tim Hunt added a comment -

        I don't quite understand, you say "old course was deleted", then "we can't review attempts, that were attempted in old course". If the old course was deleted, surely all the attempts were deleted too?

        Show
        Tim Hunt added a comment - I don't quite understand, you say "old course was deleted", then "we can't review attempts, that were attempted in old course". If the old course was deleted, surely all the attempts were deleted too?
        Hide
        Oleg Sychev added a comment -

        Probably I must say 'attempts that were attempted during old course'. The old attempts were backuped and restored with the course Questions in them are not visible, while questions in attempts, which was attempted directly in new course (after restoring) are OK.

        Show
        Oleg Sychev added a comment - Probably I must say 'attempts that were attempted during old course'. The old attempts were backuped and restored with the course Questions in them are not visible, while questions in attempts, which was attempted directly in new course (after restoring) are OK.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: