Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-49679

Restore merging a course format with section format options causes DB constraint ERR



    • Bug
    • Status: Closed
    • Major
    • Resolution: Duplicate
    • 2.8.5, 2.9
    • None
    • Backup, Course
    • None
    • Hide

      Switch to another course format without section format options and take a backup from that. Avoid merging courses in preference to creating new ones instead from the backup.

      Switch to another course format without section format options and take a backup from that. Avoid merging courses in preference to creating new ones instead from the backup.
    • Hide

      Please see description / this screen cast: https://www.youtube.com/watch?v=5CfqTPIQ3dw

      Please see description / this screen cast: https://www.youtube.com/watch?v=5CfqTPIQ3dw


      When you have a course format that correctly implements the API for creating section format options and you restore a backup of that course with 'merge' to a course using the same format then the 'mdl_courformopti_couforsec_uix' key constraint (being unique) is invoked causing a 'dmlwriteexception'.

      Stack trace:

      Error writing to database
      More information about this error
      Debug info: Duplicate entry '4-topcoll-26-donotshowdate' for key 'mdl_courformopti_couforsec_uix'
      INSERT INTO mdl_course_format_options (format,name,value,sectionid,courseid) VALUES(?,?,?,?,?)
      [array (
      0 => 'topcoll',
      1 => 'donotshowdate',
      2 => '1',
      3 => '26',
      4 => 4,
      Error code: dmlwriteexception
      Stack trace:
      line 446 of /lib/dml/moodle_database.php: dml_write_exception thrown
      line 1164 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
      line 1210 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
      line 1480 of /backup/moodle2/restore_stepslib.php: call to mysqli_native_moodle_database->insert_record()
      line 137 of /backup/util/plan/restore_structure_step.class.php: call to restore_section_structure_step->process_course_format_options()
      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 190 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser_processor->receive_chunk()
      line 278 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser->publish()
      line ? of unknownfile: call to progressive_parser->end_tag()
      line 179 of /backup/util/xml/parser/progressive_parser.class.php: call to xml_parse()
      line 158 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser->parse()
      line 110 of /backup/util/plan/restore_structure_step.class.php: call to progressive_parser->process()
      line 181 of /backup/util/plan/base_task.class.php: call to restore_structure_step->execute()
      line 98 of /backup/moodle2/restore_section_task.class.php: call to base_task->execute()
      line 177 of /backup/util/plan/base_plan.class.php: call to restore_section_task->execute()
      line 167 of /backup/util/plan/restore_plan.class.php: call to base_plan->execute()
      line 333 of /backup/controller/restore_controller.class.php: call to restore_plan->execute()
      line 184 of /backup/util/ui/restore_ui.class.php: call to restore_controller->execute_plan()
      line 111 of /backup/restore.php: call to restore_ui->execute()


      Collapsed Topics for M2.8 version https://moodle.org/plugins/pluginversion.php?id=7820 - correctly implements the method for creating section format options:

          public function section_format_options($foreditform = false) {
              static $sectionformatoptions = false;
              if ($sectionformatoptions === false) {
                  $sectionformatoptions = array(
                      'donotshowdate' => array(
                          'default' => 0,
                          'type' => PARAM_INT
              if ($foreditform && !isset($sectionformatoptions['donotshowdate']['label'])) {
                  $sectionformatoptionsedit = array(
                      'donotshowdate' => array(
                          'label' => new lang_string('donotshowdate', 'format_topcoll'),
                          'help' => 'donotshowdate',
                          'help_component' => 'format_topcoll',
                          'element_type' => 'checkbox'
                  $sectionformatoptions = array_merge_recursive($sectionformatoptions, $sectionformatoptionsedit);
              $tcsettings = $this->get_settings();
              if (($tcsettings['layoutstructure'] == 2) || ($tcsettings['layoutstructure'] == 3) ||
                  ($tcsettings['layoutstructure'] == 5)) {
                  // Weekly layout.
                  return $sectionformatoptions;
              } else {
                  return array();

      in its lib.php as documented on: https://docs.moodle.org/dev/Course_formats#Course_format_options and thus the core API code has taken over responsibility for the backup and restore of those section format options. However, because of the way that 'process_course_format_options()' is implemented in '/backup/moodle2/restore_stepslib.php':

          public function process_course_format_options($data) {
              global $DB;
              $data = (object)$data;
              $oldid = $data->id;
              $data->sectionid = $this->task->get_sectionid();
              $data->courseid = $this->get_courseid();
              $newid = $DB->insert_record('course_format_options', $data);
              $this->set_mapping('course_format_options', $oldid, $newid);

      No check is made to see if the record already exists and thus '$newid = $DB->insert_record('course_format_options', $data);' fails as a result of the 'mdl_courformopti_couforsec_uix' violation of the unique compound key containing the attributes: courseid, format, sectionid and name. 'mdl_courformopti_couforsec_uix' is one indices of the 'course_format_options' table.


      I have attached a mdz backup file used to replicate. The steps to replicate are quite long, so instead I've created a screen cast to demonstrate: https://www.youtube.com/watch?v=5CfqTPIQ3dw (the video file is 57.4MB so too big to upload) - you will need: https://moodle.org/plugins/pluginversion.php?id=7820 installed on M2.8 or devise bespoke section format options for say the Topics course format.


        Issue Links



              Unassigned Unassigned
              gb2048 Gareth J Barnard
              Adrian Greeve, David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo, Amaia Anabitarte, Bas Brands, Carlos Escobedo, Laurent David, Raquel Ortega, Sabina Abellan, Sara Arjona (@sarjona)
              1 Vote for this issue
              4 Start watching this issue