Deleting user data from the database

Question

I'm using Craft 3.6.4.1 and I need to delete a whole tranche of users from the database (that has derived from another Craft instance).

I've been deleting by hand via the CMS but note that loads of user related rows remain in the database:

• The CMS is reporting only the 4 users which I want to retain, but craft_users still has 10,706 rows in it.

From which tables should I clean out rows that pertain to users I want to delete?

I note the additional following tables which seem relevant in ways I'm unsure of:

craft_usergroups_users
craft_userpermissions
craft_userpermissions_usergroups
craft_userpermissions_users
craft_userpreferences

Answer 1

After deleting the users from the UI they are only soft-deleted. If you look at the user id and find the matching element in your elements table, you will see that this element has a populated dateDeleted.

Run garbage collection (./craft gc) to clean up and completely remove everything.

Answer 2

You could also use your general config file to set the softDeleteDuration to a very low value, so that soft deleted elements get garbagecollected quickly:

'softDeleteDuration' => 300, // 5 Mins

or if you're up to the command line, use

./craft gc --delete-all-trashed

to immediately hard delete all soft deleted items

Answer 3

I read a comment from Brandon somewhere that the best way to delete at the database level is to remove rows from craft_elements, which then does the housekeeping around that.

I'm not sure if it is still necessary to run garbage collection as per @Oli's answer above, but I have found that even after doing that rows in craft_usergroups_users persist.

I am therefore doing the following:

DELETE FROM craft_elements
WHERE `type` = 'craft\\elements\\User'
AND `id` NOT IN (/* array of IDs I want to keep*/)

…and then…

DELETE FROM craft_usergroups_users
WHERE `userId` NOT IN (/* array of IDs I want to keep */)