I get this error when I export grades to Excel. The grades table contains 13 fields and 169 rows (users). I enabled debug in Moodle and the Debug Info dump looks like this:
Debug info: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
SELECT u.*, cf0.data AS 'customfield_position', cf1.data AS 'customfield_branch' , u.lastname AS usrt1, u.firstname AS usrt2, u.id AS usrt
FROM mdle2_user u
JOIN (SELECT DISTINCT eu1_u.id
FROM mdle2_user eu1_u
JOIN mdle2_user_enrolments eu1_ue ON eu1_ue.userid = eu1_u.id
JOIN mdle2_enrol eu1_e ON (eu1_e.id = eu1_ue.enrolid AND eu1_e.courseid = ?)
WHERE eu1_u.deleted = 0 AND eu1_u.id <> ?) je ON je.id = u.id
LEFT JOIN (SELECT * FROM mdle2_user_info_data
WHERE fieldid = ?) cf0
ON u.id = cf0.userid
LEFT JOIN (SELECT * FROM mdle2_user_info_data
WHERE fieldid = ?) cf1
ON u.id = cf1.userid
JOIN (
SELECT DISTINCT ra.userid
FROM mdle2_role_assignments ra
WHERE ra.roleid = ?
AND ra.contextid IN (70,43,1)
) rainner ON rainner.userid = u.id
WHERE u.deleted = 0
ORDER BY usrt1 ASC, usrt2 ASC, usrt ASC
[array (
0 => '9',
1 => '1',
2 => '3',
3 => '4',
4 => '5',
)]
Error code: dmlreadexception
SELECT u.*, cf0.data AS 'customfield_position', cf1.data AS 'customfield_branch' , u.lastname AS usrt1, u.firstname AS usrt2, u.id AS usrt
FROM mdle2_user u
JOIN (SELECT DISTINCT eu1_u.id
FROM mdle2_user eu1_u
JOIN mdle2_user_enrolments eu1_ue ON eu1_ue.userid = eu1_u.id
JOIN mdle2_enrol eu1_e ON (eu1_e.id = eu1_ue.enrolid AND eu1_e.courseid = ?)
WHERE eu1_u.deleted = 0 AND eu1_u.id <> ?) je ON je.id = u.id
LEFT JOIN (SELECT * FROM mdle2_user_info_data
WHERE fieldid = ?) cf0
ON u.id = cf0.userid
LEFT JOIN (SELECT * FROM mdle2_user_info_data
WHERE fieldid = ?) cf1
ON u.id = cf1.userid
JOIN (
SELECT DISTINCT ra.userid
FROM mdle2_role_assignments ra
WHERE ra.roleid = ?
AND ra.contextid IN (70,43,1)
) rainner ON rainner.userid = u.id
WHERE u.deleted = 0
ORDER BY usrt1 ASC, usrt2 ASC, usrt ASC
[array (
0 => '9',
1 => '1',
2 => '3',
3 => '4',
4 => '5',
)]
Error code: dmlreadexception
Stack trace:
- line 426 of /lib/dml/moodle_database.php: dml_read_exception thrown
- line 960 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
- line 209 of /grade/lib.php: call to mysqli_native_moodle_database->get_recordset_sql()
- line 241 of /grade/export/lib.php: call to graded_users_iterator->init()
- line 59 of /grade/export/xls/index.php: call to grade_export->display_preview()