Delete user ≠ DELETE

Delete user ≠ DELETE

由Peter Möller發表於
Number of replies: 8

I know this topic has been debated before but cannot find any other answer than to use 'moosh', which is what I will probably do (although it doesn't seem to really DELETE them).

I still like to raise the question of language and explain my desire to actually remove stuff from the database.

First, language. To use the word “delete” not only actually means “destroy, blot out, efface” but there's also the SQL statement DELETE with an extremely clear meaning. To use the word “delete” in connection with a database and NOT mean delete will throw anyone who knows anything about databases into a spin. It’s unclear or even deceiving language. A more clear wording would be “hide” or something similar.

Second, what to delete. I understand that for many the moodle forum posts are of importance. Not so for us: we use the plugin CodeRunner a lot. And every time a student press “check” (in total 10-20,000 times per day) a blob that can be several hundred bytes large is stored in the database. Needless to say, it grows fast! Having a database that  never gets pruned gives us longer and longer backup times and also longer restore times. Backup is currently at approx. 30 minutes, which is ok, but the restore times are at 2 hours. That means that we cannot recover from a crash during an exam. So I feel a strong need to remove old stuff from the database. 

Attached is a partial image from a SQL-report that I have written (can be found on GitHub) and there you can see that the largest tables are in our case related to CodeRunner (mdl_question_attempt_…), the standard log and grading. I would really like to cut down on those tables. 

I believed that a proper deletion of a user would also remove stuff from these tables but it seems that nothing happens. 

We have some 12,000 users in our server and almost half of those have not logged in for > 2 years and I would really, really like to delete them. 

附件 Moodle_database_report.png
評比平均分數： -
In reply to Peter Möller

Re: Delete user ≠ DELETE

由Justin Hunt發表於
Particularly helpful Moodlers的相片 Plugin developers的相片
I can't help you delete users. Perhaps someone else has a sneaky script ...
Do you reset your courses? That should remove data related user in that course. That seems the most obvious thing to do
評比平均分數： -
In reply to Justin Hunt

Re: Delete user ≠ DELETE

由Peter Möller發表於

We have been doing that but the teachers find it cumbersome and the work usually time out. If I as an admin can simply prune users the teachers can forget about it. I recently helped a teacher delete a large course using admin/cli/delete_course.php and it took 55 minutes! Granted the SAN the university VM:s are using is pretty slow but I’m unwilling to trade away the redundancy that VM:s give. 

BTW: we are running moodle 4.1.12+

評比平均分數： -
In reply to Peter Möller

Re: Delete user ≠ DELETE

由Marcus Green發表於
Core developers的相片 Particularly helpful Moodlers的相片 Plugin developers的相片 Testers的相片
As a little background, Moodle does not implement pk/fk relationships in the database. If they are in the source xml they are discarded on install. So any deletion of related records must happen in the PHP code.

評比平均分數：Useful (1)
In reply to Peter Möller

Re: Delete user ≠ DELETE

由Howard Miller發表於
Core developers的相片 Documentation writers的相片 Particularly helpful Moodlers的相片 Peer reviewers的相片 Plugin developers的相片
You can, but you're going to have to battle with the - in my opinion - dreadful data privacy functions of Moodle.

Have fun - Data_privacy
評比平均分數： -
In reply to Howard Miller

Re: Delete user ≠ DELETE

由Lexy Walker發表於
Core developers的相片
This.

Moodle's standard "delete" function doesn't actually delete users. It just scrambles their account details. This is because there are a lot of other database tables that point to the user account.

If you want to actually delete people, you'll need to use Moodle's built-in GDPR tools. They are not friendly.

There is an option in Moodle's settings to automatically create a GDPR delete request when an account is deleted. That's probably the easiest way to do this.

After you've deleted user accounts the standard Moodle way, just go to the 'Data Requests' page and approve them all.

I wrote a horrible NodeJS / Puppeteer script to automate this. I could share this script, but it does require you to set up NodeJS, install a few packages and create an environment file. It also might need customised for different site themes if you're not using a standard Boost-based theme. It's not a beginner-friendly script.
評比平均分數：Useful (1)
In reply to Peter Möller

Re: Delete user ≠ DELETE

由Ken Task發表於
Particularly helpful Moodlers的相片

Take it this is a dedicated DB server?  What are specs of that box?

Suggest installing MySQLTuner and run it with super user creds from both the Web server and the DB server itself.

Of the tables you've shown only the logstore_standard_log can 'safely' remove records as that table is the 'who done it' table and don't think there is any relationship with any other table.

'SoS', Ken

評比平均分數： -
In reply to Ken Task

Re: Delete user ≠ DELETE

由Peter Möller發表於
No, it’s on the same VM as moodle (but in a separate docker container). Specs are normally 4 vCPU and 16 GB of RAM. Storage is on the central SAN and delivers ≈ 100 MB/sec but being a SAN there’s lots of heads working on the platters.
For exams we increase both CPU and RAM; come this June, we will have an exam with 800 students (running CodeRunner) and will probably have 8 or 12 vCPU and 64 GB of RAM. Last June we had an exam with 600 students and 8/64 and it went smooth as silk; I believe the entire DB was in RAM (or at least the parts that were “moving”).

I have been thinking about moving the DB to a physical box for performance reasons, but then I’ll lose the physical redundancy the VM provides and that is very un-appealing. Having the DB on a different VM-server seems to be no win: the SAN is the bottleneck and all VM share the same SAN.
評比平均分數： -