Unable to upgrade from Moodle 3.9.13+ to 4.0.1

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by Leon Stringer -
Number of replies: 15
Picture of Core developers Picture of Particularly helpful Moodlers

That code change might fix the error but I don't think that's what caused this. What I think has happened is that there's been a previous attempt to upgrade your site to Moodle 4.0 but this was rolled back to the previous version by restoring a MySQL/MariaDB backup. The problem with doing this is that any new tables added during the Moodle 4.0 upgrade do not get removed. So when you tried to upgrade again there's already a mdl_question_bank_entries with some data which is what's led to this error.

So alternatively you could drop (delete) the mdl_question_bank_entries from your Moodle 3.9 database before attempting the upgrade. Back up your database before making any changes like this.

(My opinion: I think Moodle shouldn't be doing a conditional create table for mdl_question_bank_entries so that an error occurs when it tries to create the table).

Average of ratings: Useful (2)
In reply to Leon Stringer

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by David Husband -
I have tried changing the code and I get the following error.

Duplicate entry '194-mod_quiz-slot-16' for key 'lms_quesrefe_usicomqueite_uix'
INSERT INTO lms_question_references
(usingcontextid, component, questionarea, itemid, questionbankentryid)
SELECT c.id, 'mod_quiz', 'slot', qs.id, qv.questionbankentryid
FROM lms_question q
JOIN lms_question_versions qv ON q.id = qv.questionid
JOIN lms_quiz_slots qs ON q.id = qs.questionid
JOIN lms_modules m ON m.name = 'quiz'
JOIN lms_course_modules cm ON cm.module = m.id AND cm.instance = qs.quizid
JOIN lms_context c ON c.instanceid = cm.id AND c.contextlevel = 70
WHERE q.qtype <> 'random'
[array (
)]
Error code: dmlwriteexception

I have tried removing the table, but get a different error telling me the table is missing.

I have 3.11 and have tried installing the latest build as of 31/05/22.
Average of ratings: -
In reply to David Husband

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

David: If any of the following tables exist delete them (ideally back up the database before making changes like this):

  1. lms_question_bank_entries
  2. lms_question_references
  3. lms_question_versions
  4. lms_bigbluebuttonbn
  5. lms_bigbluebuttonbn_logs
  6. lms_bigbluebuttonbn_recordings

And then see if the upgrade works.

To explain: it looks like 1) you're using MySQL/MariaDB, 2) an upgrade to Moodle 4.0 was previously attempted, and 3) the site was rolled back to Moodle 3.11. But the default behaviour when a MySQL/MariaDB database is restored is that any newly created tables are not removed, only the tables in the backup are restored. So when the upgrade is re-attempted there are a mix of restored 3.11 tables and 4.00 tables from the previous upgrade attempt. This is what's causing these errors.

So when you roll back to the previous version you need to delete the existing database, create a new empty one, then restore the file into that. Or, if you know the affected tables, you can delete these manually.

Average of ratings: Useful (2)
In reply to Leon Stringer

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

If you have BigBlueButton installed on your 3.11 site don't delete lms_bigbluebuttonbn.

Average of ratings: Useful (1)
In reply to Leon Stringer

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by David Husband -
I now get this error

Debug info: Table 'dev-dewi-moodle-103.lms_question_references' doesn't exist
INSERT INTO lms_question_references
(usingcontextid, component, questionarea, itemid, questionbankentryid)
SELECT c.id, 'mod_quiz', 'slot', qs.id, qv.questionbankentryid
FROM lms_question q
JOIN lms_question_versions qv ON q.id = qv.questionid
JOIN lms_quiz_slots qs ON q.id = qs.questionid
JOIN lms_modules m ON m.name = 'quiz'
JOIN lms_course_modules cm ON cm.module = m.id AND cm.instance = qs.quizid
JOIN lms_context c ON c.instanceid = cm.id AND c.contextlevel = 70
WHERE q.qtype <> 'random'
[array (
)]
Error code: dmlwriteexception
Average of ratings: -
In reply to Leon Stringer

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by David Husband -
Morning

This error was on a testing site, so I have replaced the testing database. I have now been able to complete the upgrade.
I am glad this did not occur on the live site.

Kind regards
David
Average of ratings: -
In reply to Leon Stringer

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by marg marg -
I had the same error and I did what you said about deleting tables from the database. The update went well (3.11.6 to 4.0.1). Thank you very much!!
Average of ratings: -
In reply to marg marg

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by James Todd -
I'm upgrading to version 4.0.3 and still get the error.
Deleting the tables gave me the same error as David.
I also get error when importing the data into a new table.
I upgraded one of our other sites yesterday with hardly any issues.

renaming the line in upgrade.php
gives the below.

Debug info: Duplicate entry '421-mod_quiz-slot-64' for key 'mdl_quessetrefe_usicomquei_uix'
INSERT INTO mdl_question_set_references
(usingcontextid, component, questionarea, itemid, questionscontextid, filtercondition)
SELECT
c.id,
'mod_quiz',
'slot',
qs.id,
qc.contextid,
CONCAT('{"questioncategoryid":"', q.category, '","includingsubcategories":"', qs.includingsubcategories, '"}')
FROM mdl_question q
INNER JOIN mdl_quiz_slots qs on q.id = qs.questionid
INNER JOIN mdl_course_modules cm ON cm.instance = qs.quizid AND cm.module = ?
INNER JOIN mdl_context c ON cm.id = c.instanceid AND c.contextlevel = ?
INNER JOIN mdl_question_categories qc ON qc.id = q.category
WHERE q.qtype = ?
[array (
0 => '17',
1 => 70,
2 => 'random',
)]
Error code: dmlwriteexception
Average of ratings: -
In reply to James Todd

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

James: This error almost certainly has the same root cause, i.e. an upgrade to 4.0 was performed, the database was rolled back to 3.x, then the upgrade attempted again. But because MySQL/MariaDB leaves any new tables behind these new 4.0 tables already exist. In general you should delete and recreate the MySQL/MariaDB database before restoring the backup to avoid this. For this specific error you should make sure the following tables have been dropped (deleted) before attempting the upgrade again:

  1. mdl_question_bank_entries
  2. mdl_question_versions
  3. mdl_question_references
  4. mdl_question_set_references

As I said previously Moodle should handle this better by stopping with an error – in this case when it finds mdl_question_set_references already exists – instead of proceeding to the INSERT.  That way you would instead get the error Table "question_set_references" already exists which tells you what the actual problem is. If I get time I'll expand the patch in MDL-74859 to cover this.

Average of ratings: Useful (1)
In reply to Leon Stringer

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by James Todd -
Thanks Leon.
I've got a full list of issues upgrading.
If I try to upgrade, I only get the error on this table "mdl_question_bank_entries"
When I build using a blank database, it fails importing the data the, the error doesn't give a reason it just could not complete due to an error.
If I delete the table, it says the table does not exist and can't continue.
When I change the code in the upgrade file it gives the error above.

I have another site on the same version. That upgraded with hardly any issues at all, however quizzes aren't really used on that site it's more for managing classroom bookings, so there's only a few.

This site is all elearning and it's really busy so there's thousands of entries.
Average of ratings: -
In reply to James Todd

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

You'll have to be more precise about what you did for each scenario and exactly what the outcome was (exact errors or other messages).

To re-attempt an upgrade to Moodle 4.0.x you should ensure you're starting with only the 3.x tables in the database. The best way to do this is to delete the target database, create a new empty database and restore the 3.x backup into the empty database. (Back up the database before deleting it just in case).

You could try replacing the Moodle 4.0.x lib/db/upgrade.php with this version. This should just fail with if the new tables exist (error: Table … already exists). But if you do get this error it means the new tables were already present before the upgrade was started, i.e. you haven't rolled the 3.x database back correctly.

If you're not rolling back the database before re-attempting the upgrade you could try deleting the rows from mdl_question_set_references (DELETE FROM mdl_question_set_references – again back up the database beforehand). But I'd recommend the above roll-back approach otherwise you'll probably run into other issues such as with the BigBlueButton tables.

Average of ratings: -
In reply to Leon Stringer

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by James Todd -
I've changed the upgrade file. and deleted the question table.

I now get this error.

Debug info: Table 'defedf77_tfc.mdl_question_set_references' doesn't exist
INSERT INTO mdl_question_set_references
(usingcontextid, component, questionarea, itemid, questionscontextid, filtercondition)
SELECT
c.id,
'mod_quiz',
'slot',
qs.id,
qc.contextid,
CONCAT('{"questioncategoryid":"', q.category, '","includingsubcategories":"', qs.includingsubcategories, '"}')
FROM mdl_question q
INNER JOIN mdl_quiz_slots qs on q.id = qs.questionid
INNER JOIN mdl_course_modules cm ON cm.instance = qs.quizid AND cm.module = ?
INNER JOIN mdl_context c ON cm.id = c.instanceid AND c.contextlevel = ?
INNER JOIN mdl_question_categories qc ON qc.id = q.category
WHERE q.qtype = ?
[array (
0 => '17',
1 => 70,
2 => 'random',
)]
Error code: dmlwriteexception×Dismiss this notification
Stack trace:
line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown
line 291 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
line 1167 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->query_end()
line 4017 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->execute()
line 1875 of /lib/upgradelib.php: call to xmldb_main_upgrade()
line 525 of /admin/index.php: call to upgrade_core()

Rolling back isn't an option as the site has been in use since the back up from weeks ago.

When I build using a blank database, it fails importing the data the, the error is useless it's just "could not complete due to an error".
Average of ratings: -
In reply to James Todd

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by James Todd -
I'm ready to give up.
This morning I've build a new site and got the data to import
it then asked me to upgrade again.

here we go again...

Debug info: Duplicate entry '421-mod_quiz-slot-64' for key 'mdl_quessetrefe_usicomquei_uix'
INSERT INTO mdl_question_set_references
(usingcontextid, component, questionarea, itemid, questionscontextid, filtercondition)
SELECT
c.id,
'mod_quiz',
'slot',
qs.id,
qc.contextid,
CONCAT('{"questioncategoryid":"', q.category, '","includingsubcategories":"', qs.includingsubcategories, '"}')
FROM mdl_question q
INNER JOIN mdl_quiz_slots qs on q.id = qs.questionid
INNER JOIN mdl_course_modules cm ON cm.instance = qs.quizid AND cm.module = ?
INNER JOIN mdl_context c ON cm.id = c.instanceid AND c.contextlevel = ?
INNER JOIN mdl_question_categories qc ON qc.id = q.category
WHERE q.qtype = ?
[array (
0 => '17',
1 => 70,
2 => 'random',
)]
Error code: dmlwriteexception×Dismiss this notification
Stack trace:
line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown
line 291 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
line 1167 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->query_end()
line 4017 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->execute()
line 1875 of /lib/upgradelib.php: call to xmldb_main_upgrade()
line 525 of /admin/index.php: call to upgrade_core()
Average of ratings: -
In reply to James Todd

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers
It will always ask to to upgrade because you are bringing 4.0 code to a 3.9 database. So on this site, I am presuming that you have additional question types - I know that when some of the additional question types (from OU) moved to core, there were some issues with upgrades on sites that already had those installed. I am going to guess that this might be the root cause. Of course, if you delete them first, you are going to lose any data related to them which probably is not an option. I normally wouldn't go here but I wonder if you try jumping to 3.11 first, that would help at all. There might be some steps in the 3.11 code that would deal with this better. The other option would be to find the offending entry mentioned above and delete it. That will probably then take you to the next one and it would probably be an arduous task but maybe it is only a few...if you are good with sql, you might be able to identify any duplicate entries in the database table and remove them all with a query.
Average of ratings: Useful (3)
In reply to Emma Richardson

Re: Unable to upgrade from Moodle 3.9.13+ to 4.0.1

by James Todd -
Thanks Emma.
You've helped me a few times lately.

We have found a simple solution; I don't know why I didn't try it originally.

I have built a new site from scratch.
Backed up and restored all the courses.
Just a bit tweaking to get it working.

We have 7 sites all together, only 2 more to go after this one, the others have been fine (ish)
Average of ratings: -