Routine maintenance coming, 2021-04-04

Discuss recent changes, make suggestions, etc.
Post Reply
User avatar
crfriend
Master Barista
Posts: 14432
Joined: Fri Nov 19, 2004 9:52 pm
Location: New England (U.S.)
Contact:

Routine maintenance coming, 2021-04-04

Post by 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.
Retrocomputing -- It's not just a job, it's an adventure!
User avatar
Uncle Al
Moderator
Posts: 3861
Joined: Tue Oct 21, 2003 10:07 pm
Location: Duncanville, TX USA

Re: Routine maintenance coming, 2021-04-04

Post by Uncle Al »

:thumleft:

Uncle Al
:mrgreen: :ugeek: :mrgreen:
Kilted Organist/Musician
Grand Musician of the Grand Lodge, I.O.O.F. of Texas 2008-2009, 2015-2016,
2018-202 ? (and the beat goes on ;) )
When asked 'Why the Kilt?'
I respond-The why is F.T.H.O.I. (For The H--- Of It)
User avatar
denimini
Member Extraordinaire
Posts: 3224
Joined: Wed Jan 07, 2015 2:50 am
Location: Outback Australia

Re: Routine maintenance coming, 2021-04-04

Post by 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.
Anthony, a denim miniskirt wearer in Outback Australia
User avatar
crfriend
Master Barista
Posts: 14432
Joined: Fri Nov 19, 2004 9:52 pm
Location: New England (U.S.)
Contact:

Re: Routine maintenance coming, 2021-04-04

Post by crfriend »

denimini wrote: Sun Apr 04, 2021 7:14 amThanks 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;
Retrocomputing -- It's not just a job, it's an adventure!
User avatar
crfriend
Master Barista
Posts: 14432
Joined: Fri Nov 19, 2004 9:52 pm
Location: New England (U.S.)
Contact:

Re: Routine maintenance coming, 2021-04-04

Post by crfriend »

... And the deed is done. The final count was 4,820 dead, deactivated, or deeply unused accounts.
Retrocomputing -- It's not just a job, it's an adventure!
Coder
Member Extraordinaire
Posts: 2649
Joined: Mon Dec 16, 2019 4:40 am
Location: Southeast Michigan

Re: Routine maintenance coming, 2021-04-04

Post by 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?
User avatar
crfriend
Master Barista
Posts: 14432
Joined: Fri Nov 19, 2004 9:52 pm
Location: New England (U.S.)
Contact:

Re: Routine maintenance coming, 2021-04-04

Post by crfriend »

Coder wrote: Sun Apr 04, 2021 12:32 pmJust 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...
Retrocomputing -- It's not just a job, it's an adventure!
Post Reply