[#MDL-8550] enrol/database/enrol_database_sync.php unenrolls everyone!

Issue Details (XML | Word | Printable)

Key:	MDL-8550
Type:	Bug
Status:	Closed
Resolution:	Fixed
Priority:	Critical
Assignee:	Eloy Lafuente (stronk7)
Reporter:	Clinton Graham
Votes:	1
Watchers:	2

Operations

Add/Edit UI Mockup to this issue

If you were logged in you would be able to see more operations.

Moodle

enrol/database/enrol_database_sync.php unenrolls everyone!

Created: 17/Feb/07 04:19 AM Updated: 13/May/08 04:45 PM

Return to search

Component/s:

Enrolments

Affects Version/s:

1.7.4, 1.8.5, 1.9

Fix Version/s:

1.8.6, 1.9.1

File Attachments:

database_enrol.php.patch.txt (2 kB)
2.

enroll.php.patch (2 kB)

Database:	Any
Participants:	C. Lopez, Clinton Graham, Eloy Lafuente (stronk7), Martin Dougiamas, Neil Streeter, Nicolas Connault, Petr Skoda and Prabir Choudhury
Security Level:	None
QA Assignee:	Petr Skoda
Resolved date:	12/May/08
Affected Branches:	MOODLE_17_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE
Fixed Branches:	MOODLE_18_STABLE, MOODLE_19_STABLE

Description

« Hide

After successfully assigning users in enrol/database/enrol.php's sync_enrolments(), the script procedes to role_unassign() all users managed by the database enrollment plugin. The SQL in the section following the comment "// prune enrolments to courses that are no longer in ext auth", uses a left outer join of role_assignments on a join of context on course. The outer join returns a resultset of all database role assignments, and passes these off for unassignment.

Line 351 of enrol/database/enrol.php (version 1.32) should read:
351 JOIN ({$CFG->prefix}context cn
not the current
351 LEFT OUTER JOIN ({$CFG->prefix}context cn

The above change will affect the stated purpose to "prune enrolments to courses that are no longer in ext auth".

I'm guessing that the intention of the outer join was probably to find roles associated with a missing course or context. If this is necessary, some other implementation will be needed.

All

Comments

Change History

Version Control

Sort Order:

[ Permalink | « Hide ]

Nicolas Connault added a comment - 05/Apr/07 02:54 PM

Another enrol one.

[ Permalink | « Hide ]

C. Lopez added a comment - 07/Sep/07 09:24 AM

I have been looking into this, and it is my feeling that the LEFT JOIN statement is theoretically correct, but the problem (in my case, anyway) is that versions pf MySQL before 5.0.1 do not support nested joins. Here we have a command of the form

SELECT * FROM RA LEFT JOIN (C, CN)

And this is a form that explicitly not allowed in MySQL prior to 5.0.1. Basically, these versions of MySQL will just ignore the parentheses. That's what's resulting in this behavior.

Since Moodle is supposed to support MySQL 4.1, the query will have to be rewritten. I think a query of the form

SELECT * FROM RA LEFT JOIN CN LEFT JOIN C

That is,

"SELECT ra.roleid, ra.userid, ra.contextid
FROM {$CFG->prefix}role_assignments ra
LEFT JOIN {$CFG->prefix}context cn
ON ra.contextid = cn.id AND cn.contextlevel = ".CONTEXT_COURSE."
LEFT JOIN {$CFG->prefix}course c ON cn.instanceid = c.id
WHERE ra.enrol = 'database'" .....

will do what was intended here, though it may not be formally equivalent.

[ Permalink | « Hide ]

Martin Dougiamas added a comment - 04/Mar/08 05:20 PM

Hmm, Martin is this still a problem?

[ Permalink | « Hide ]

Prabir Choudhury added a comment - 02/May/08 06:47 AM

there is a bug in Mysql 5.0.3a. to get details plz visit http://bugs.mysql.com/bug.php?id=25575

[ Permalink | « Hide ]

C. Lopez added a comment - 02/May/08 07:24 AM

I don't think this MySQL bug is relevant to this Moodle bug.

[ Permalink | « Hide ]

Neil Streeter added a comment - 04/May/08 12:29 PM

It is indeed still a problem. I should have looked here first :-/ Serves me right...

I am using a version of mysql prior to 5.0.1.... I'll take a look at what C. Lopez has suggested..

http://moodle.org/mod/forum/discuss.php?d=94586

[ Permalink | « Hide ]

Eloy Lafuente (stronk7) added a comment - 05/May/08 05:27 AM - edited

Well,

IMO that script shouldn't try to "fix" (or clean) enrolments in orphan contexts or courses (that's another problem out from the database enrol itself).

So I've transformed the problematic query into one common INNER JOIN so it simply returns the role assignments in moodle courses that aren't any more in external DB. Just that.

I haven't tested code (patch attached for Moodle 1.9) but should work fine, AFAIK.

If somebody can test it I'll commit it to as many releases as possible.

TIA and ciao