Moodle

Assignment Module Submissions SQL overflow

Details

  • Type: Bug Bug
  • Status: Closed 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.
  • URL:
  • Affected Branches:
    MOODLE_19_STABLE, MOODLE_20_STABLE, MOODLE_21_STABLE
  • Fixed Branches:
    MOODLE_19_STABLE, MOODLE_20_STABLE
  • Pull from Repository:
  • Pull Master Branch:
    wip-MDL-27638-master
  • Pull Master Diff URL:

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

caused a regression

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-29529 Error reading from database - Unknown column 'status' in 'order clause'

  • Blocker - Blocks development and/or testing, prevents Moodle from running (applicable to bugs only)
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-29928 Sorting submitted assignments by status makes all assignments disappear - again

  • Blocker - Blocks development and/or testing, prevents Moodle from running (applicable to bugs only)
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.
is duplicated by

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-26284 Problems with MySQL 5.5.8 and viewing assignments

  • Blocker - Blocks development and/or testing, prevents Moodle from running (applicable to bugs only)
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-33413 I was not able to view submitted assignments in my course CIT2012C, Exercise C British English, Exercise C American English

  • Blocker - Blocks development and/or testing, prevents Moodle from running (applicable to bugs only)
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-26503 Uploaded assignments are not shown to teacher

  • Critical - Crashes server, loss of data, severe memory leak
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-27463 Cannot access assignment grading table

  • Critical - Crashes server, loss of data, severe memory leak
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-26579 error when displaying assignments BIGINT UNSIGNED value is out of range in '(`moodle`.`s`.`timemarked` - `moodle`.`s`.`timemodified`)'

  • Major - Major loss of function, incorrect output
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-26984 error al abrir tareas alzadas por el alumno

  • Major - Major loss of function, incorrect output
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-28096 Unable to view "submitted assignments" (SQL error: BIGINT UNSIGNED value is out of range)

  • Major - Major loss of function, incorrect output
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-27462 Cannot access assignment grading table

  • Minor - Minor loss of function where workaround is possible
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-27678 Submitted Assignments unable to be accessed

  • Minor - Minor loss of function where workaround is possible
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-28252 dmlreadexception when teacher tries to view uploaded assignments using "View 1 submitted assignments" link

  • Minor - Minor loss of function where workaround is possible
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-28380 I am not able to see submitted assignments

  • Minor - Minor loss of function where workaround is possible
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-28446 Error reading from database error/moodle/dmlreadexception 22003/1690

  • Minor - Minor loss of function where workaround is possible
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-29687 Database Error - BIGINT UNSIGNED value

  • Minor - Minor loss of function where workaround is possible
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-32795 error al leer base de datos

  • Minor - Minor loss of function where workaround is possible
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-32796 error al leer base de datos de un espacio de tareas creado

  • Minor - Minor loss of function where workaround is possible
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-34425 We can not access to the window for evaluate activities

  • Minor - Minor loss of function where workaround is possible
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-39677 Cannot view my students' assignments

  • Minor - Minor loss of function where workaround is possible
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
Michael de Raadt added a comment -

And online text assignments also.

Show
Michael de Raadt added a comment - And online text assignments also.
Hide
Permalink
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
Permalink
Michael de Raadt added a comment -

Over to you Big Guy!

Show
Michael de Raadt added a comment - Over to you Big Guy!
Hide
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
Eloy Lafuente (stronk7) added a comment -

(reopening as commented)

Show
Eloy Lafuente (stronk7) added a comment - (reopening as commented)
Hide
Permalink
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
Permalink
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
Permalink
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
Permalink
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

Vote (3)
Watch (5)

Dates

  • Created:
    Updated:
    Resolved: