Hi,
I have been trying to figure out why "completion_daily_task" is not working/finishing. After many hours of debugging this problem I think I may have found the issue.
First I found the problem query:
SELECT
c.id AS course,
u.id AS userid,
crc.id AS completionid,
ue.timestart AS timeenrolled,
ue.timecreated
FROM
mdl_user u
INNER JOIN
mdl_user_enrolments ue
ON ue.userid = u.id
INNER JOIN
mdl_enrol e
ON e.id = ue.enrolid
INNER JOIN
mdl_course c
ON c.id = e.courseid
INNER JOIN
mdl_role_assignments ra
ON ra.userid = u.id
LEFT JOIN
mdl_course_completions crc
ON crc.course = c.id
AND crc.userid = u.id
WHERE
c.enablecompletion = 1
AND crc.timeenrolled IS NULL
AND ue.status = 0
AND e.status = 0
AND u.deleted = 0
AND ue.timestart < ?
AND (ue.timeend > ? OR ue.timeend = 0)
AND ra.roleid IN (5,9)
ORDER BY
course,
userid
I noticed that this query results are being stored in a PHP variable name "rs", so I took that query and manually ran it against the mysql db and stored the results to a local file. Then I checked the size of the file and discovered it had a size of "2683650313" and "99348759" rows. Then i did some checking and it appears that php 7.1.2 has a max variable size of 2^31 (2147483648), so it appears that the query is overflowing the varialbe "rs", I have not seen any errors when this happens. The task just drops out. When this happens in the normal cron to schedule the task it make all other tasks quite running and I have to disable the "completion_daily_task" in order to get cron working again.
My question is has anybody else had this problem and is there a way to limit the query so it will complete?
Sample of the results:
course userid completionid timeenrolled timecreated
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 1119 NULL 0 1563283931
9 2015 NULL 0 1574245574
9 2015 NULL 0 1574245574
9 2015 NULL 0 1574245574
9 2015 NULL 0 1574245574
9 2015 NULL 0 1574245574
9 2015 NULL 0 1574245574
9 2015 NULL 0 1574245574
9 2015 NULL 0 1574245574
9 2015 NULL 0 1574245574
9 2015 NULL 0 1574245574
Regards,
Note I am running Moodle 3.5 (Build: 20180531)
