Moodle
  1. Moodle
  2. MDL-27910

Changing grading method causes database error in moodle 2.1

    Details

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

      Create a quiz, allowing multiple attempts, with grading method set to maximum grade.
      Login as a student and make multiple attempts.
      Then change the grading method to first or last attempt. (Ideally tr
      Verify that the grades are recomputed correctly, in particular that you don't get a fatal error.

      Ideally try changing between all the grading methods and verify the scores after each change.

      Show
      Create a quiz, allowing multiple attempts, with grading method set to maximum grade. Login as a student and make multiple attempts. Then change the grading method to first or last attempt. (Ideally tr Verify that the grades are recomputed correctly, in particular that you don't get a fatal error. Ideally try changing between all the grading methods and verify the scores after each change.
    • URL:
      qa.moodle.net
    • Affected Branches:
      MOODLE_21_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE
    • Pull from Repository:
    • Pull Master Branch:
    • Rank:
      17816

      Description

      I was doing QA test MDLQA-1093 and ran into a problem when changing grading method for a quiz.

      Error reading from database

      More information about this error

      Debug info: Table 'qa.iquiza' doesn't exist

      SELECT users.userid, qg.id, qg.grade, newgrades.newgrade

      FROM (
      SELECT userid
      FROM mdl_quiz_grades qg
      WHERE quiz = ?
      UNION
      SELECT DISTINCT userid
      FROM mdl_quiz_attempts quiza2
      WHERE
      quiza2.timefinish <> 0 AND
      quiza2.preview = 0 AND
      quiza2.quiz = ?
      ) users

      LEFT JOIN mdl_quiz_grades qg ON qg.userid = users.userid AND qg.quiz = ?

      LEFT JOIN (

      SELECT quiza.userid, MAX(quiza.sumgrades) * 5 AS newgrade
      FROM mdl_quiz_attempts quiza
      JOIN (
      SELECT
      iquiza.userid,
      MIN(attempt) AS firstattempt,
      MAX(attempt) AS lastattempt

      FROM

      {quiz_attempts iquiza}

      WHERE
      iquiza.timefinish <> 0 AND
      iquiza.preview = 0 AND
      iquiza.quiz = ?

      GROUP BY iquiza.userid
      ) first_last_attempts ON first_last_attempts.userid = quiza.userid
      WHERE
      quiza.attempt = first_last_attempts.firstattempt AND
      quiza.timefinish <> 0 AND
      quiza.preview = 0 AND
      quiza.quiz = ?
      GROUP BY quiza.userid
      ) newgrades ON newgrades.userid = users.userid

      WHERE
      ABS(newgrades.newgrade - qg.grade) > 0.000005 OR
      (newgrades.newgrade IS NULL) <> (qg.grade IS NULL)
      [array (
      0 => '6',
      1 => '6',
      2 => '6',
      3 => '6',
      4 => '6',
      )]
      Stack trace:
      line 391 of /lib/dml/moodle_database.php: dml_read_exception thrown
      line 794 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
      line 723 of /mod/quiz/locallib.php: call to mysqli_native_moodle_database->get_records_sql()
      line 119 of /mod/quiz/lib.php: call to quiz_update_all_final_grades()
      line 327 of /course/modedit.php: call to quiz_update_instance()

        Issue Links

          Activity

          Hide
          Tim Hunt added a comment -

          Sorry, this was a silly typo. Must have been introduced when I ported this code from 1.9.

          Show
          Tim Hunt added a comment - Sorry, this was a silly typo. Must have been introduced when I ported this code from 1.9.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Integrated, thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - Integrated, thanks!
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Confirmed the error doesn't happen anymore...

          But I've got some really annoying results after regrade, let me explain:

          1) One quiz, with one true/false question, set to highest grade.
          2) User A does one attempt, answer correct, gets 100% grade in attempt and final.
          3) User A does same attempt, answer incorrect, gets 0% grade in attempt and 100% final.
          4) Edit quiz and change grading to average.
          5) WRONG: Recalculate grades, expected 50%, get 0% on both attempts and final too.
          6) WRONG: Specially, the first attempt, who had 100% shows this information on review:

          The correct answer is 'True'.
          Correct
          Marks for this submission: 1.00/1.00. With previous penalties this gives 0.00/1.00.
          

          (so it seems that penalty is being applied incorrectly.
          7) Also, I change the quiz behaviour to "adaptative mode, no penalties", try recalculating and nothing changes. Not sure if this is also one problem or no but I report it here.

          So, tell me what to do with this and I'll do. I think it's separate from this issue, but seems really dangerous, no way to "recover" those grades at all. Grade recalculation is stuck applying that (wrong?) penalty.

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Confirmed the error doesn't happen anymore... But I've got some really annoying results after regrade, let me explain: 1) One quiz, with one true/false question, set to highest grade. 2) User A does one attempt, answer correct, gets 100% grade in attempt and final. 3) User A does same attempt, answer incorrect, gets 0% grade in attempt and 100% final. 4) Edit quiz and change grading to average. 5) WRONG: Recalculate grades, expected 50%, get 0% on both attempts and final too. 6) WRONG: Specially, the first attempt, who had 100% shows this information on review: The correct answer is 'True'. Correct Marks for this submission: 1.00/1.00. With previous penalties this gives 0.00/1.00. (so it seems that penalty is being applied incorrectly. 7) Also, I change the quiz behaviour to "adaptative mode, no penalties", try recalculating and nothing changes. Not sure if this is also one problem or no but I report it here. So, tell me what to do with this and I'll do. I think it's separate from this issue, but seems really dangerous, no way to "recover" those grades at all. Grade recalculation is stuck applying that (wrong?) penalty. Ciao
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Crap, I've tried exactly the 1-7 points above and now the grades don't get any incorrect penalty applied. Really strange!

          Show
          Eloy Lafuente (stronk7) added a comment - Crap, I've tried exactly the 1-7 points above and now the grades don't get any incorrect penalty applied. Really strange!
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Uhm, just saw that the quiz where I did the test originally was one activity upgraded from 2.0! I've tried with others and same happens, wrong penalties get in action!

          The original quiz running under 2.0.x performs the regrade ok, the student continues having 100% if highest is used and changes to 50% if average is used, no wrong penalties happen.

          Show
          Eloy Lafuente (stronk7) added a comment - Uhm, just saw that the quiz where I did the test originally was one activity upgraded from 2.0! I've tried with others and same happens, wrong penalties get in action! The original quiz running under 2.0.x performs the regrade ok, the student continues having 100% if highest is used and changes to 50% if average is used, no wrong penalties happen.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Also, offtopic: I've seen the the last page, before finishing the attempt shows "not answered" no matter if you've answered true or false. I think this was fixed no long ago in 2.0.x (other qtypes shown that behavior too, if I'm not wrong).

          Show
          Eloy Lafuente (stronk7) added a comment - Also, offtopic: I've seen the the last page, before finishing the attempt shows "not answered" no matter if you've answered true or false. I think this was fixed no long ago in 2.0.x (other qtypes shown that behavior too, if I'm not wrong).
          Hide
          Eloy Lafuente (stronk7) added a comment -

          LOL, more yet, I've tried to backup the 2.0 course (working ok) with that activity and restore it on 2.1 and I'm getting one horrible error about dupe index, trying to insert 0 into quiz_attempt->uniqueid

          Or how one simple typo fixing leads to a big collection of horrible problems... surely we need to split this into a bunch of critical issues.

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - LOL, more yet, I've tried to backup the 2.0 course (working ok) with that activity and restore it on 2.1 and I'm getting one horrible error about dupe index, trying to insert 0 into quiz_attempt->uniqueid Or how one simple typo fixing leads to a big collection of horrible problems... surely we need to split this into a bunch of critical issues. Ciao
          Hide
          Tim Hunt added a comment -

          Please split into separate issues, but after this week's integration, I think at least on of the things you mentioned was fixed over the weekend.

          Show
          Tim Hunt added a comment - Please split into separate issues, but after this week's integration, I think at least on of the things you mentioned was fixed over the weekend.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Oki, note for integrators:

          • This has already been integrated and WILL NOT stop daily/weekly release process.
          • This will kept open until integration has been completed, then:
            • Will be retested
            • Found remaining issues will be created
            • Finally, this will be closed

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Oki, note for integrators: This has already been integrated and WILL NOT stop daily/weekly release process. This will kept open until integration has been completed, then: Will be retested Found remaining issues will be created Finally, this will be closed Ciao
          Hide
          Eloy Lafuente (stronk7) added a comment -

          This has been sent upstream, tomorrow:

          • Will be retested
          • Found remaining issues will be created
          • Finally, this will be closed

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - This has been sent upstream, tomorrow: Will be retested Found remaining issues will be created Finally, this will be closed Ciao
          Hide
          Eloy Lafuente (stronk7) added a comment - - edited

          Retesting, I've confirmed these points (will create new issues after finishing tests):

          E1 (MDL-28037) - 1-activity (1-quiz) backup generated in 2.0.x (with 3 attempts), restores perfectly in 2.0.x, but trying to restore in 2.1.x ends with DB error:

          Debug info: Duplicate entry '0' for key 'mdl_quizatte_uni_uix'
          
          INSERT INTO mdl_quiz_attempts (uniqueid,userid,sumgrades,timestart,timefinish,timemodified,layout,preview,quiz,attempt) VALUES(?,?,?,?,?,?,?,?,?,?)
          [array (
          0 => 0,
          1 => '5',
          2 => '1.00000',
          3 => 1287061174,
          4 => 1287061181,
          5 => 1287061181,
          6 => '1,0',
          7 => '0',
          8 => 66,
          9 => '1',
          )]
          Stack trace:
          line 396 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 291 of /mod/quiz/backup/moodle2/restore_quiz_stepslib.php: call to mysqli_native_moodle_database->insert_record()
          line 300 of /mod/quiz/backup/moodle2/restore_quiz_stepslib.php: call to restore_quiz_activity_structure_step->process_quiz_attempt()
          line 131 of /backup/util/plan/restore_structure_step.class.php: call to restore_quiz_activity_structure_step->process_quiz_attempt_legacy()
          ...
          ...
          

          E2 (MDL-28036) - On upgrade from 2.0.x to 2.1.x the "plugins report" page shows (missing lang strings):

          [pluginname,quiz_grading]...
          [pluginname,quiz_overview]...
          [pluginname,quiz_responses]...
          [pluginname,quiz_statistics]...
          

          E3 (MDL-28035) - Upgraded site, with 3 attempts (originally 100%, 100%, 0%). Regrade all changes them to 0% 0% 0%. And reviewing it shows (somehow applying incorrect penalties):

          Correct (because the answer IS correct!)
          Marks for this submission: 1.00/1.00. With previous penalties this gives 0.00/1.00.
          
          Show
          Eloy Lafuente (stronk7) added a comment - - edited Retesting, I've confirmed these points (will create new issues after finishing tests): E1 ( MDL-28037 ) - 1-activity (1-quiz) backup generated in 2.0.x (with 3 attempts), restores perfectly in 2.0.x, but trying to restore in 2.1.x ends with DB error: Debug info: Duplicate entry '0' for key 'mdl_quizatte_uni_uix' INSERT INTO mdl_quiz_attempts (uniqueid,userid,sumgrades,timestart,timefinish,timemodified,layout,preview,quiz,attempt) VALUES(?,?,?,?,?,?,?,?,?,?) [array ( 0 => 0, 1 => '5', 2 => '1.00000', 3 => 1287061174, 4 => 1287061181, 5 => 1287061181, 6 => '1,0', 7 => '0', 8 => 66, 9 => '1', )] Stack trace: line 396 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 291 of /mod/quiz/backup/moodle2/restore_quiz_stepslib.php: call to mysqli_native_moodle_database->insert_record() line 300 of /mod/quiz/backup/moodle2/restore_quiz_stepslib.php: call to restore_quiz_activity_structure_step->process_quiz_attempt() line 131 of /backup/util/plan/restore_structure_step.class.php: call to restore_quiz_activity_structure_step->process_quiz_attempt_legacy() ... ... E2 ( MDL-28036 ) - On upgrade from 2.0.x to 2.1.x the "plugins report" page shows (missing lang strings): [pluginname,quiz_grading]... [pluginname,quiz_overview]... [pluginname,quiz_responses]... [pluginname,quiz_statistics]... E3 ( MDL-28035 ) - Upgraded site, with 3 attempts (originally 100%, 100%, 0%). Regrade all changes them to 0% 0% 0%. And reviewing it shows (somehow applying incorrect penalties): Correct (because the answer IS correct!) Marks for this submission: 1.00/1.00. With previous penalties this gives 0.00/1.00.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          (attaching simple 1-activity quiz backup containing 3 attempts, with only one question)

          It can be used to reproduce the problems E1 and E3. Surely you'll need to enrol the users in the course to see the differences in the gradebook.

          Show
          Eloy Lafuente (stronk7) added a comment - (attaching simple 1-activity quiz backup containing 3 attempts, with only one question) It can be used to reproduce the problems E1 and E3. Surely you'll need to enrol the users in the course to see the differences in the gradebook.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Passing this finally because has been retested and all problems found reported into separate issues:

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Passing this finally because has been retested and all problems found reported into separate issues: E1 ( MDL-28037 ) E2 ( MDL-28036 ) E3 ( MDL-28035 ) Ciao
          Hide
          Eloy Lafuente (stronk7) added a comment -

          And closing because was sent upstream 10 days ago. Thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - And closing because was sent upstream 10 days ago. Thanks!

            People

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

              Dates

              • Created:
                Updated:
                Resolved: