Cannot assign system roles when there are many registered users

I could not reproduce it on my machine.
It might be some memory problem with SQL query.

Couple of things that I would change to make the query go faster:
1) In role_assignment table I would alter an index on roleid and add also contextid to it.
2) In potential_assignees_course_and_above::find_users the table() reference to table

in subquery is not necessary:

        $sql = " FROM { user }
                WHERE $wherecondition
                      AND id NOT IN (
                         SELECT u.id
                           FROM { role_assignments } r, { user } u
                          WHERE r.contextid = :contextid
                                AND u.id = r.userid
                                AND r.roleid = :roleid)";

also there are several more queries like that

This is a regression introduced in 2.2

Except for fixing the bug itself, I have also changed two queries so they run faster on multi-user installations.

For integrators: Please note that in master branch there is also a second commit with DB changes that adds indexes to role_assignments table also to speed up queries. You may reject this commit if you wish

+1 - thanks Marina!

The main moodle.git repository has just been updated with latest weekly modifications. You may wish to rebase your PULL branches to simplify history and avoid any possible merge conflicts. This would also make integrator's life easier next week.

TIA and ciao

Hi Marina,
thanks for the patch, its been integrated.

I was wondering why we weren't backporting the new indexes - it may be that we aren't supposed to make db changes to stable, but these being non-unique i've raised MDL-31191 for considering backporting to 22. It may be worthwhile to check with Eloy (the db guru).

anyway , this is up for testing now.

I loaded up a system with 3000 users, but it wasn't enough to replicate the problem. I had a talk with Marina and she said that I should check to make sure that the new code didn't have any regressions. I assigned system roles to users and within a course with less than 100 and more than 100 users.
I couldn't find any problems.
Test passed.

This is now available in the git and cvs repositories.

Consider the responsibility of your fingerprints engraved there for future generations!

Thanks for the work, closing, ciao