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

Certain actions lead to orphaned questions in the question bank

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.0.2, 2.3.1
    • Fix Version/s: 2.2.5, 2.3.2
    • Component/s: Questions, Quiz
    • Labels:
    • Database:
      Any
    • Testing Instructions:
      Hide

      Test difficulty: difficult

      Test pre-requisites

      • Gold medal if tested on 4 DB engines
      • Create a question 'Question A' in the front page question bank
      • Create a question 'Question B' in a course question bank
      • Create a question 'Question C' in a quiz question bank

      Test 1

      1. Download test.php to your Moodle instance root directory
      2. Manually edit the database record of question A and B (mdl_question)
      3. Set their category field to 0
      4. Run `sudo -u www-data php test.php`
      5. Make sure mdl_question_categories contains a new category called 'Questions saved from deleted categories'
      6. Make sure the category field of question A and B in mdl_question match the ID of the newly created category
      7. Edit the database record of question C, and set category to 0
      8. Run `sudo -u www-data php test.php`
      9. Make sure there is no more than one category called 'Questions saved from deleted categories' in mdl_question_categories
      10. Make sure the category field of question C matches the category 'Questions saved from deleted categories'
      11. As an admin, go to any question bank
      12. Make sure you can view the three questions in the new category at system level

      Test 2

      1. Remove categories/questions created in Test 1, including the category 'Questions saved from deleted categories'
      2. Prepare a Moodle instance that can be upgraded
      3. Edit question A and B category field, and set it to 0
      4. Upgrade Moodle
      5. Go to a question bank
      6. Make sure question A and B appear under a category called 'Questions saved from deleted categories' at system level
      Show
      Test difficulty: difficult Test pre-requisites Gold medal if tested on 4 DB engines Create a question 'Question A' in the front page question bank Create a question 'Question B' in a course question bank Create a question 'Question C' in a quiz question bank Test 1 Download test.php to your Moodle instance root directory Manually edit the database record of question A and B (mdl_question) Set their category field to 0 Run `sudo -u www-data php test.php` Make sure mdl_question_categories contains a new category called 'Questions saved from deleted categories' Make sure the category field of question A and B in mdl_question match the ID of the newly created category Edit the database record of question C, and set category to 0 Run `sudo -u www-data php test.php` Make sure there is no more than one category called 'Questions saved from deleted categories' in mdl_question_categories Make sure the category field of question C matches the category 'Questions saved from deleted categories' As an admin, go to any question bank Make sure you can view the three questions in the new category at system level Test 2 Remove categories/questions created in Test 1, including the category 'Questions saved from deleted categories' Prepare a Moodle instance that can be upgraded Edit question A and B category field, and set it to 0 Upgrade Moodle Go to a question bank Make sure question A and B appear under a category called 'Questions saved from deleted categories' at system level
    • Workaround:
      Hide

      If you are having problems caused by this, then you can use Admin -> XMLDB -> Check foreign keys to find those questions where the corresponding category has been deleted, and those questions categories where the corresponding context no longer exists. (In a healthy Moodle site there should be none of these.)

      Then, you can manually go into the database and repair those foreign keys as best you can, for example by pointing the question at a category id that does exist, and pointing categories at a suitable context that does exist.

      Show
      If you are having problems caused by this, then you can use Admin -> XMLDB -> Check foreign keys to find those questions where the corresponding category has been deleted, and those questions categories where the corresponding context no longer exists. (In a healthy Moodle site there should be none of these.) Then, you can manually go into the database and repair those foreign keys as best you can, for example by pointing the question at a category id that does exist, and pointing categories at a suitable context that does exist.
    • Affected Branches:
      MOODLE_20_STABLE, MOODLE_23_STABLE
    • Fixed Branches:
      MOODLE_22_STABLE, MOODLE_23_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-27083-master

      Description

      We've found a series of actions that can lead to broken quizzes and loss of questions

      To reproduce

      1) Create a new quiz. This quiz has initially no questions and in my case gets an id=45;

      select id,name,questions from mdl_quiz where name like "Test%";

      id name questions
      45 Test quiz  

      2) Choose a spare category (or create it accordingly).
      you can see in my case this new category has id=544:

      select id,name from mdl_question_categories where name like "Test%";

      id name
      544 Test category - to be deleted

      3) Create a test question belonging to this category. In my case this question gets an id=10418, and is assigned the right category:

      select id,category,name from mdl_question where name like "Test%";

      id category name
      10418 544 Test question - short answer type

      4) Add this question to the quiz. You can see the question is correctly added to the quiz:

      select id,name,questions from mdl_quiz where name like "Test%";

      id name questions
      45 Test quiz 10418,0

      5) Now the malfunctioning part: Delete the recently created category. Moodle will ask you to select another category to move the Test question to. Choose any existing category.

      THIS SEEMS TO WORK, but in fact it doesn't. What happen is: category is deleted BUT affected questions are not assigned to the new category, so they become orphaned from a category point of view:

      select id,category,name from mdl_question where id=10418;

      id category name
      10418 544 Test question - short answer type

      BUT:

      select id,name from mdl_question_categories where id=544;
      Empty set (0.00 sec)

      So category is in fact deleted but affected question retains the old category id.

      This situation leads to 2 major problems:

      1) Questions inside the deleted category aren't in fact moved into the indicated category, but they are "lost" inside the database and cannot be further accessed. -> So DATA LOSS.

      2) Affected quizzes cease to work, as they have questions belonging to a non-existent category. The attached picture shows that the test quiz has one question inserted but it's not shown due to this problem. So if you try to preview or attempt these quizzes, you get a "Category ID is invalid" error. The only solution is to delete the offending quiz and to regenerate it. Well, you can also go to the database and correct the wrong category id of the affected questions, but clearly this isn't an option for everybody.

        Gliffy Diagrams

          Attachments

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  10/Sep/12