Moodle in English: Cannot delete some users

Cannot delete some users

by stefan weber - Friday, May 17, 2019, 7:44 PM

Using Moodle Moodle 3.6.3+ (Build: 20190410)

Im in the process of manually deleting old users, and came across a couple of users that cannot be deleted.

User profile looks normal, users are no longer enrolled in any existing courses, if I click on Grade Overview in the user profile I just get You are not enrolled in, nor teaching any courses on this site.

Can anyone link that to any known bug, or should I try to reproduce the issue and make a Tracker item?

-----------------------------------------

Error output:

Can not find data record in database table course.

Debug info: SELECT id,category FROM {course} WHERE id IS NULL
[array (
)]
Error code: invalidrecord

×Stack trace:

line 1546 of /lib/dml/moodle_database.php: dml_missing_record_exception thrown
line 1522 of /lib/dml/moodle_database.php: call to moodle_database->get_record_select()
line 6813 of /lib/accesslib.php: call to moodle_database->get_record()
line 70 of /lib/classes/event/grade_deleted.php: call to context_course::instance()
line 1119 of /lib/grade/grade_grade.php: call to core\event\grade_deleted::create_from_grade()
line 1532 of /lib/gradelib.php: call to grade_grade->delete()
line 4164 of /lib/moodlelib.php: call to grade_user_delete()
line 107 of /admin/user.php: call to delete_user()

×Output buffer: <div class="notifytiny debuggingmessage" data-rel="debugging">Class coursecat is now alias to autoloaded class core_course_category, course_in_list is an alias to core_course_list_element. Class coursecat_sortable_records is deprecated without replacement. Do not include coursecatlib.php<ul style="text-align: left" data-rel="backtrace"><li>line 31 of /lib/coursecatlib.php: call to debugging()</li><li>line 26 of /blocks/open_courses/block_open_courses.php: call to include_once()</li><li>line 2054 of /lib/blocklib.php: call to include_once()</li><li>line 2016 of /lib/blocklib.php: call to block_load_class()</li><li>line 90 of /lib/classes/plugininfo/block.php: call to block_instance()</li><li>line 69 of /admin/settings/plugins.php: call to core\plugininfo\block->load_settings()</li><li>line 8065 of /lib/adminlib.php: call to require()</li><li>line 7952 of /lib/adminlib.php: call to admin_get_root()</li><li>line 24 of /admin/user.php: call to admin_externalpage_setup()</li></ul></div> Notice: Trying to get property 'itemtype' of non-object in /var/www/moodle/lib/grade/grade_grade.php on line 1160 Notice: Trying to get property 'courseid' of non-object in /var/www/moodle/lib/classes/event/grade_deleted.php on line 70

Re: Cannot delete some users

by Rick Jerz - Sunday, May 19, 2019, 6:45 AM

No sure. Are you manually deleting users in the database, or from the Moodle interface?

Average of ratings: -

Re: Re: Cannot delete some users

by stefan weber - Monday, May 20, 2019, 4:46 PM

from the moodle interface - how else would i even get an error on the moodle interface

Average of ratings: -

Re: Cannot delete some users

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

so from what I've gathered so far it looks like it fails when trying to create an event about a deleted grade it looks up the course context of the grade item and that is NULL, thus the DB query fails

Average of ratings:Useful (1)

Re: Cannot delete some users

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

I found the problem in the DB, but I don't know how this happened in the first place - since I seem to have a lot of old users with this problem, I still wonder if this is a Moodle Bug, or if something went wrong in our DB at some point.

In DB table mdl_grade_grades, there were still some grades recorded for this userid, even though no grades showed up in his user profile, and he was not enrolled in any courses any more.

I wonder how that happened, and I still do not know how to proceed - it's not practical to delete all these orphaned grades manually for all the users that have this problem (which seem to be quite a lot)...

Average of ratings:Useful (1)

Re: Re: Cannot delete some users

by Rick Jerz - Monday, May 20, 2019, 9:24 PM

I don't know, Stefan. If you are able to create a reproducible situation that causes the error, you should probably create a bug report in the Tracker system. What you describe has never happened to me, but I am a very small sample of 1.

Average of ratings: -

Re: Re: Cannot delete some users

by Ken Task - Monday, May 20, 2019, 9:45 PM

Haven't experienced same ... so can't give an answer ... do have a suggestion though ... when was the last time you checked your DB ... character set/collations for all tables. A miss-match there could cause some strange behavior.

'SoS', Ken

Average of ratings: -

Re: Cannot delete some users

by stefan weber - Tuesday, May 21, 2019, 7:25 PM

so i tracked down the problem to:

- there are entries in mdl_grade_grades which have an itemid that does not exist in mdl_grade_items
- mdl_grade_items links grade items to the courseid of the course
- on user deletion, moodle tries to create an event that includes the courseid of the deleted grade
- if there is a grade for a user in mdl_grade_grades which does not have an associated grade_item, the courseid is NULL, thus the DB query fails, and the deletion is cancelled
- in our DB there are 47000 grades in mdl_grade_grades which do not have an associated grade_item (!)
- those 47000 grades are distributed over more than 4000 users (our user total is 16000). not only old users, but plenty of new ones (!!)
- this means that 1/4 of all the users at our site can never be deleted (!!!)

since new users are also affected, my best guess is that this is either a Moodle bug or a bug in a plugin we have installed.

Please try to verify if your site has this problem as well by following these steps:

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

If this query returns data records, then all of the userids in these records will be undeletable

Please report back so I can see if this is an isolated problem at our site, or a bug in some installed plugin, or a general Moodle bug!

Average of ratings:Useful (1)

Re: Re: Cannot delete some users

by Luis de Vasconcelos - Tuesday, May 21, 2019, 8:22 PM

My Moodle 3.5.2+ MSSQL/SQLSRV database returns 1 record for that query:

id itemid userid courseid
4753834 13882 57155 NULL

So what is that itemid on my database? I run this to find out:

select *
from mdl_grade_items
where id = 13882

but that query returns 0 records.

So if it is a bug it is in Moodle core, not in one of your plugins. You should report it in the Moodle Tracker...

And the other big question is: what are the repercussions of having this "bad data" in our Moodle databases?

Average of ratings: -

Re: Re: Re: Cannot delete some users

by stefan weber - Tuesday, May 21, 2019, 8:55 PM

the repercussion is, you cannot delete the user with the userid 57155

is this the only grade that turned up in your DB? like I said, we have over 47000 records like that

Average of ratings:Useful (1)

Re: Re: Re: Re: Cannot delete some users

by Luis de Vasconcelos - Tuesday, May 21, 2019, 11:40 PM

Yes, luckily I only got one result from that query.

Unfortunately my Moodle QA/Test environment doesn't have that bad data, so I'll have to refresh the environment from prod before I can test what happens when I try to delete that 57155 user.

Please report this on https://tracker.moodle.org. I think it's worth further debugging by the Moodle developers. Bad data like this could potentially cause problems with future Moodle upgrades.

Average of ratings: -

Re: Re: Re: Re: Re: Cannot delete some users

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

thanks for reporting back. another moodle admin reported finding 2 records, so it seems that the problem is specific to our site, and otherwise only occurs in very rare cases.

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

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?

Average of ratings: -

Re: Re: Re: Re: Re: Cannot delete some users

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

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

Average of ratings: -

Re: Cannot delete some users

by Tobias Marx - Thursday, June 13, 2019, 10:33 PM

Hi!

We've got the same problem. Your query returns 1655 hits in our database.

There is a (probably) related Tracker issue for this problem from 2015...: https://tracker.moodle.org/browse/MDL-51694

Tobias

Average of ratings:Useful (1)

Re: Cannot delete some users

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

thank you so much for bringing this up! finally i know that its a known bug and not just our site, and know that i can safely delete those orphaned records. i was increasingly worried something is wrong just at our site.

Average of ratings: -

Re: Cannot delete some users

by Damian Nogueiras - Thursday, September 19, 2019, 1:06 AM

Thanks for the SELECT.

I have the same problem, but only with a few user.

Some tip: when yo delete a user, the first time, moodle return error, and second time moodle delete the user.

Need delete twice the user

Average of ratings: -

Re: Cannot delete some users

by stefan weber - Thursday, September 19, 2019, 2:16 PM

thats a different issue then - with the DB issue, no matter how often you try, Moodle will just display the error

Average of ratings: -

Re: Cannot delete some users

by Damian Nogueiras - Thursday, September 19, 2019, 5:27 PM

I was wrong, depends of the user. For example (see the attach image, it's a extract of my select),

the user 1386 need twice, the user 1533 need four times.

I had this problem this days, because I deleted a lot off users of my ldap and the sync user task returned the error.

Then I deleted the users manually (run the task manually). I needed re-run the task when moodle wanted delete the user.

I don`t know if it`s the same issue, but thanks a lot for clarify myself with your contributions.

Average of ratings: -

General help

Cannot delete some users