Routine maintenance coming, 2021-04-04

[crfriend]

It's long overdue, and it's time for the occasional purge of deeply-inactive user accounts (many of which are spammer throwaway accounts anyway).

Here are the criteria for account deletion:

1) The account must have been created one year or more in the past,
2) The account cannot have seen a login in the past year, and
3) The account can have no posts registered.

This is a Boolean AND operation: ALL THREE cases must be TRUE for the deletion to happen. This is so designed to allow "lurkers" (who have no posts) to remain active so long as they've logged on in the past year but to get rid of "dead wood" lest it cause a fire.

The tally of the accounts to be deleted is 3,398, most of which are long-defunct spammer registrations.

This will likely happen sometime on 2021-04-04.

[Uncle Al]

Uncle Al

[denimini]

Thanks Carl, I think the addition of criteria number 3 nails it. I would not like to see some of our mortally departed go ........... all those morally departed can go.

[crfriend]

Thanks Carl, I think the addition of criteria number 3 nails it. I would not like to see some of our mortally departed go ........... all those morally departed can go.

I've done this a few times in the past, mainly to clear the deadwood out, and it's overdue. A lot of thought went into the process and determining precisely what the criteria for deletion were so as not to, as you put it, accidentally nuke the "dearly departed".

'm working on a generic version of the SQL that can be executed over periods of time without needing me to modify it. I'll post it when I'm done so you guys can see the guts.

Code: Select all

CREATE TEMPORARY TABLE foo
SELECT user_id
FROM phpbb3_users
WHERE user_posts=0
 AND username NOT like '%[%'
 AND user_regdate >= UNIX_TIMESTAMP(date_sub(now(),INTERVAL 10 YEAR))
 AND user_lastvisit < UNIX_TIMESTAMP(date_sub(now(),INTERVAL 1 YEAR))
 AND user_id > 50 ORDER BY user_regdate ASC;

DELETE FROM phpbb3_users
WHERE user_id IN (SELECT user_id FROM foo);

DROP TABLE foo;

[crfriend]

... And the deed is done. The final count was 4,820 dead, deactivated, or deeply unused accounts.

[Coder]

Just curious why you create the temporary table and not just delete directly? Does it help you do a quick spot check of the accounts being deleted?

[crfriend]

Just curious why you create the temporary table and not just delete directly? Does it help you do a quick spot check of the accounts being deleted?

I certainly could have, but developing the query was work enough and I had the data-sample of the rows I wanted to delete so just did it that way. Anyway, the underlying database does not support deletions from a table using a subquery on that table, so I circumvented that with the temporary table. Had this been a PostgreSQL database it would have been the subquery methodology all the way.

I've got chops in a few RDBMSes but tend not to bray about some of them lest I get roped into things I don't need to. I've already got more on my table than I can likely complete before I hit my "best used by" date.

Interestingly, this reply caused a 500-series error in the server by the mention of the database in use (the predecessor of mariadb) so I need to figure out what tripped that -- or maybe finish the upgrade that's been needed to be done for months and months... ... And I can't reproduce it in my lab here...