Moodle in English: Error reading from database

Teaching with Moodle

Error reading from database

This discussion has been locked because a year has elapsed since the last post. Please start a new discussion topic.

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

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()

I opened a ticket in the help line of my web hosting service provider and submitted the debug info. The tech support came back with this answer:

Hello,

This error means that your MySQL query would examine more than 1000000000 (one billion) rows.  For performance reasons our servers do not allow this.  You will either need to remove un-needed data from the tables listed in the query, or rewrite the MySQL query so it does not examine so many rows.

Thank you,
Charles
Level II Tech Support Engineer

Is there a configuration within Moodle that will avoid this error trap from being triggered?

Thanks, Pabs

Average of ratings: -

Permalink | Reply