7.5 million duplicate questions: how I rescued a Moodle course without wrecking another one's exams

Some Moodle upgrades take half an hour in the lab and teach you humility in production. This is the full story of one of the second kind: it started with an upgrade task that would never finish, and ended up as a forensic investigation into the database that uncovered a months-old restore bug affecting real exams from a completely different course.

The first warning: the mod_qbank upgrade gets stuck #

Since the switch from the traditional question bank to the mod_qbank activity (shareable banks), the bank no longer lives “just” in the course/category/site context. After the upgrade, the ad-hoc task mod_qbank\task\transfer_question_categories runs (and, for each moved category, transfer_questions), which selects every question in a category at once to move files and tags. On a normal course you barely notice. On a context with millions of questions, that query builds an IN (?, ?, …) so large that PostgreSQL refuses to run it.

This isn’t a click-through-the-wizard post. It’s the diary of a large university production site (fat banks, years of course copies) where we already lived with another bank wound — duplicate stamps, which don’t break anything on their own but turn into a performance problem once the bank grows into the millions — and where the switch to the new question bank model exposed that same volume problem, now at a much bigger scale.

The PostgreSQL failure: too many parameters #

PostgreSQL and the driver cap the number of bound parameters per statement (the ballpark is usually around 65,535 binds; the exact number depends on version and code path). An IN (?, ?, …) with hundreds of thousands of placeholders isn’t a reasonable query: the server rejects it.

The symptoms in the cron logs: the transfer_question_categories or transfer_questions task stays failed, the admin sees the notice that pre-existing banks haven’t been transferred to mod_qbank instances yet, and underneath, a “too many parameters” error preparing the statement.

PostgreSQL isn’t the villain. The villain is assuming “every ID in this category fits in one placeholder IN”. On a campus with one pathological context, that assumption is false.

How you get to “millions of questions” in one place #

A teacher doesn’t need to have typed a million items: the problem is duplicates. Every course restore, every quiz duplication, every copy of a question category from one academic year to the next generates a new copy of the questions instead of reusing the ones that already exist. Repeat that for years, with the same template course restored over and over — and, looked at case by case, each individual restore or duplication seems to go perfectly fine. The same handful of original questions multiplies on its own. The courseid looks like an ordinary course in the UI; in mdl_question*, after years of this loop, it’s a black hole.

The course that refused to be deleted #

With the upgrade blocked, it was time to deal directly with the context that was breaking it: a test course called “Question junk course” that, on top of everything, also refused to be deleted outright.

We had a CLI cleanup script for duplicate questions (one of those community scripts that circulate for Moodle) meant to detect and merge duplicate questions within a course context. Running it in --dryrun mode:

php cleanup_duplicates_context_cli_global_dp_parent_fix.php --courseid=55914 --dryrun
[...]
SQL query completed, starting to process results...
Fatal error: Allowed memory size of 8589934592 bytes exhausted (tried to allocate 20480 bytes)
in /var/www/aula/moodle/lib/dml/pgsql_native_moodle_database.php on line 1057

8 GB of PHP memory exhausted, failing to allocate 20 more KB. That’s not “there are a lot of duplicates” — that’s something gone seriously wrong. And it was the same underlying pattern as the upgrade failure: the script loaded every question in the context into a PHP array at once, instead of processing them one by one with a database cursor.

First diagnosis: how much is “a lot”? #

Before touching anything, I wanted the real magnitude with direct SQL:

1SELECT COUNT(*)
2FROM mdl_question q
3JOIN mdl_question_versions qv ON qv.questionid = q.id
4JOIN mdl_question_bank_entries qbe ON qbe.id = qv.questionbankentryid
5JOIN mdl_question_categories qc ON qc.id = qbe.questioncategoryid
6WHERE qc.contextid = <course_context>
7   OR qc.contextid IN (SELECT id FROM mdl_context WHERE path LIKE '<path>/%');

7,594,560 questions. In a single course. No real course has that — it was the unmistakable signature of a runaway restore/duplication loop, and the very same monster that had been breaking the mod_qbank upgrade task.

With that figure, loading everything into PHP to compare question by question (exactly what the script did, and what Moodle itself does internally when deleting a course) was never going to work, no matter how much RAM you throw at it. This had to be purged directly in the database, bypassing the application layer entirely.

The twist: check before deleting #

Before wiping out 7.5M rows, I asked the mandatory question: is anything hooked into this that shouldn’t be lost? Specifically, does any real exam attempt reference any of these questions?

1SELECT COUNT(*)
2FROM mdl_question_attempts qa
3WHERE qa.questionid IN (
4    -- every question in the suspicious context
5);

Result: 588. Not zero. Tracing which course those attempts belonged to:

1SELECT DISTINCT c.id, c.fullname, COUNT(*) AS n_attempts
2FROM mdl_question_attempts qa
3JOIN mdl_question_usages qu ON qu.id = qa.questionusageid
4JOIN mdl_context ctx ON ctx.id = qu.contextid AND ctx.contextlevel = 70
5JOIN mdl_course_modules cm ON cm.id = ctx.instanceid
6JOIN mdl_course c ON c.id = cm.course
7WHERE qa.questionid IN (...)
8GROUP BY c.id, c.fullname;

The 588 attempts belonged to a completely different course, a real, active one. Its questions were physically housed inside the context of the junk course I was about to destroy. Had I purged without checking this, I would have broken exam review for real students.

Tracing the exact ids, it turned out to be only 50 distinct questions (reused many times across the 588 attempts), spread across two categories.

The broken-tree clue #

Investigating those two categories, I found something revealing: one category’s parent field pointed to a category that, checking its row directly, lived in the correct course’s context (the one with the real exams), while the category itself had its contextid pointing at the junk course. In other words: the hierarchical pointer (parent) was correct; the field that says “which course you belong to” (contextid) was corrupted.

That was proof the bug didn’t just duplicate questions — at some point during a botched restore/duplication, it had also relocated entire categories across contexts without correctly updating the whole chain. The “junk” course wasn’t random junk: it was the accumulated dumping ground of repeated restores of real content — and that exact dumping ground was what had been blocking the whole site’s upgrade to mod_qbank.

The rescue #

With that clear, I moved the two categories back to their correct context:

1UPDATE mdl_question_categories
2SET contextid = <correct_context>,
3    parent = <correct_top_category>
4WHERE id IN (<cat_a>, <cat_b>);

And verified the only way that matters: opening several real attempts’ review pages (/mod/quiz/review.php?attempt=<id>) and confirming they loaded correctly.

An extra surprise: those two categories, despite holding only 50 “needed” questions, were dragging 2,331,648 questions in total — the same duplication bug had contaminated them too. They had to be cleaned separately, keeping only the 50 in real use.

The purge: why not use Moodle’s API #

Moodle has an internal function, question_delete_question(), that checks whether a question is in use before deleting it — exactly the safety guarantee I needed. The problem: it checks one question at a time, loading the full object each time. It’s the same “row by row” pattern that was blowing up both the upgrade task and the cleanup script.

Since I’d already done that safety check in bulk, with SQL, against the entire system (not just historical attempts, but also “live” references from quiz slots via mdl_question_references/mdl_question_set_references), I didn’t need to repeat it question by question. I could purge directly via SQL with the same safety guarantee, without the performance cost.

I designed a PL/pgSQL procedure that purges one whole category per call, in batches of 5,000 rows with a COMMIT after each batch (to avoid accumulating one giant transaction):

 1CREATE OR REPLACE PROCEDURE purge_category(p_cat_id integer)
 2LANGUAGE plpgsql
 3AS $$
 4DECLARE
 5  rows_deleted integer;
 6  batch_total integer := 0;
 7BEGIN
 8  LOOP
 9    DELETE FROM mdl_question
10    WHERE id IN (
11      SELECT q.id FROM mdl_question q
12      JOIN mdl_question_versions qv ON qv.questionid = q.id
13      JOIN mdl_question_bank_entries qbe ON qbe.id = qv.questionbankentryid
14      WHERE qbe.questioncategoryid = p_cat_id
15      LIMIT 5000
16    );
17    GET DIAGNOSTICS rows_deleted = ROW_COUNT;
18    batch_total := batch_total + rows_deleted;
19    COMMIT;
20    EXIT WHEN rows_deleted = 0;
21  END LOOP;
22  -- cleanup of question_versions, question_bank_entries and the category...
23END;
24$$;

Category by category, smallest to largest, verifying each one before moving to the next. Tedious but controllable: if something gets cut off mid-run, nothing is lost (each batch is already committed), and you just re-run the same call.

The PostgreSQL lesson: NOT IN vs NOT EXISTS #

Halfway through the purge, a cleanup step that should have taken seconds hung for minutes. The culprit was this seemingly innocent query:

1-- SLOW with large tables
2DELETE FROM mdl_question_versions
3WHERE questionbankentryid IN (...)
4  AND questionid NOT IN (SELECT id FROM mdl_question);

NOT IN with a subquery against a multi-million-row table is a classic PostgreSQL performance trap — the planner doesn’t always find a good plan for it. The fix is switching to NOT EXISTS, which enables a much more efficient anti-join:

1-- FAST
2DELETE FROM mdl_question_versions qv
3WHERE qv.questionbankentryid IN (...)
4  AND NOT EXISTS (SELECT 1 FROM mdl_question q WHERE q.id = qv.questionid);

That change turned a “minutes, might hang” query into a “seconds” one. If you write bulk deletes against large tables in Postgres, avoid NOT IN with subqueries — use NOT EXISTS.

The result #

With the context clean, I re-ran the original cleanup script:

php cleanup_duplicates_context_cli_global_dp_parent_fix.php --courseid=55914 --dryrun
[...]
Found 0 questions, grouping by stamp...
No questions found with the current filter criteria.

Instant, no memory error. And finally, deleting the course:

sudo -u www-data php admin/cli/delete_course.php --courseid=55914 --disablerecyclebin
[...]
++ Deleted - Questions ++
[...]
Done!

The “Deleted - Questions” line — the one that used to blow up the whole process — went through without incident. And with that context out of the picture, the site-wide mod_qbank\task\transfer_question_categories task stopped tripping over it.

What I’m taking from this #

• Upgrading to mod_qbank is a model change, not “export to a new format”: it re-homes categories into activity contexts and moves files and tags. That’s why one pathological context can make the whole thing fail.
• A memory error (or a “too many parameters” one) is almost never “just give it more resources”. It’s almost always the same signal showing up in two different places: something is processing row by row — or a whole category at once — what should be processed in bounded batches.
• Before purging anything at scale, systematically check what depends on it — and do it against the entire system, not just the obvious case. The 588 attempts from another course weren’t visible at a glance.
• “Corrupted” data often tells a story if you look closely — the mismatch between contextid and parent was the clue that explained both why the junk course had real exams inside it and why it had been blocking the upgrade for months.
• NOT IN against large subqueries is a recurring PostgreSQL performance trap. NOT EXISTS is almost always the right alternative.
• Procedures with per-batch COMMITs are your friend when you have to delete millions of rows in a pseudo-resumable way: if something fails halfway through, you don’t lose progress.
• Small sites will never see either of these bugs. That’s why it’s worth writing up: on a large campus with a wild history, sooner or later some context turns into a black hole, and the symptom can show up first as an upgrade failure and later as a memory failure in an apparently unrelated script.