Notes on upgrading to 4.2.0 & 4.1.2

[goggo24]

Because of the way the new Question API works many changes had to be made for the new Offlinequiz versions for Moodle 4. The execution of a repair script is now necessary to fix problems caused by those changes in the Offlinequiz versions 4.0 to 4.1.0. Even if you have never used any of the mentioned plugin versions there could be a lot of missing entries in the database and the execution of the script would be helpful anyway.

Attention:
This script takes some time to execute, for example on our pretty powerful instance with 25,000 Offlinequizzes it took around 10 minutes. On not so powerful servers it might take longer.

Instructions for use:
If you use the same question in more than one group - the version will be the same over all groups. This also applies to changes to the version, it will be carried out automatically in all groups where the question has been added!

---

Further information for admins

• If you can afford this downtime we recommend to upgrade Offlinequiz to 4.1.2 (we skip version 4.1.1. due to internal dependencies) or later and execute the upgrade routine only.
• If you think this script takes too much time, you can execute it before upgrading. Just write the statement below into a php-file in the Offlinequiz folder and execute it.
• If you ran into problems during the upgrade you can increase the new plugin version manually in the database and execute this script later. It only fixes errors with versions of questions, it's not necessary for a working Offlinequiz.

<?php
require(__DIR__ . '/../../config.php');
function offlinequiz_fix_question_versions() {
    global $DB;
    //first set all
    $sql = "SELECT DISTINCT gq1.id,gq1.offlinegroupid,  gq2.questionid
                       FROM {offlinequiz_group_questions} gq1
                       JOIN {question_versions} qv1 on qv1.questionid = gq1.questionid
                       JOIN {question_versions} qv2 on qv2.questionbankentryid = qv1.questionbankentryid and qv1.version < qv2.version
                       JOIN {offlinequiz_group_questions} gq2 on gq2.questionid = qv2.questionid and gq2.offlinequizid = gq1.offlinequizid";
    $records = $DB->get_records_sql($sql);
    foreach ($records as $record) {
        $DB->set_field('offlinequiz_group_questions', 'questionid', $record->questionid,['id' => $record->id]);
    }
    $sql = "SELECT qr.id,qv.version FROM {question_references} qr
              JOIN {offlinequiz_group_questions} ogq on ogq.id = qr.itemid
              JOIN {question_versions} qv on qv.questionid = ogq.questionid
              JOIN {question_bank_entries} mbe on mbe.id = qv.questionbankentryid
             WHERE component = 'mod_offlinequiz' and questionarea = 'slot'
               AND qr.version is null or qr.version <> qv.version";
    $records = $DB->get_records_sql($sql);
    foreach ($records as $record) {
        $DB->set_field('question_references', 'version', $record->version,['id' => $record->id]);
    }
 
 
    $sql = "SELECT ogq.id groupquestionid, og.templateusageid templateusageid, qa.id questionattemtid, qa.questionid oldquestionid, ogq.questionid newquestionid
              FROM {offlinequiz_groups} og
              JOIN {question_usages} qu on qu.id = og.templateusageid
              JOIN {offlinequiz_group_questions} ogq on og.id = ogq.offlinegroupid
              JOIN {question_versions} oqv on ogq.questionid = oqv.questionid
              JOIN {question_attempts} qa on qa.questionusageid = qu.id
              JOIN {question_versions} tqv on tqv.questionid = qa.questionid and tqv.questionbankentryid = oqv.questionbankentryid
             WHERE qa.questionid <> ogq.questionid";
    $records = $DB->get_records_sql($sql);
    foreach ($records as $record) {
        $templateusage = question_engine::load_questions_usage_by_activity($record->templateusageid);
        $oldquestionanswers = $DB->get_records('question_answers', ['question' => $record->oldquestionid]);
        $newquestionanswers = array_values($DB->get_records('question_answers', ['question' => $record->newquestionid]));
        $sql = "SELECT qasd.id AS id, qasd.value AS value
                FROM {question_attempt_step_data} qasd
                JOIN {question_attempt_steps} qas ON qas.id = qasd.attemptstepid
                JOIN {question_attempts} qa ON qa.id = qas.questionattemptid
               WHERE qa.questionusageid = :qubaid
                 AND qa.questionid = :questionid
                 AND qasd.name = '_order'";
        $value = $DB->get_record_sql($sql, ['qubaid' => $templateusage->get_id(), 'questionid' => $record->oldquestionid]);
        $values = explode(',', $value->value);
        $replace = [];
        $i = 0;
        foreach ($oldquestionanswers as $oldquestionanswer) {
            $replace[$oldquestionanswer->id] = $newquestionanswers[$i]->id;
            $i++;
        }
        for ($i = 0; $i < count($values); $i++) {
            $values[$i] = $replace[$values[$i]];
        }
        $values = implode(',', $values);
        $DB->set_field('question_attempt_step_data', 'value', $values, ['id' => $value->id]);
        $DB->set_field('question_attempts', 'questionid', $record->newquestionid, ['questionid' => $record->oldquestionid, 'questionusageid' => $templateusage->get_id()]);
    }
 
    $sql = "SELECT ogq.id itemid, c.id usingcontextid, 'mod_offlinequiz' component, 'slot' questionarea,  qv.questionbankentryid questionbankentryid, qv.version version
              FROM {offlinequiz_group_questions} ogq
              JOIN {modules} m ON m.name ='offlinequiz'
              JOIN {course_modules} cm ON cm.module = m.id AND cm.instance = ogq.offlinequizid
              JOIN {context} c ON c.instanceid = cm.id AND c.contextlevel = '70'
              JOIN {question_versions} qv ON qv.questionid = ogq.questionid
              WHERE NOT EXISTS (
                   SELECT 1
                     FROM {question_references} mqr
                    WHERE component = 'mod_offlinequiz'
                      AND questionarea = 'slot'
                      AND itemid = ogq.id
                    )";
    $sql2 = "INSERT INTO {question_references} (itemid, usingcontextid, component, questionarea, questionbankentryid, version) ($sql LIMIT 10000)";
    $thiscount = $DB->count_records('question_references');
    $lastcount = -1;
    try {
        while ($thiscount > $lastcount) {
            $DB->execute($sql2);
            $lastcount = $thiscount;
            $thiscount = $DB->count_records('question_references');
        }
    } catch(Exception $e) {
        //Database doesn't support this type of insert, we have to get them out of the database and insert them manually.
        while ($records = $DB->get_records_sql($sql, [], 0, 10000)) {
            $DB->insert_records('question_references', $records);
        }
    }
}
offlinequiz_fix_question_versions();

[lposmb]

Hi,
We have at our university a production moodle instance on VMWare VM on high-end servers and the last request on the script takes 31 min per 10000 rows and according to some COUNT(*) we have to run it 3 or 4 times to get it done. It's very problematic to run it on production database.
The EXPLAIN for the SELECT don't show anything bad. The mysql-slow-query.log about a try on this is catastrophic : 9 500 000 000 row examined in 31 minutes. There no tables counting above 100 000 lines in all mdl_quizz_* on our bases.
I can run the last SELECT in 1.3 second by hand on same environment, without the UPDATE.
The script tries the UPDATE ... SELECT ... and it's only this form that cause the query optimizer to go crazy on MariaDB 10.5.
The reason for me it's because the table to be updated is mentioned in the sub-query of the SELECT :
UPDATE {question_references} (...) SELECT (...) FROM t1 JOIN t2 ON (...) JOIN t3 (...) JOIN t4 (...) WHERE NOT EXISTS ( SELECT 1 FROM {question_references} ... )
So I started to try to understand how data/transaction integrity is needed and I've read the catch() block : it's a fallback to "no integrity" in the sense of : read all, then write all, even if some one else modifies {question_references} table in between.

If this fallback is really correct, then I think it should used in any cases, avoiding using UPDATE ... INSERT even for SGBD that supports it. The data is far less than 1 megabyte (of RAM) in our case. I tried it on a production copy here, the script finishes in less that 3 seconds (because the first queries takes some times too).

If the result is correct, then it's seem the whole script provided here became useless at least on large MariaDB baked Moodle instances because the same fix could be directly applied in the data migration functions in the plugin itself and take a few seconds at worth case (and not hours).

I put here what I exactly run on my production clone (just commenting out the whole try, and forcing to run the fallback code). I have to add 2 lines at start to make it run with php ./upgrade_data_mig.php from the plugin folder then I just commented out things at bottom without changer any letters :

<?php
define('CLI_SCRIPT', true);
require(__DIR__ . '/../../config.php');
require_once($CFG->libdir . '/questionlib.php');

function offlinequiz_fix_question_versions() {
    global $DB;
[...]
    $sql2 = "INSERT INTO {question_references} (itemid, usingcontextid, component, questionarea, questionbankentryid, version) ($sql LIMIT 10000)";
    $thiscount = $DB->count_records('question_references');
    $lastcount = -1;
    /*
    try {
        while ($thiscount > $lastcount) {
            $DB->execute($sql2);
            $lastcount = $thiscount;
            $thiscount = $DB->count_records('question_references');
        }
    } catch(Exception $e) {*/
        //Database doesn't support this type of insert, we have to get them out of the database and insert them manually.
        while ($records = $DB->get_records_sql($sql, [], 0, 10000)) {
            $DB->insert_records('question_references', $records);
        }
    //}
}
offlinequiz_fix_question_versions();

Could you review what I run and say me if it is safe to do migration this way ?

Note : with the UPDATE ... SELECT ... with the database data already fixed, so the SELECT alone returns 0 lines, the UPDATE ... SELECT takes also 30 minutes to successfully end with affected rows == 0. And this is currently ran by the plugin 4.1.2 when pushing the database upgrade web button in the moodle administration interface ... so running the given script here costs at least 30 mins downtime on our side when update the plugin if we don't patch it to not try the UPADTE ... SELECT.

[univietw]

hi @lposmb, sorry for answering so late. I didn't get a mail for this answer.
Yes, you are absolutely right, it will work.
It's still very strange to me, because we actually made the part in the try, because mysql didn't like the one at the bottom.
On the other hand some Databases don't support it.

TLDR: The solution you promoted definitely works. I just don't understand why for some mysql databases it's slower and for others it's faster.