Listing users for a course fails with a 'dmlreadexception' when navigating to 'Enrolled Users' in a course administration block.
- Set up a new Moodle install using MSSQL as the database
- Create a course (as Admin)
- Select the newly created course (as Admin)
- Using the Settings block, navigate to 'Course Administration'
Expected: a list of users for the course, or an interface to enrol users.
Got: An error message 'Error reading from database' and a link to an unhelpful web page.
A debug stack trace is as follows:
Debug info: SQLState: 42000<br>
Error Code: 421<br>
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]The ntext data type cannot be selected as DISTINCT because it is not comparable.<br>
SELECT DISTINCT TOP 100u.*, ul.timeaccess AS lastseen
FROM mdl_user u
JOIN mdl_user_enrolments ue ON (ue.userid = u.id AND ue.enrolid = 1)
JOIN mdl_enrol e ON (e.id = ue.enrolid)
LEFT JOIN mdl_user_lastaccess ul ON (ul.courseid = e.courseid AND ul.userid = u.id) ORDER BY u.lastname ASC, u.firstname ASC
0 => 1,
- line 390 of \lib\dml\moodle_database.php: dml_read_exception thrown
- line 261 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
- line 371 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
- line 786 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
- line 864 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
- line 202 of \enrol\locallib.php: call to sqlsrv_native_moodle_database->get_records_sql()
- line 913 of \enrol\locallib.php: call to course_enrolment_manager->get_users()
- line 225 of \enrol\users.php: call to course_enrolment_manager->get_users_for_display()
Output buffer: get_users
This appears to be a MSSQL specific problem, as the same functionality works as expected on an identical Moodle instance using MYSQL as a database.
Is it related to http://tracker.moodle.org/browse/MDL-21874?