Moodle
  1. Moodle
  2. MDL-28512

Duplicate entry '717' for key 'qtypessaopti_que_uix'

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 2.1
    • Fix Version/s: 2.3.5, 2.4.2
    • Component/s: Backup, Questions, Quiz
    • Labels:
    • Environment:
      Ubuntu 10.04 64 Bit
    • Database:
      MySQL
    • Testing Instructions:
      Hide

      To test this, you really need to watch what is going on in the mdl_question and mdl_qtype_essay_options while you do things in the Moodle UI.

      A. On master only:

      1. Before you upgrade, do

      • SELECT COUNT * FROM mdl_qtype_essay_options
      • SELECT COUNT * FROM mdl_question WHERE qtype = 'essay'

      To make it a really good test, those numbers should be different before you do the upgrade. If they are currently the same in your Moodle site, create and delete an essay question, that should leave on extra garbage row in mdl_qtype_essay_options.

      2. Run the upgrade. (Verify no errors.)

      3. Repeat the two queries.

      • SELECT COUNT * FROM mdl_qtype_essay_options
      • SELECT COUNT * FROM mdl_question WHERE qtype = 'essay'
        Now, all the extra rows in mdl_qtype_essay_options should have been cleaned up, so the counts should be the same.

      B. On all branches.

      1. Create a new essay question. Make sure one row is added to each table.

      2. Delete the question. Make sure the new row is deleted from both tables.

      Show
      To test this, you really need to watch what is going on in the mdl_question and mdl_qtype_essay_options while you do things in the Moodle UI. A. On master only: 1. Before you upgrade, do SELECT COUNT * FROM mdl_qtype_essay_options SELECT COUNT * FROM mdl_question WHERE qtype = 'essay' To make it a really good test, those numbers should be different before you do the upgrade. If they are currently the same in your Moodle site, create and delete an essay question, that should leave on extra garbage row in mdl_qtype_essay_options. 2. Run the upgrade. (Verify no errors.) 3. Repeat the two queries. SELECT COUNT * FROM mdl_qtype_essay_options SELECT COUNT * FROM mdl_question WHERE qtype = 'essay' Now, all the extra rows in mdl_qtype_essay_options should have been cleaned up, so the counts should be the same. B. On all branches. 1. Create a new essay question. Make sure one row is added to each table. 2. Delete the question. Make sure the new row is deleted from both tables.
    • Affected Branches:
      MOODLE_21_STABLE
    • Fixed Branches:
      MOODLE_23_STABLE, MOODLE_24_STABLE
    • Pull from Repository:
    • Pull 2.4 Branch:
    • Pull Master Branch:
    • Rank:
      18171

      Description

      In the process of converting a moodle 1.9 instance and "upgrading" to 2.1 to try and export courses to put them into a new 2.1 infrastructure, I ran across this error with multiple of the same courses.
      "Debug info: Duplicate entry '717' for key 'qtypessaopti_que_uix'
      INSERT INTO qtype_essay_options (responseformat,responsefieldlines,attachments,graderinfo,graderinfoformat,questionid) VALUES(?,?,?,?,?,?)
      [array (
      0 => 'editor',
      1 => '15',
      2 => '0',
      3 => '',
      4 => '0',
      5 => 717,
      )]
      Stack trace:

      line 397 of /lib/dml/moodle_database.php: dml_write_exception thrown
      line 878 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
      line 920 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
      line 63 of /question/type/essay/backup/moodle2/restore_qtype_essay_plugin.class.php: call to mysqli_native_moodle_database->insert_record()
      line 131 of /backup/util/plan/restore_structure_step.class.php: call to restore_qtype_essay_plugin->process_essay()
      line 103 of /backup/util/helper/restore_structure_parser_processor.class.php: call to restore_structure_step->process()
      line 125 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 148 of /backup/util/plan/base_plan.class.php: call to base_task->execute()
      line 157 of /backup/util/plan/restore_plan.class.php: call to base_plan->execute()
      line 302 of /backup/controller/restore_controller.class.php: call to restore_plan->execute()
      line 144 of /backup/util/ui/restore_ui.class.php: call to restore_controller->execute_plan()
      line 45 of /backup/restore.php: call to restore_ui->execute()"

        Activity

        Hide
        Michael de Raadt added a comment -

        Hi, Daniel.

        The issue appears to be related to essay questions in the course. Could you try adjusting what you a backup without questions to see if we can isolate the problem some more.

        Also, have you tried restoring these backups to the original server? This might tell us if the problem is with the backup process or the restore process.

        Show
        Michael de Raadt added a comment - Hi, Daniel. The issue appears to be related to essay questions in the course. Could you try adjusting what you a backup without questions to see if we can isolate the problem some more. Also, have you tried restoring these backups to the original server? This might tell us if the problem is with the backup process or the restore process.
        Hide
        Richard Kirby added a comment - - edited

        I believe this is because the essay_options table is not cleaned up if the essay question is deleted.

        You can confirm this easily by creating an essay question in a dummy course, checking the entry in the qtype_essay_options table, delete the course and then confirm that the entry is still present in qtype_essay_options although orphaned since the question won't exist.

        This bug is present in recent Moodle releases too.

        Show
        Richard Kirby added a comment - - edited I believe this is because the essay_options table is not cleaned up if the essay question is deleted. You can confirm this easily by creating an essay question in a dummy course, checking the entry in the qtype_essay_options table, delete the course and then confirm that the entry is still present in qtype_essay_options although orphaned since the question won't exist. This bug is present in recent Moodle releases too.
        Hide
        Michael de Raadt added a comment -

        This sounds like it could be a problem with the way the question is handled, rather than the backup/restore later.

        Tim: could you have a look at this?

        Show
        Michael de Raadt added a comment - This sounds like it could be a problem with the way the question is handled, rather than the backup/restore later. Tim: could you have a look at this?
        Hide
        Tim Hunt added a comment -

        qtype_essay does seem to be missing the delete_question method. Oops! I will add it.

        However, I don't see how that leads to this restore error.

        Show
        Tim Hunt added a comment - qtype_essay does seem to be missing the delete_question method. Oops! I will add it. However, I don't see how that leads to this restore error.
        Hide
        Richard Kirby added a comment -

        Looking at the code in process_essay of the restore_qtype_essay_plugin, it is because it tries to insert a record into the qtype_essay_options table. and if an old entry happens to be there already with the desired questionid, you get the duplicate entry error, since questionid is a foreign unique key in this table.

        Show
        Richard Kirby added a comment - Looking at the code in process_essay of the restore_qtype_essay_plugin, it is because it tries to insert a record into the qtype_essay_options table. and if an old entry happens to be there already with the desired questionid, you get the duplicate entry error, since questionid is a foreign unique key in this table.
        Hide
        Tim Hunt added a comment -

        But, when you restore a question, it gets a new questionid. That is why I don't understand the duplicate row with the same questioned.

        Show
        Tim Hunt added a comment - But, when you restore a question, it gets a new questionid. That is why I don't understand the duplicate row with the same questioned.
        Hide
        Richard Kirby added a comment -

        I wondered that too. When this happened to me in a different context - namely on restoring a moodle2 backup into a moodle2, it turned out to be because there were already some entries in the qtype_essay_options table but without any questions, and for some reason the new questionids ended up overlapping these old entries causing the problem. I just emptied the table manually and restored. Subsequent restores worked fine, although as you have noticed, the old entries just get left.

        Show
        Richard Kirby added a comment - I wondered that too. When this happened to me in a different context - namely on restoring a moodle2 backup into a moodle2, it turned out to be because there were already some entries in the qtype_essay_options table but without any questions, and for some reason the new questionids ended up overlapping these old entries causing the problem. I just emptied the table manually and restored. Subsequent restores worked fine, although as you have noticed, the old entries just get left.
        Hide
        Tim Hunt added a comment -

        I have added the missing delete_question method on all branches.

        In addition, for master only I have done an extra commit that cleans up any orphanned garbage rows on upgrade.

        If you want to clean up the garbage manually before you upgrade to Moodle 2.5, then do

        DELETE FROM mdl_qtype_essay_options WHERE NOT EXISTS (
                SELECT 1 FROM mdl_question WHERE qtype = 'essay' AND
                        mdl_question.id = mdl_qtype_essay_options.questionid)
        
        Show
        Tim Hunt added a comment - I have added the missing delete_question method on all branches. In addition, for master only I have done an extra commit that cleans up any orphanned garbage rows on upgrade. If you want to clean up the garbage manually before you upgrade to Moodle 2.5, then do DELETE FROM mdl_qtype_essay_options WHERE NOT EXISTS ( SELECT 1 FROM mdl_question WHERE qtype = 'essay' AND mdl_question.id = mdl_qtype_essay_options.questionid)
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Integrated (23, 24 & master), thanks!

        Show
        Eloy Lafuente (stronk7) added a comment - Integrated (23, 24 & master), thanks!
        Hide
        Frédéric Massart added a comment -

        Test passed on 2.3, 2.4 and master (Upgrade tested on PostgreSQL and MySQL).

        Show
        Frédéric Massart added a comment - Test passed on 2.3, 2.4 and master (Upgrade tested on PostgreSQL and MySQL).
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Surely you will be happy to know that your code is now part of Moodle upstream. Thanks, thanks!

        Closing as fixed, ciao

        Show
        Eloy Lafuente (stronk7) added a comment - Surely you will be happy to know that your code is now part of Moodle upstream. Thanks, thanks! Closing as fixed, ciao

          People

          • Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: