SELECT ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
FROM mdl_context ctx
SELECT DISTINCT ctx.id
FROM mdl_grade_items gi
JOIN mdl_context ctx
ON ctx.instanceid = gi.courseid
AND ctx.contextlevel = ?
LEFT JOIN mdl_grade_grades gg
ON gg.itemid = gi.id
AND (gg.userid = ? OR gg.usermodified = ?)
LEFT JOIN mdl_grade_grades_history ggh
ON ggh.itemid = gi.id
ggh.userid = ?
OR ggh.loggeduser = ?
OR ggh.usermodified = ?
WHERE gg.id IS NOT NULL
OR ggh.id IS NOT NULL) target ON ctx.id = target.id
0 => 50,
1 => 3,
2 => 3,
3 => 3,
4 => 3,
5 => 3,
In a small database, this isn't a problem, and the query completes in a reasonable amount of time, however, in our large database (2+ million rows in the mdl_grade_grades_history table), the query can never complete, and it locks up our MySQL server. Has anyone run into this issue before?
We are running Moodle in a corporate training environment, and have our grade history set to 1000 days, which I realize could likely be lowered. However, the people who make the decisions want to keep the historical data in Moodle so that students can access it. We are currently using Moodle 3.5.2.
I have the same problem, after upgrade my Moodle from 3.1.8 to 3.3.8.
Today(22/11/2018) I upgraded to version 3.3.9+, but the messages keep showing.
Generating the contexts containing personal data for the user...
Fetching 482 components (quinta, 22 Nov 2018, 15:25)
Processing core_grades (447/482) (quinta, 22 Nov 2018, 15:28)
Here mysql locks up.
I opened up a tracker and submitted a fix for the query. You can find it at https://tracker.moodle.org/browse/MDL-64027. I hope they consider adding the fix to core code. It would help if you voted for the tracker! I'd appreciate it.
I already voted and I am following.
Thanks for sharing.