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

During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      This is very similar to MDL-40370, which was basically the same bug for qtype_shortanswer.

      You need to test upgrading from 2.4.x to 2.5 or master.

      1. Before you upgrade, create some match questions somewhere in your question bank.

      2. Go into the mdl_question_match table in the database, and duplicate some of the rows there (so, in the duplicate row, only the id column will be different from the original).

      3. Upgrade to 2.5 or master. Make sure that there are no unique key related errors.

      4. Check in the database. The key table will have been renamed to qtype_match_options. Ensure that there is now only one row for each questionid.

      Show
      This is very similar to MDL-40370 , which was basically the same bug for qtype_shortanswer. You need to test upgrading from 2.4.x to 2.5 or master. 1. Before you upgrade, create some match questions somewhere in your question bank. 2. Go into the mdl_question_match table in the database, and duplicate some of the rows there (so, in the duplicate row, only the id column will be different from the original). 3. Upgrade to 2.5 or master. Make sure that there are no unique key related errors. 4. Check in the database. The key table will have been renamed to qtype_match_options. Ensure that there is now only one row for each questionid.
    • Affected Branches:
      MOODLE_25_STABLE
    • Fixed Branches:
      MOODLE_25_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      Platform: Redhat Enterprise Linux 6.4 64 bit, PHP 5.3.3, MySQL 5.6

      During the upgrade from 2.2.4+ to 2.5.1+, we encountered this error.

      Default exception handler: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
      CREATE UNIQUE INDEX mdl_qtypmatcopti_que_uix ON mdl_qtype_match_options (questionid)
      Error code: ddlexecuteerror

      Upon further investigation, I was informed that the mdl_qtype_match_options is a rename of the old question_match table for our version of 2.2.4+.

      It would seem that the "questionid" field on mdl_qtype_match_options table for 2.5.1+ is related to the "question" field in the mdl_question_match table for 2.2.4+

      So I ran a query to look for these duplicate "question" field values in the mdl_question_match table in our 2.2.4+.

      Sure enough the dupes were there.

      mysql> select id, question, subquestions from mdl_question_match where question in (select question from mdl_question_match group by question having count(question) > 1) order by question;

      -------------------------------------------------------------------------

      id question subquestions

      -------------------------------------------------------------------------

      1422 679986 14164,14165,14166,14167,14168,14169,14170,14171,14172,14173
      1426 679986 14189,14190,14191,14192,14193,14194,14195,14196,14197,14198
      1423 679987 14174,14175,14176,14177,14178
      1427 679987 14199,14200,14201,14202,14203
      1424 679988 14179,14180,14181,14182,14183,14184,14185
      1428 679988 14204,14205,14206,14207,14208,14209,14210
      1425 679992 14186,14187,14188
      1429 679992 14211,14212,14213

      -------------------------------------------------------------------------
      8 rows in set (0.31 sec)

      It is important to fix or remove these duplicates in the question column before the upgrade because if I don't will get the "DDL sql execution error Debug: Duplicate entry 'XXXXXX' for key 'mdl_qtypmatcopti_que_uix'" where XXXXXX represents the question column field value error (ie. DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix)

      I want to find out which courses these records returned by my query are attached to so I could remove these quizzes or questions that are no longer needed to bypass the error.

      Is this a bug or expected behaviour during the upgrade?

      How could be resolve this other than actually deleting one of each duplicate record (could be dangerous as we don't know what we are deleting and what other situations it is tied to if we delete the record).

      Thanks

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              timhunt Tim Hunt
              Reporter:
              corestaples Jerry
              Peer reviewer:
              Frédéric Massart
              Integrator:
              Dan Poltawski
              Tester:
              Rajesh Taneja
              Participants:
              Component watchers:
              Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Tim Hunt, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                9/Sep/13