There really should not be qtype_random instances any more.
The Moodle 4.0 & 4.3 changes were supposed to finally phase out the use of this fake quetion type. The randomisation feature in quizzes is now handled in a completely different way. But something must have been missed somewhere. The qtype_random plugin still exists in the code, but that should only be to support restore of old backups ... and that is probably where the problem lies.
I have just checked our Moodle db, and we seem to have unnecessary 40,000 random questions too.
I believe (but am not yet confident) that it is completely safe to run the three clean-up queries
DELETE FROM mdl_question_bank_entries
WHERE id IN (
SELECT qv.questionbankentryid
FROM mdl_question_versions qv
JOIN mdl_question q ON q.id = qv.questionid
WHERE q.qtype = 'random'
)
DELETE FROM mdl_question_versions qv
WHERE questionid IN (
SELECT id FROM mdl_question q WHERE q.qtype = 'random'
)
DELETE FROM mdl_question WHERE qtype = 'random'
Note, you can get some confidence that nothing is referring to these random questions by running the following 2 queries, which should both return 0 rows.
SELECT *
FROM mdl_question_attempts qa
JOIN mdl_question q ON q.id = qa.questionid
WHERE q.qtype = 'random'
SELECT *
FROM mdl_question_references qr
JOIN mdl_question_versions qv ON qv.questionbankentryid = qr.questionbankentryid
JOIN mdl_question q ON q.id = qv.questionid
WHERE q.qtype = 'random'
To see how many random questions you have in your database, the query is
SELECT COUNT(1) FROM mdl_question WHERE qtype = 'random'
For now, please only try this clean-up if you know what you are doing, and re able to test it first. (I have just tried it on our test server - fortuanately we are in a period of testing at the moment.)
If anyone can track down where these "should not exist" random questions are coming from, please tell us. This is a bug that needs to be fixed.
I have tried your queries into our production database with Moodle 4.1.9 (Build: 20240212), and present these numbers:
mysql> SELECT COUNT(1) FROM mdl_question WHERE qtype = 'random'; +----------+ | COUNT(1) | +----------+ | 154052 | +----------+ 1 row in set (0.11 sec) mysql> SELECT * -> FROM mdl_question_attempts qa -> JOIN mdl_question q ON q.id = qa.questionid -> WHERE q.qtype = 'random'; Empty set (16.93 sec) mysql> SELECT * -> FROM mdl_question_references qr -> JOIN mdl_question_versions qv ON qv.questionbankentryid = qr.questionbankentryid -> JOIN mdl_question q ON q.id = qv.questionid -> WHERE q.qtype = 'random'; Empty set (22.76 sec)
Thanks a lot,
Jordi
Thank you for sharing that data.
I am afraid I cannot absolutly guarantee that it is safe to delete those random questions. What I can say is:
- I spent a bit of time (maybe 30 mins) analysing the code, and I beleive it is safe to delete that data.
- Last friday I ran the clean-up queries on our test server. Since then, we have not spotted any probems, and right now we are in the middle of testing a bunch of quiz things.
- My plan is, at some point, I will either a) do the delete our our production database; but more likely b) develop some Moodle upgrade code in Moodle core, to automatically clean up everyone's database.