Moodle in English: Orphaned grades

Orphaned grades

by stefan weber - Monday, May 20, 2019, 8:30 PM

Like described in this post: https://moodle.org/mod/forum/discuss.php?d=386490, I have a problem with deleting users in Moodle.

I tracked it down to grades in the DB that seem to be orphaned - does anyone know if this is a known Moodle Bug?

What happens is:

1) I cannot delete some users on the Moodle Interface, it will give an "invalid course id" error

2) in the DB table grade_grades, there are grades recorded for these users, even though no grades show up in their Moodle profile, and they are no longer enrolled in any existing courses (their courses have been already deleted)

3) If I manually delete the records associated with the userid from the DB table grade_grades, I am able to delete the user in Moodle

Re: Orphaned grades

by stefan weber - Monday, May 27, 2019, 8:24 PM

I analyzed the problem further, but now I don't really know how to proceed.

The problem is, we have a lot of records in mdl_grade_grades whose itemid does not exist in mdl_grade_items.

If I run this SQL query:

SELECT mdl_grade_grades.id, itemid, userid, courseid
FROM mdl_grade_grades
LEFT OUTER JOIN mdl_grade_items
ON mdl_grade_grades.itemid = mdl_grade_items.id
WHERE mdl_grade_items.courseid IS NULL

at our site, it returns more than 43000 records. I got two other Moodle admins at other sites to run it, and they only came up with 1 or 2 records, so it seems that it is a site-specific problem.

We found out that we can find the missing itemids in mdl_grade_items_history, but that just made the whole problem even more perplexing:

according to mdl_grade_items_history.timemodified, all the orphaned records have been modified between 07/2013 and 10/2014
according to mdl_grade_grades.courseid, only old courses are affected - most of the affected courses have actually already been deleted
BUT: according to grade_grades.userid, the grades belong to new users as well. the newest user has only been created in 09/2018

can anyone with more knowledge about how moodle grades work in the database explain this?

Re: Re: Orphaned grades

by Richard van Iwaarden - Tuesday, May 28, 2019, 5:50 PM

Hi Stefan,

Your Moodle site has been running for a while, so this might have been the problem in 2013-2014: MDL-36024

What we experienced in 2013-2014 was total horror. When students were re-enrolled into courses using any other enrollment method than manual enrollment, all grades were lost for that specific student.

Did you use other enrollment methods in 2013-2014 (for example cohort or metacourse?)

Re: Re: Orphaned grades

by stefan weber - Tuesday, May 28, 2019, 6:41 PM

hhhm, sounds like a different problem - we only used manual enrolment for the most part, with a few self-enrolments at most.

it definitely could be a quite old problem - like I said, the timemodified points to the time we were using 2.4, and ends when we upgraded to 2.8, so it could be leftovers from some 2.4-specific problem.

the big question is however: why are there new userids? maybe those are wrong as well? i will watch the DB carefully over the next few weeks and see if more such records are added, or if the number stays at its current level. maybe I will try to restore a DB dump on the testserver with data from 2 years back, then I could be sure that the number of faulty record stays the same ever since we upgraded to 2.8...

I'm still unsure on how to proceed - seems I got two options if I want to start deleting old users:

1) i manually delete the orphaned records from grade_grades
2) i change the function that writes events upon user deletion so it doesnt crash when courseid is NULL

not sure which option is less risky... :/

Re: Re: Orphaned grades

by stefan weber - Wednesday, June 12, 2019, 5:15 PM

i just checked again - since end of may, the number of such orphaned records in our DB increased further, from 43102 to 43247

so it seems like the problem persists... i have no real idea how to fix this

Re: Orphaned grades

by stefan weber - Friday, June 14, 2019, 2:59 PM

in the thread on the general forum, someone finally linked me to the issue causing this: https://tracker.moodle.org/browse/MDL-51694

seems like it's an 4 year old known and unfixed bug...

Gradebook

Orphaned grades