Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Minor
-
Resolution: Duplicate
-
Affects Version/s: 1.9.7
-
Fix Version/s: 1.9.10
-
Component/s: Course, Database SQL/XMLDB
-
Labels:None
-
Environment:64-bit PHP 5, MySQL 5
-
Database:MySQL
-
Affected Branches:MOODLE_19_STABLE
-
Fixed Branches:MOODLE_19_STABLE
Description
I originally discovered this problem when using the "Re-sort courses by name" button on the "Add/edit courses" page. When I would click the button, the page would quickly reload and the courses would be in same order as before (not sorted by name). There would be no error messages, not even with developer debugging turned on.
When looking at the "sortorder" column in the "mdl_course" table, I noticed that most of the values were ridiculously large. And dozens of the values were the same number, 13806016346361000000. While this is still below the max value for that column (unsigned bigint has max of 18446744073709551615), it is above the max value for a floating point number in PHP (even 64-bit). You can test this with the PHP CLI like this:
[user@host ~]$ php -r 'var_dump(13806016346361000000 + 123456);'
float(1.3806016346361E+19)
[user@host ~]$
Notice how it rounded it to the nearest precision it could handle (this was with 64-bit PHP). As you may know, PHP does not keep track of overflows. So it's doing this without throwing any errors. See http://www.php.net/manual/en/language.types.float.php for more information on this.
So it looks like every time you sort courses, these values in the database get larger and larger until the value overflows and PHP just rounds it. I've been told that this only happens with MySQL but I have not been able to test it with anything else. I was able to fix this on the site that was having the problem by putting the site in Maintenance Mode (just in case) and running the following script:
<?php
require_once('moodle/config.php');
$rs = get_recordset('course', '', '', 'sortorder', 'id');
for ($i = 1; $rec = rs_fetch_next_record($rs); ++$i) {
set_field('course', 'sortorder', $i, 'id', $rec->id);
}
rs_close($rs);
?>
I'm not sure if this is the best way to fix it (it's technically only temporary), but it worked just fine and now the "Re-sort courses by name" button on the "Add/edit courses" page works as expected.
We are using Moodle 1.9.4+ (Build: 20090225) and have been dealing with this Problem for a while now.
Manually calling fix_course_sortorder in safe mode used to repair the sort order but recently this failed too. Responsible for this was a record in the course table that had no related record in the context table (context.instanceid = course.id and context.contextlevel=50).
Since fix_course_sortorder uses get_courses() to get a set of courses in each category it missed this record because get_courses() joins course and context.
For all other purposes fix_course_sortorder affects this record and from there it gets worse. Every time the function was called the maximum sortorder value about doubled and the high values alway started with this course.
Deleting the broken course record or recreating the context record caused fix_course_sortorder(safe=true) to create a nice clean sortorder with a max value of about 420000 now growing at about 10000 a day (which gives us some time i guess
) and calling fix_course_sortorder(safe=true) sets it back to a lower value as it is suppused to do.
I recommend to check moodle-databases that show this issue for missing context-records / relational integrity to confirm my observation.
I hope this helps and can give additional information if needed.