Description

Edit quizz (Navigation: quizz administration) and add two random question (with the two previous essay questions). The quizz should have two page with a random question of each page.

Connect as student and answer quizz.

As admin I tried to view the attempt (/Moodle_Integration/mod/quiz/report.php?id=5&mode=overview)
Just after the following text "Showing graded and ungraded attempts for each user. The one attempt for each user that is graded is highlighted. The grading method for this quiz is Highest grade.", I can see this exception:

Error reading from database

More information about this error

Debug info: BIGINT UNSIGNED value is out of range in '(`moodle_registration`.`qa`.`timefinish` - `moodle_registration`.`qa`.`timestart`)'

Travis Wilhelm
added a comment - 17/Feb/11 9:50 AM I've got the same problem. Appears to be that in the mdl_quiz_attempts table, the query breaks when the 'timefinish' field is 0.
See thread here: http://moodle.org/mod/forum/discuss.php?d=167616#p741516

The problem is with computing qa.timefinish - qa.timestart AS duration in the select. In MySQL these are unsigned values, and so it does not like it when timefinish < timestart. Except that it does not always happen. For example, for an open attempt, timefinish is 0, but that does not seem to cause problems. At least not on my test MySQL system.

This value has to be computed in SQL, because it must be possible to sort on that column.

Presumably, this is fixable with appropriate use of casts, but I would like to know more about the edge-cases for MySQL unsiged int subtraction before doing the fix.

If anyone can explain a safe way to do the subtraction, or can give examples of pairs of values timestart and timefinish that either do or don't give errors, that would be helpful. Thanks.

Tim Hunt
added a comment - 17/Feb/11 5:55 PM Quiz bug, so -> me.
The problem is with computing qa.timefinish - qa.timestart AS duration in the select. In MySQL these are unsigned values, and so it does not like it when timefinish < timestart. Except that it does not always happen. For example, for an open attempt, timefinish is 0, but that does not seem to cause problems. At least not on my test MySQL system.
This value has to be computed in SQL, because it must be possible to sort on that column.
Presumably, this is fixable with appropriate use of casts, but I would like to know more about the edge-cases for MySQL unsiged int subtraction before doing the fix.
If anyone can explain a safe way to do the subtraction, or can give examples of pairs of values timestart and timefinish that either do or don't give errors, that would be helpful. Thanks.

So I will now run a query to update 'timefinish' to a standard sort of timestamp (only for that specific quiz currently giving the error):
UPDATE `mdl_quiz_attempts` set timefinish=1297994073 WHERE `quiz` = 268 and `timefinish`=0
(affected 6 rows - others with timefinish=0 from the same quiz).

And now the quiz works: (mod/quiz/report.php?id=6086&mode=overview) shows up (including 6 non-finished as above).

Other information about my mysql (my.ini):
I tried adding sql_mode='NO_UNSIGNED_SUBTRACTION' to the [mysqld] section. I have decommented it also and it still doesn't affect the error.

Travis Wilhelm
added a comment - 18/Feb/11 6:40 AM OK, I'm currently working on a specific quiz.
Here's an entry with timefinish that isn't working:
4733 4733 268 1165 1 0.00000 1297672957 0 1297672957 3187,3188,3192,0 0
So I will now run a query to update 'timefinish' to a standard sort of timestamp (only for that specific quiz currently giving the error):
UPDATE `mdl_quiz_attempts` set timefinish=1297994073 WHERE `quiz` = 268 and `timefinish`=0
(affected 6 rows - others with timefinish=0 from the same quiz).
And now the quiz works: (mod/quiz/report.php?id=6086&mode=overview) shows up (including 6 non-finished as above).
Other information about my mysql (my.ini):
I tried adding sql_mode='NO_UNSIGNED_SUBTRACTION' to the [mysqld] section. I have decommented it also and it still doesn't affect the error.

That explains why I can't reproduce this. I still seem to be on MySQL 5.1.x on this test computer. I assume you are on 5.5.5 or later.

Can you try changing strict mode on your system, and see if it makes a difference? (I am not proposing turning off strict mode as a long-term solution, I just want to make sure we have fully understood the problem before I try to fix the Moodle code.)

Tim Hunt
added a comment - 18/Feb/11 4:40 PM - edited Thanks for the extra information, that is really helpful.
Just two more things I would like to know. Which version of MySQL? and are you using MyISAM or InnoDB tables?
Aha! the old Google the error message techniqued has worked. Have a read of http://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html
That explains why I can't reproduce this. I still seem to be on MySQL 5.1.x on this test computer. I assume you are on 5.5.5 or later.
Can you try changing strict mode on your system, and see if it makes a difference? (I am not proposing turning off strict mode as a long-term solution, I just want to make sure we have fully understood the problem before I try to fix the Moodle code.)
By the way, for testing I made a simple test table
CREATE TABLE qa (
id bigint (10) unsigned NOT NULL AUTO_INCREMENT,
timestart bigint (10) unsigned NOT NULL DEFAULT '0' ,
timefinish bigint (10) unsigned NOT NULL DEFAULT '0' ,
PRIMARY KEY (id)
) DEFAULT CHARSET=utf8 COLLATE =utf8_unicode_ci AUTO_INCREMENT=5 ;
INSERT INTO qa (id, timestart, timefinish) VALUES (1, 1297672957, 0);
INSERT INTO qa (id, timestart, timefinish) VALUES (2, 1297672957, 1297672957);
INSERT INTO qa (id, timestart, timefinish) VALUES (3, 1297672957, 1297672956);
INSERT INTO qa (id, timestart, timefinish) VALUES (4, 1297672957, 1297672958);
and was running the test query
SELECT id, timestart, timefinish, timefinish - timestart
FROM qa
ORDER BY id
I think that this covers all the cases that we need to get working in order to fix the Moodle code.

Tim Hunt
added a comment - 18/Feb/11 10:17 PM I think I have a fix. See https://github.com/timhunt/moodle/compare/master...MDL-26416 .
Note that there are two commits there. One just cleans up the code a bit, the second one actually makes the relevant change.
It would be really helpful if someone could test this fix, and confirm that it works, before I do a pull request. Thanks.

Travis Wilhelm
added a comment - 19/Feb/11 4:59 AM Perfect Tim - that fixes it and now shows some attempts as 'Open'. As you've probably worked out, I'm using the latest version of mysql (5.5.x - not sure exactly which).
Thanks for your help Tim! It is much appreciated!
What's the process from here? When you do a pull request, does the change go into the next daily build?

Tim Hunt
added a comment - 19/Feb/11 6:07 AM The way it works is described on http://docs.moodle.org/en/Development:Process
But to summarise: There are not daily builds, there are now weekly builds.
All the pull requests from the previous week are reviewed on Monday, and combined at http://git.moodle.org/gw?p=integration.git;a=summary
Then on Tuesday, that new version is tested, and if all is OK, then on Wednesday it becomes that week's weekly build.
Except that this week and next week are a bit difference because they are about to release Moodle 2.0.2 which involves extra testing.
Anyway, thanks for testing this, I will do the pull request now. You will know when this becomes part of a weekly build, because the status of the bug will be changed to closed.

omer
added a comment - 07/Mar/11 4:57 PM hi,
just installed build 20110303, problam is still not fixed..
i get the same error.
in order for the teacher to be able to view the data, i've manually changed the values of finishtime from 0 to the starttime of the same line..

Tim Hunt
added a comment - 08/Mar/11 12:58 AM Right, well then you are clearly not running the latest Moodle release, because the latest version of Moodle does not execute that database query.
Look at mod/quiz/report/overview/report.php in your version, and compare it to https://github.com/moodle/moodle/blob/master/mod/quiz/report/overview/report.php#L210

If you use Postgres, then you can't possibly have exactly the same error. The text of the error message is MySQL-specific. Please copy and paste exactly the error message you get (with debugging set to developer level) here.

Tim Hunt
added a comment - 08/Mar/11 3:32 PM If you use Postgres, then you can't possibly have exactly the same error. The text of the error message is MySQL-specific. Please copy and paste exactly the error message you get (with debugging set to developer level) here.

Tim Hunt
added a comment - 11/Mar/11 6:31 AM Doh! How did I forget to check that myself?
Since this particular bug about the overview report is already fixed please could you create a new issue for the responses report problem. Thanks.