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

Could not create unique index "mdl_quizatte_quiuseatt_uix

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.3
    • Fix Version/s: 2.3
    • Component/s: Quiz
    • Labels:
    • Testing Instructions:
      Hide

      1. You need to start with a Moodle 2.2 site.

      2. In this test site, you need to manufacture the kind of bad data that was causing problems. You can do that as follows:
      a. Suppose userid 600 is enroled in the course as a student, and userid 700 is enroled as a teacher.
      b. Start a quiz attempt with user 600 and finish it. Then start a preview with userid 700, but don't submit it.
      c. Go into the database, and edit the quiz_attempt row for the teacher's preview, and change userid to 600.
      So, now, if you do SELECT * FROM mdl_quiz_attempts WHERE userid = 600, it should look like the data in the screen-grab attached to this bug.

      3. Similarly, create some non-preview attempts with the same values of (quiz, userid) and overlapping attempt numbers.

      4. Now upgrade to latest master. Verify that
      a. There are no errors during the upgrade.
      b. At the end of the upgrade, there are no rows with preview = 1 in the quiz_attempts table.
      c. If you can be bothered, check that all related data in linked tables has been deleted. (If you don't know that the linked tables are, then look at the SQL in the patch.)
      d. Looking in the quiz_attempts table, for the rows you set up in 3. verify that the rows are now numbered sequentially (according to the order ((old-)attempt, id).

      Ideally test this on all 4 supported DBs, but at least test MySQL. (I tested Postgres.)

      Show
      1. You need to start with a Moodle 2.2 site. 2. In this test site, you need to manufacture the kind of bad data that was causing problems. You can do that as follows: a. Suppose userid 600 is enroled in the course as a student, and userid 700 is enroled as a teacher. b. Start a quiz attempt with user 600 and finish it. Then start a preview with userid 700, but don't submit it. c. Go into the database, and edit the quiz_attempt row for the teacher's preview, and change userid to 600. So, now, if you do SELECT * FROM mdl_quiz_attempts WHERE userid = 600, it should look like the data in the screen-grab attached to this bug. 3. Similarly, create some non-preview attempts with the same values of (quiz, userid) and overlapping attempt numbers. 4. Now upgrade to latest master. Verify that a. There are no errors during the upgrade. b. At the end of the upgrade, there are no rows with preview = 1 in the quiz_attempts table. c. If you can be bothered, check that all related data in linked tables has been deleted. (If you don't know that the linked tables are, then look at the SQL in the patch.) d. Looking in the quiz_attempts table, for the rows you set up in 3. verify that the rows are now numbered sequentially (according to the order ((old-)attempt, id). Ideally test this on all 4 supported DBs, but at least test MySQL. (I tested Postgres.)
    • Affected Branches:
      MOODLE_23_STABLE
    • Fixed Branches:
      MOODLE_23_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      This was some 2.1 site which I first upgraded to 2.2 and then upgraded to 2.3 without stopping in the middle:

      Default exception handler: DDL sql execution error Debug: ERROR: could not create unique index "mdl_quizatte_quiuseatt_uix"
      DETAIL: Key (quiz, userid, attempt)=(8, 40, 1) is duplicated.
      CREATE UNIQUE INDEX mdl_quizatte_quiuseatt_uix ON mdl_quiz_attempts (quiz, userid, attempt)

      • line 419 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown
      • line 243 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
      • line 596 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
      • line 94 of /lib/ddl/database_manager.php: call to pgsql_native_moodle_database->change_database_structure()
      • line 83 of /lib/ddl/database_manager.php: call to database_manager->execute_sql()
      • line 813 of /lib/ddl/database_manager.php: call to database_manager->execute_sql_arr()
      • line 132 of /mod/quiz/db/upgrade.php: call to database_manager->add_index()
      • line 627 of /lib/upgradelib.php: call to xmldb_quiz_upgrade()
      • line 358 of /lib/upgradelib.php: call to upgrade_plugins_modules()
      • line 1524 of /lib/upgradelib.php: call to upgrade_plugins()
      • line 155 of /admin/cli/upgrade.php: call to upgrade_noncore()

      !!! DDL sql execution error !!!
      !! ERROR: could not create unique index "mdl_quizatte_quiuseatt_uix"
      DETAIL: Key (quiz, userid, attempt)=(8, 40, 1) is duplicated.
      CREATE UNIQUE INDEX mdl_quizatte_quiuseatt_uix ON mdl_quiz_attempts (quiz, userid, attempt) !!
      !! Stack trace: * line 419 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown

      • line 243 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
      • line 596 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
      • line 94 of /lib/ddl/database_manager.php: call to pgsql_native_moodle_database->change_database_structure()
      • line 83 of /lib/ddl/database_manager.php: call to database_manager->execute_sql()
      • line 813 of /lib/ddl/database_manager.php: call to database_manager->execute_sql_arr()
      • line 132 of /mod/quiz/db/upgrade.php: call to database_manager->add_index()
      • line 627 of /lib/upgradelib.php: call to xmldb_quiz_upgrade()
      • line 358 of /lib/upgradelib.php: call to upgrade_plugins_modules()
      • line 1524 of /lib/upgradelib.php: call to upgrade_plugins()
      • line 155 of /admin/cli/upgrade.php: call to upgrade_noncore()

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  25/Jun/12