Question bank consistency check

There are certain internal consistency things that should be true in the database, but which may no longer be the case due to past (or present bugs). This page lists some checks you can run, and in some cases gives ways to fix any inconsistencies that are found.

For random questions, question.parent should equal question.id

So the following query should return no records:

SELECT * FROM mdl_question WHERE qtype = 'random' and parent <> id

This will not be true for random questions restored from backup before MDL-5482 was fixed. It should be safe to fix it by running

UPDATE mdl_question SET parent = id WHERE qtype = 'random' and parent <> id

Indeed, this is done automatically on update to 1.9.1.

For multianswer questions, subquestions should have the right parent

For each question whose id is listed in question_multianswer.sequence, its question.parent field should equal question_multianswer.question. That is, the following query should return no records.

SELECT * FROM mdl_question q
    JOIN mdl_question_multianswer qma ON POSITION(',' || q.id || ',' IN ',' || qma.sequence || ',') > 0
WHERE qma.question <> q.parent

(This query uses SQL standard string concatenation and the position function. There are probably some database engines that don't support this properly. This work on Postgres.)

Now that MDL-14750 has been fixed, this should not happen any more, and there is a database upgrade that will be run when you upgrade to 1.9.1 that should fix any old instances of this problem.

For multianswer questions, subquestions should be in the same category as their parents

This test only makes sense if question.parent is correct for all your subquestions, that is, the the previous test passes.

If the previous test passes, then this query should return no records:

SELECT * FROM mdl_question q
    JOIN mdl_question parent_q ON parent_q.id = q.parent
WHERE q.category <> parent_q.category

To fix this, you should change the category of the child questions to match the category of the parent questions.

Only multianswer and random questions should appear as the parent of another question

(Unless someone invents an interesting new question type.) The following query should return no records:

SELECT * FROM mdl_question q
    JOIN mdl_question parent_q ON parent_q.id = q.parent
WHERE parent_q.qtype NOT IN ('random', 'multianswer')