twoscope / moodle-report_completionoverview Goto Github PK

HI -

I installed the plugin on Moodle 3.7 (AWS Server )

I seem to get a server time out error when to running the report - server seems top freeze for 20 minutes
Error from browser
"The gateway did not receive a timely response from the upstream server or application."
Any Suggestions??

Thanx in advance

Hi We're using your moodle plugin course completion overview plugin (2017111702) in a moodle 3.4. Unfortunatly we're getting following error when trying to access the report:

Debug info: Incorrect key file for table '/tmp/#sql_8b8_0.MYI'; try to repair it
SELECT DISTINCT cr.id AS courseid, cr.fullname AS coursename,
COUNT(DISTINCT ra.id ) AS enrols,
COUNT(DISTINCT cc.timecompleted) AS completed
FROM aca_course cr
JOIN aca_context ct ON ( ct.instanceid = cr.id )
LEFT JOIN aca_role_assignments ra ON ( ra.contextid = ct.id ) and ra.roleid = 5
LEFT JOIN aca_course_completions cc ON cc.course = cr.id
WHERE ct.contextlevel = ? AND cr.id <> ? AND cr.enablecompletion = ?
GROUP BY cr.fullname, cr.id
ORDER BY coursename
[array (
0 => '50',
1 => '1',
2 => '1',
)]
Error code: dmlreadexception
×Stack trace:
line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 1216 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 67 of /report/completionoverview/index.php: call to mysqli_native_moodle_database->get_records_sql()

I'm not sure if this project is still active, but we found this plugin and decided to give it a try with a new moodle install. During testing, it worked great, but once we imported approx 3 courses, and 3,000 users, we started having issues: Anytime anyone would access the report, the page would timeout and generate a 503 error.

After digging in the logs, we found that the $compdatasql query was examining a little over 13,000,000 rows when it ran, which is a little insane for our current dataset.

The solution we came up with was to alter the query from:

$compdatasql = "SELECT DISTINCT cr.id AS courseid, cr.fullname AS coursename,
                COUNT(DISTINCT ra.id ) AS enrols,
                COUNT(DISTINCT cc.timecompleted) AS completed
                FROM {course} cr
                JOIN {context} ct ON ( ct.instanceid = cr.id )
                LEFT JOIN {role_assignments} ra ON ( ra.contextid = ct.id ) and ra.roleid = 5
                LEFT JOIN {course_completions} cc ON cc.course = cr.id
                WHERE ct.contextlevel = ? AND cr.id <> ? AND cr.enablecompletion = ?
               GROUP BY  cr.fullname, cr.id
               ORDER BY coursename";

to

$compdatasql = "SELECT DISTINCT cr.id AS courseid, cr.fullname AS coursename,
                COUNT(DISTINCT ra.id ) AS enrols,
                COUNT(DISTINCT cc.timecompleted) AS completed
                FROM {course} cr
                JOIN {context} ct ON ( ct.instanceid = cr.id )
                LEFT JOIN {role_assignments} ra ON ( ra.contextid = ct.id ) and ra.roleid = 5
                LEFT JOIN {course_completions} cc ON cc.course = cr.id AND ra.userid = cc.userid
                WHERE ct.contextlevel = ? AND cr.id <> ? AND cr.enablecompletion = ?
               GROUP BY  cr.fullname, cr.id
               ORDER BY coursename";

While testing, I needed to delete and re-create a couple test user accounts and now the course completion counts are off.

Any ideas on how to resolve this? Thanks!