General help

the class core_grade\privacy\provider produces long running query

 
Picture of Amy Stewart
the class core_grade\privacy\provider produces long running query
 

We have had a few data requests, and a query is getting produced in the class core_grade\privacy\provider which locks our MySQL database.  The query looks like the following in the cron log:

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
JOIN (
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
AND (
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
[array (
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.


 
Average of ratings: Useful (1)
foto_perfil
Re: the class core_grade\privacy\provider produces long running query
 

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.





 
Average of ratings: -
Picture of Amy Stewart
Re: the class core_grade\privacy\provider produces long running query
 

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.

 
Average of ratings: Useful (2)
foto_perfil
Re: the class core_grade\privacy\provider produces long running query
 

Hello Ammy,


I already voted and I am following. 

Thanks for sharing.

 
Average of ratings: -