Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Blocker
-
Resolution: Fixed
-
Affects Version/s: 1.9.12, 2.0.3, 2.1
-
Component/s: Assignment (2.2)
-
Environment:LAMP Stack
-
Database:MySQL
-
Testing Instructions:
-
Workaround:
-
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:
- log in as an admin
- create a new assignment within a course
- log in as a student and submit and assignment
- log back in as the admin
- view the assignment and click View submissions
- Ensure you see the submissions and not an error.
Gliffy Diagrams
Attachments
Issue Links
- caused a regression
-
-
- Closed
-
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Links Hierarchy
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.
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
Confirming this behaviour on Single Upload and Advanced Upload assignment types.
This happens on a fresh install as well as an upgrade.
And online text assignments also.
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;
Over to you Big Guy!
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
Hi Sam,
Will this be fixed for 1.9.12?
Thanks,
Barbara
Looks perfect, 100% unnecessary to calculate it in SQL, agree.
I've tested it here under 20_STABLE and submissions and their status is shown perfectly.
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 
(reopening as commented)
Hi guys,
I've backported this fix for 1.9 now and its up for integration.
Cheers
Sam
Integrated the 1.9 patch, thanks!
Works Great
tested on mysql 5.1.41, and postgres 8.4
Sent to moodle.git, many thanks!
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?