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
Issue Links
- caused a regression
-
MDL-29529
Error reading from database - Unknown column 'status' in 'order clause'
-
- Closed
-
-
MDL-29928
Sorting submitted assignments by status makes all assignments disappear - again
-
- Closed
-
- is duplicated by
-
MDL-26284
Problems with MySQL 5.5.8 and viewing assignments
-
- Closed
-
-
MDL-33413
I was not able to view submitted assignments in my course CIT2012C, Exercise C British English, Exercise C American English
-
- Closed
-
-
MDL-26503
Uploaded assignments are not shown to teacher
-
- Closed
-
-
MDL-27463
Cannot access assignment grading table
-
- Closed
-
-
MDL-26579
error when displaying assignments BIGINT UNSIGNED value is out of range in '(`moodle`.`s`.`timemarked` - `moodle`.`s`.`timemodified`)'
-
- Closed
-
-
MDL-26984
error al abrir tareas alzadas por el alumno
-
- Closed
-
-
MDL-28096
Unable to view "submitted assignments" (SQL error: BIGINT UNSIGNED value is out of range)
-
- Closed
-
-
MDL-27462
Cannot access assignment grading table
-
- Closed
-
-
MDL-27678
Submitted Assignments unable to be accessed
-
- Closed
-
-
MDL-28252
dmlreadexception when teacher tries to view uploaded assignments using "View 1 submitted assignments" link
-
- Closed
-
-
MDL-28380
I am not able to see submitted assignments
-
- Closed
-
-
MDL-28446
Error reading from database error/moodle/dmlreadexception 22003/1690
-
- Closed
-
-
MDL-29687
Database Error - BIGINT UNSIGNED value
-
- Closed
-
-
MDL-32795
error al leer base de datos
-
- Closed
-
-
MDL-32796
error al leer base de datos de un espacio de tareas creado
-
- Closed
-
-
MDL-34425
We can not access to the window for evaluate activities
-
- Closed
-
-
MDL-39677
Cannot view my students' assignments
-
- Closed
-
Activity
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?