Further information per Anthony's request posted in forum

"I would want to see what happens when we do something like:
SELECT * from mdl_course
WHERE category = 1
ORDER BY fullname ASC;
"

My re-worked version of that query (pruning displayed fields, adjusting the category)

mysql> select sortorder, shortname, fullname from mdl_course where category=311 order by fullname asc;
--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
25 rows in set (0.03 sec)

Screenshot of actual display in Moodle v1.9.6 attached to issue.

See also comments below, re: "Further information per Anthony's request posted in forum ". This is a screenshot of the actual Moodle display of same courses that appear in the sample query.

Final details requested in the forum thread.

Linux version 2.6.16.60-0.21-smp
Apache2
mysql Ver 14.12 Distrib 5.0.51a

I created some courses with names like "ENGL 1100: English Literature 1 (S10)" and they sorted correctly so there is nothing inherently about names like that that break sorting.

While experimenting with this in 1.9 I noticed that sortorder values in the course table creep up with every automatic sort. This could be causing the problem. The sort orders listed above certainly seem awfully high.

With every sort the sortorder of every course in the category goes up by 1000 + 100 + (n-1) where n is the number of courses in the category. If you just hit the reorder button over and over the value goes up and up. I've attached 2 patches that resolve this. These should NOT be applied to any production system until they've been properly reviewed.

The change to datalib.php fixes what looks like a logic error that causes the +1000 shift in sortorders with every sort. Line 1334 does this
$n = $min;
then starting at line 1346 there is this
if ($min < $n+$tolerance || $min > $n+$tolerance+$catgap )

if $min and $n are equal and you add $tolerance to $n then $min will always be less than it and a +1000 shift will occur. The patch just flips the addition to subtraction so it now tests whether $min is less than $n by the tolerance.

The changes to category.php have the effect or sorting courses in place rather than saying "get the current maximum current sortorder and move all the courses above that" This is what was adding the 100 + (n-1) to the sort orders. I'm not aware of a good reason to not do this in place but one may exist.

Note this is fixed comprehensively in 2.0 with a big patch MDL-14580

We just need a small fix here for 1.9

This is an addition to the upgrade script that should shift all the courses down away from the column maximums. Needs checking by one of the seniors.

Talked this over with Martin. I've committed the code to sort the courses in place. I've also committed code in db/upgrade.php that iterates over all courses and gives them a new sortorder. Theyll be in the same order but it will bring all the numbers down away from the column maximum.

Uhm... I'd reopen this and change upgrade code a bit. We cannot use get_records() in queries returning thousands of records (and definetively, there are sites with thousands of courses). Plz, change it to get_recordset_sql()

More yet, fetch only the needed fields (id, category, sortorder) and not the whole record. And finally, to update only one field (sortorder), perhaps it's better to use set_field() instead of update_record().

For your consideration, ciao

reopening, this upgrade can not use get_records_sql()
also if you need instance of $c do use "new object()" - set field is better here

I've committed altered upgrade code based on the comments above. I tested this against 5000 courses and the old code did work however the new version is definitely faster.

hey i just committed changes to lib/db/upgrade.php and can't move the merge tag because of your commit - can you confirm this should have the merge tag moved ?

This area of the code has already been extensively rewritten in 2. These changes do not need to be merge so you are ok to move the merged tag. Sorry. Forgot to do it myself.

So what can I apply to my 1.9.8 install to make this work? How is the patch applied? I cannot sort courses and have hundreds of courses to manage. Very slow to do by hand and no ajax for dragging here.

This fix has actually broken our "Re-sort courses by name" button. It has always worked fine but after upgrading to 1.9.9 it now has no effect. The up and down arrows still work though.

With debugging on I get messages like:
Duplicate entry '1-170852' for key 'category_sortorder'
UPDATE mdl_course SET sortorder = '170852' WHERE id = '2704'

Reverting the changes to course/category.php fixes it in my test environment.

We're using SLES 11, Apache 2, MySQL 5.0.26, PHP 5.2.5.

Cheers.

Tony, sorry to hear that the fix has broken your "Re-sort courses by name" button. Reopening the issue for further investigation.

Any fix? Same problem with 1.9.9 "Re-sort courses by name" button broken. Any workaround.?!?

Hi Abraham,

Did it work pre-1.9.8? If so, reverting the changes to course/category.php worked for us. Try the attached patch.

Cheers,
Tony

Sorry about the delay. Hopefully Ill get time to investigate this further later this week.

We are also experiencing the same issue and are now on 1.9.9. The Sort Courses by Name button is not working for us as well.

Hi. I'm attaching a patch that will hopefully resolve this. Can someone who is experiencing the problem and has a test environment try it and let me know how you go? Do not apply this patch to your production servers.

Thanks Andrew. That works successfully, but gives the following message:

Notice: Undefined property: stdClass::$min in /var/www/moodle/course/category.php on line 63

I'm sorry Tony. That's my fault. Can you please try this patch?

That seems fine now Andrew.

Fix committed. Thanks for your help Tony

Inaki reports this is not quite fixed yet ... (discussion in dev chat just now)

Was able to find the discussion in the dev chat logs. Aside from Iñaki Arenaza seeing increasing sortorders there was no additional information. I'm waiting on either more info or the patch he mentioned.

Please see here, in 1.9.9+ the sortorder fields are still overflown: http://tracker.moodle.org/browse/MDL-14718. Thanks for a quick solution.

Any update on this issue?

Responding to Helen's query: FWIW, I could have sworn that the course sort started working for us at about Moodle v1.9.8 or thereabouts, but I just tried it on our main production site (Moodle 1.9.10 (Build: 20101025)) and it did not sort.

A few more (new) details: When I use the "Re-sort courses by name" button, the resulting list is not only not sorted, the text size on the page is bumped up a level or two (it gets bigger), and I get a narrow banner across the top of the page with the word "Success" in it. Clicking on any link takes the text size back to normal. I had thought this effect might be a result of some flakey coding in our custom theme, but it also happens in Chameleon theme.

Thank goodness only site Admins see this.

Moodle v1.9.10 with none of the suggested patches for this issue installed.
Effect noted in both our main production Moodle instance and our sandbox instance.

We just upgraded to v.1.9.12 and our course sorting has suddenly broken. We have over 100 categories with hundreds of courses not sorted.

Oddly, in IE8 after an attempt to sort is made the courses seem to all shift to the left temporarily.

I can confirm it was working fine in our previous version 1.9.8.

update: very odd - was going to test a patch on our dev server and realized that the problem does not occur here. How can that be? We are running the same version on both instances: Moodle 1.9.12+ (Build: 20110511)

We currently have clients that are having this issue also, they are on 1.9.11

Susan, from some of the comments I've seen the sort problem seems to be related to the number of courses you're sorting and how often they've been sorted. A background index number gets bumped up by some significant amount on each sort and when the maximum value has been reached, the sort stops working.

Thanks Donna, yes I figured that out after! Is there a workaround for this right now?

Update. After an exhaustive search through the forums and tracker I applied a change to our database (change column data type for mdl_course.sortorder to bigint).

This change did in fact resolve our issue. We are now able to sort courses using the Re-sort courses by name button and the manual sort using the arrow icons seems to be working as well.

If I understand what the way this was fixed was to just add a really large number to things. I'm wondering if it might help to also take the max number and the min number in the sort column and using that to calculate a new beginning spot. Might that keep the numbers from getting insanely large. I've not looked closely at this so I'm just thinking out loud - not sure if it will be practical or useful. Peace - Anthony

I'm not so sure what I posted is a fix so much as a workaround

U P S T R E A M I Z E D !

Many thanks, this is now available in all the repos (git & cvs).

Closing, ciao

Doh,

somehow this issue was closed incorrectly when processing all the integrated issues this week. (sort of most voted and current in integration filters mix). Apologies for the confusion, reseting to previous status!

Ciao, Eloy

This comment concerns MDL-21730 (Course "sortorder" values in the database get too large for PHP), which was closed, because it duplicates this one. So I’ll post it here; if it’s the wrong place, feel free to delete it.

We had some problems with too big sortorder values, too, so we made two small changes (function fix_course_sortorder), which seem to help a lot. I’m not sure, if you are still interested in information about that problem, so I’ll make it short and put the more detailed information into the attached document.
We are working with Moodle 1.9.15, Oracle-11-DB and Solaris.

1) The case “if ( $mustshift && !$safe && !$hasgap)”:
$mustshift is set true, if ($min < $n-$tolerance || $min > $n+$tolerance+$catgap ), e.g. if the gap between two sortorder-values is too big or too small. In both cases the reaction is to increase the bigger value. For too big gaps the right reaction would be to decrease it. Our solution is described in the attached document (AttachementPostMatthias.txt).

2) The else-case:
This is the more serious problem. In some situations it can happen, that the sortorder values for all courses are calculated newly, starting with 2x the “old” maximum value.
There seems to be a problem with the set_field-command in the foreach-loop. When it is called in non-safe mode it works, in safe mode it hangs up. Maybe a problem with Oracle and transactions? We replaced set_field with execute_sql. The solution is described in the attached document (AttachementPostMatthias.txt).

We made these changes about 10 weeks ago and until now, we had no trouble with it. It seems to work.

Best regards,
Matthias

The uploaded file "AttachementPostMatthias.txt" is just the attachement to my post before.
@admins: feel free to delete it, if you think, it is not helpful.

Best regards,
Matthias

Hi everyone, I'm wondering if people might be able to help me out with trying to fix this issue. Running moodle 1.9.6, don't really want to upgrade to 2+ as the staff here are still getting used to this one. Do any of the 1.9.x versions fix this issue? I've been doing research and people even using 1.9.15 still have it popping up. I checked our mysql database and yes a lot of the sortorder ints were huge and definitely causing the issue. I checked the column properties and it's already set to bigint does anyone have a definitive fix for this and possibly any instructions on how I can implement it?

Thanks all very much for any help!

Hello. I will not be able to work on this issue in the immediate future. In order to create a truer sense of the state of this issue and to allow other developers to have chance to become involved, I am removing myself as the assignee of this issue.

For more information, see http://docs.moodle.org/dev/Changes_to_issue_assignment

If you have any information about this issue or a possible fix please post it here

Hi There,

Support for general bug fixes on the Moodle 1.9 branch ended June 2011 - support for serious security issues on the Moodle 1.9 branch ends Dec 2013

as this is not a security issue this is being closed as "won't fix" - please upgrade to a more recent supported Moodle version.

More information on the support cycles for Moodle versions is available here:
http://docs.moodle.org/dev/Releases#Moodle_1.9

If you find this is still an issue with Moodle 2.5 please create a new bug with information on how to reproduce the issue - thanks.