Restore bug: Duplicate entry for key 'mdl_subpsect_sec_uix'

[hello-josh]

I am running into the following error when trying to restore a course that contains subpages:

!! Duplicate entry '10008905' for key 'mdl_subpsect_sec_uix'
INSERT INTO mdl_subpage_sections (sectionid,pageorder,stealth,subpageid) VALUES(?,?,?,?)
[array (
  0 => 10008905,
  1 => '1',
  2 => '0',
  3 => 209,
)]
Error code: dmlwriteexception !!
!! Stack trace: * line 429 of /lib/dml/moodle_database.php: dml_write_exception thrown
* line 1107 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 1149 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
* line 91 of /mod/subpage/backup/moodle2/restore_subpage_stepslib.php: call to mysqli_native_moodle_database->insert_record()
* line 131 of /backup/util/plan/restore_structure_step.class.php: call to restore_subpage_activity_structure_step->process_subpage_sections()
* line 103 of /backup/util/helper/restore_structure_parser_processor.class.php: call to restore_structure_step->process()
* line 151 of /backup/util/xml/parser/processors/grouped_parser_processor.class.php: call to restore_structure_parser_processor->dispatch_chunk()
* line 91 of /backup/util/helper/restore_structure_parser_processor.class.php: call to grouped_parser_processor->postprocess_chunk()
* line 148 of /backup/util/xml/parser/processors/simplified_parser_processor.class.php: call to restore_structure_parser_processor->postprocess_chunk()
* line 92 of /backup/util/xml/parser/processors/progressive_parser_processor.class.php: call to simplified_parser_processor->process_chunk()
* line 169 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser_processor->receive_chunk()
* line 253 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser->publish()
* line ? of unknownfile: call to progressive_parser->end_tag()
* line 158 of /backup/util/xml/parser/progressive_parser.class.php: call to xml_parse()
* line 137 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser->parse()
* line 105 of /backup/util/plan/restore_structure_step.class.php: call to progressive_parser->process()
* line 153 of /backup/util/plan/base_task.class.php: call to restore_structure_step->execute()
* line 192 of /backup/moodle2/restore_activity_task.class.php: call to base_task->execute()
* line 167 of /backup/util/plan/base_plan.class.php: call to restore_activity_task->execute()
* line 157 of /backup/util/plan/restore_plan.class.php: call to base_plan->execute()
* line 315 of /backup/controller/restore_controller.class.php: call to restore_plan->execute()
* line 26 of /be101-restore.php: call to restore_controller->execute_plan()
 !!

This happens if I am trying to restore into a new course as well as when trying to restore into an existing course while deleting the prior course content.

This is the script I am running it with now (based off of MOODLE_24_STABLE). Is there a fix for this in a more recent version that I can backport into the 2.4 version?

#!/usr/bin/php
<?php
define( 'CLI_SCRIPT', true );

require_once( 'config.php' );
require_once( $CFG->dirroot . '/backup/util/includes/restore_includes.php' );

$transaction = $DB->start_delegated_transaction( );

$user      = 2; // admin
$category  = 1; // Miscellaneous
$fullname  = '';
$shortname = '';
$directory = $argv[1]; // as found in: $CFG->dataroot . '/temp/backup/'
$courseid  = 35;

echo 'Starting restore now...' . PHP_EOL;

$rc = new restore_controller( $directory, $courseid, backup::INTERACTIVE_NO, backup::MODE_SAMESITE, $user, backup::TARGET_CURRENT_DELETING );
$rc->execute_precheck( );
$rc->execute_plan( );

$transaction->allow_commit( );

echo 'Restore finished successfully!' . PHP_EOL;

[sammarshallou]

Hi, sorry, I'm afraid this is a known issue. To fix it in the database, at a time when you are sure there are no current restores ongoing, delete all rows from mdl_subpage_sections where sectionid > 10000000. (Note: This assumes you have less than that many sections. To double-check you could also include a left join against course_sections and check there isn't one that matches that id.)

What usually causes it is if a previous restore crashes partway through. It can crash if you try to restore the same thing twice at the same time, or else just if something totally unrelated to subpage causes the restore to crash.

I didn't think of a good way to prevent this happening yet. Your script where it restores with a transaction around everything should prevent it from being caused by that restore, though.

[hello-josh]

I have multiple courses using subpages, will deleting the rows where sectionid > 10000000 mess up current courses or are they renumbered at the end of the restore process?

[sammarshallou]

During normal use (other than restore) the sectionid from mdl_subpage_sections should correspond to an id from the mdl_course_sections table.

It temporarily uses a larger number when restoring to avoid clashes with a database unique index, because it doesn't know the new section number at that point. Then renumbers it.

So you should only have entries with really big numbers if a restore is currently in progress.

[hello-josh]

Awesome, thanks! That is my issue then, I have a broken restore that corrupted the temporary numbers in the table

[nadavkav]

We are having a similar issue on one of our old Moodle 2.4 systems :-(

I wonder... We are about to upgrade the entire system to Moodle 2.7 and get an updated subpage plugin from you, Do you think It is fixed in the new plugin version?

[nadavkav]

Came across this issue again, as we are using it (still) on Moodle 3.1 And once again, @sammarshallou advice helped :smile: (Thank you!)

[hannaedelman]

Sustaining nadavkav. we also had this problem :-(

[sammarshallou]

Good (ish) news - since last comment I made a script called mod/subpage/deletebaddata.php which you can use to automatically delete the bad data without having to manually do it in the database.

You need to call it manually like mod/subpage/deletebaddata.php?iamsure=1&sesskey=whatever (where whatever is your current sesskey) - obviously it is available only to site admin.

The script should be safe to use provided that there is no current ongoing restore operation at the point when you run it.

Sorry we haven't done a better fix! We are still using subpage with Moodle 3.1 as well, but are beginning to phase it out (that said I firmly expect we will still have it for legacy material in 3.2, 3.3, 3.4, and probably a long way into the future).

[nadavkav]

Thank you @sammarshallou very much for this important update! We will definitely use it, if future issues like these arise again.

I have also recommended (even strongly) phasing out this plugin, but I am not sure how seriously I am taken ;-) as the pedagogical stuff like it a LOT :smile: