Moodle
  1. Moodle
  2. MDL-27638

Assignment Module Submissions SQL overflow

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 1.9.12, 2.0.3, 2.1
    • Fix Version/s: 1.9.13, 2.0.4
    • Component/s: Assignment (2.2)
    • Labels:
    • Environment:
      LAMP Stack
    • Database:
      MySQL
    • Testing Instructions:
      Hide

      This needs to be tested on MySQL version > 5.5 and probably on a couple of other database engines as well.

      Show
      This needs to be tested on MySQL version > 5.5 and probably on a couple of other database engines as well.
    • Workaround:
      Hide

      Remove the line...

      COALESCE(SIGN(SIGN(s.timemarked) + SIGN(s.timemarked - s.timemodified)), 0) AS status

      ...from two queries in assignment/lib.php. Be sure to leave a space before the FROM.

      Show
      Remove the line... COALESCE(SIGN(SIGN(s.timemarked) + SIGN(s.timemarked - s.timemodified)), 0) AS status ...from two queries in assignment/lib.php. Be sure to leave a space before the FROM.
    • Affected Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE, MOODLE_21_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE
    • Pull Master Branch:
      wip-MDL-27638-master
    • Rank:
      17317

      Description

      Completed an upgrade from Moodle 1.9.12+ to Moodle 2.0.2+ of all of our systems. Many of my assignments in the system with user data in them are returning these errors when visited. I have turned on the full debugging.

      Warning: mysqli::query(): (22003/1690): BIGINT UNSIGNED value is out of range in '(`mdl_viu_primary`.`s`.`timemarked` - `mdl_viu_primary`.`s`.`timemodified`)' in /share-viu/viu/moodlePrimary/lib/dml/mysqli_native_moodle_database.php on line 793
      Error reading from database

      More information about this error

      Debug info: BIGINT UNSIGNED value is out of range in '(`mdl_viu_primary`.`s`.`timemarked` - `mdl_viu_primary`.`s`.`timemodified`)'
      SELECT u.id,u.picture,u.firstname,u.lastname,u.imagealt,u.email,
      s.id AS submissionid, s.grade, s.submissioncomment,
      s.timemodified, s.timemarked,
      COALESCE(SIGN(SIGN(s.timemarked) + SIGN(s.timemarked - s.timemodified)), 0) AS status FROM mdl_user u LEFT JOIN mdl_assignment_submissions s ON u.id = s.userid
      AND s.assignment = 6994 WHERE u.id IN (8066,8790,13244,13314,13600,13604,14554,15678,17371,17480,17616,17695,18138,18144,18501,18526,18702,20388,20844,21230,21239,21240,21250,21256) ORDER BY lastname ASC
      [array (
      )]
      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 1295 of /mod/assignment/lib.php: call to mysqli_native_moodle_database->get_records_sql()
      line 642 of /mod/assignment/lib.php: call to assignment_base->display_submissions()
      line 57 of /mod/assignment/submissions.php: call to assignment_base->submissions()

      Replication instructions:

      1. log in as an admin
      2. create a new assignment within a course
      3. log in as a student and submit and assignment
      4. log back in as the admin
      5. view the assignment and click View submissions
      6. Ensure you see the submissions and not an error.

        Issue Links

          Activity

          Hide
          Michael de Raadt added a comment -

          Thanks for reporting this.

          You might have to help us discover how the DB got into this state before the module is accessed. Could you provide us with more information about the values that are being upgraded?

          Show
          Michael de Raadt added a comment - Thanks for reporting this. You might have to help us discover how the DB got into this state before the module is accessed. Could you provide us with more information about the values that are being upgraded?
          Hide
          Brent Lee added a comment -

          Thanks Michael,

          This may be related to this bug:

          http://tracker.moodle.org/browse/MDL-26284

          This was a clean MYSQL dump from one server, and then sourced into another. The course worked fine in its previous 1.9.12+ state, so I am guessing its something that happened during the upgrade process which went smooth.

          Show
          Brent Lee added a comment - Thanks Michael, This may be related to this bug: http://tracker.moodle.org/browse/MDL-26284 This was a clean MYSQL dump from one server, and then sourced into another. The course worked fine in its previous 1.9.12+ state, so I am guessing its something that happened during the upgrade process which went smooth.
          Hide
          Brent Lee added a comment -

          Got it:

          I replaced all instances of:

          COALESCE(SIGN(SIGN(s.timemarked) + SIGN(s.timemarked - s.timemodified)), 0) AS status ";

          With:

          COALESCE(SIGN(CAST(s.timemarked as SIGNED) + SIGN(CAST(s.timemarked as SIGNED) - CAST(s.timemodified as SIGNED))), 0) AS status ";

          in assignment/lib.php

          Show
          Brent Lee added a comment - Got it: I replaced all instances of: COALESCE(SIGN(SIGN(s.timemarked) + SIGN(s.timemarked - s.timemodified)), 0) AS status "; With: COALESCE(SIGN(CAST(s.timemarked as SIGNED) + SIGN(CAST(s.timemarked as SIGNED) - CAST(s.timemodified as SIGNED))), 0) AS status "; in assignment/lib.php
          Hide
          Michael de Raadt added a comment -

          Confirming this behaviour on Single Upload and Advanced Upload assignment types.

          This happens on a fresh install as well as an upgrade.

          Show
          Michael de Raadt added a comment - Confirming this behaviour on Single Upload and Advanced Upload assignment types. This happens on a fresh install as well as an upgrade.
          Hide
          Michael de Raadt added a comment -

          And online text assignments also.

          Show
          Michael de Raadt added a comment - And online text assignments also.
          Hide
          Michael de Raadt added a comment -

          This appears to be a change how MySQL is working. We tried this on an older version of MySQL and the query ran without error. We also tried it on PostgrSQL and it ran.

          The COALESCE line of the query seems to be redundant now. In both cases where it is used in assignment/lib.php the result of "status" it is later recalculated in PHP.

          Removing this line of the query allows the query to run and the file functions properly.

          Let's see if we can resolve this in this week's integration.

          Michael;

          Show
          Michael de Raadt added a comment - This appears to be a change how MySQL is working. We tried this on an older version of MySQL and the query ran without error. We also tried it on PostgrSQL and it ran. The COALESCE line of the query seems to be redundant now. In both cases where it is used in assignment/lib.php the result of "status" it is later recalculated in PHP. Removing this line of the query allows the query to run and the file functions properly. Let's see if we can resolve this in this week's integration. Michael;
          Hide
          Michael de Raadt added a comment -

          Over to you Big Guy!

          Show
          Michael de Raadt added a comment - Over to you Big Guy!
          Hide
          Sam Hemelryk added a comment -

          Alright its up for integration now, as discussed I've opted to remove those two lines of SQL given that they are being calculated in PHP after the query anyway.

          Cheers
          Sam

          Show
          Sam Hemelryk added a comment - Alright its up for integration now, as discussed I've opted to remove those two lines of SQL given that they are being calculated in PHP after the query anyway. Cheers Sam
          Hide
          Barbara Taylor added a comment -

          Hi Sam,

          Will this be fixed for 1.9.12?

          Thanks,

          Barbara

          Show
          Barbara Taylor added a comment - Hi Sam, Will this be fixed for 1.9.12? Thanks, Barbara
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Looks perfect, 100% unnecessary to calculate it in SQL, agree.

          Show
          Eloy Lafuente (stronk7) added a comment - Looks perfect, 100% unnecessary to calculate it in SQL, agree.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          I've tested it here under 20_STABLE and submissions and their status is shown perfectly.

          Show
          Eloy Lafuente (stronk7) added a comment - I've tested it here under 20_STABLE and submissions and their status is shown perfectly.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Finally, I'm opting to reopen this for next week because it seems that the same fix is 100% possible for 19_STABLE, but I cannot implement and test it now. So surely will be available soon there unless we find some problem. Thanks for pointing that, Barbara.

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Finally, I'm opting to reopen this for next week because it seems that the same fix is 100% possible for 19_STABLE, but I cannot implement and test it now. So surely will be available soon there unless we find some problem. Thanks for pointing that, Barbara. Ciao
          Hide
          Eloy Lafuente (stronk7) added a comment -

          (reopening as commented)

          Show
          Eloy Lafuente (stronk7) added a comment - (reopening as commented)
          Hide
          Sam Hemelryk added a comment -

          Hi guys,

          I've backported this fix for 1.9 now and its up for integration.

          Cheers
          Sam

          Show
          Sam Hemelryk added a comment - Hi guys, I've backported this fix for 1.9 now and its up for integration. Cheers Sam
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Integrated the 1.9 patch, thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - Integrated the 1.9 patch, thanks!
          Hide
          Rajesh Taneja added a comment -

          Works Great
          tested on mysql 5.1.41, and postgres 8.4

          Show
          Rajesh Taneja added a comment - Works Great tested on mysql 5.1.41, and postgres 8.4
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Sent to moodle.git, many thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - Sent to moodle.git, many thanks!

            People

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

              Dates

              • Created:
                Updated:
                Resolved: