Purge of Inactive Users with Zero Posts and Other Anti-spam Measures

In order to keep the site "clean, safe and warm", I have recently purged all accounts at UNIX.COM which meet the ALL of the following 3 criteria:

• No user activity in 2018
• No posts (zero post count)
• Link in user profile.

In addition, I purged all users who registered but never confirmed their email address.

Also, instead of relying on flawed or inaccurate anti-spam databases or spam classifiers, I am manually inspecting new users in our database and we will actively delete users who are obviously registering on the site only to spam and/or post links to spammy sites.

The reason for this change is that anti-spam databases and anti-spam classifiers sometime block legitimate new users. We do not want any legitimate user with a UNIX/Linux problem (and an emergency need to be solve or helped) to be blocked. "AI' methods (Bayesian classifiers, ANN (artificial neural networks), etc ) are not really 100% reliable for blocking users and spam, and I don't want UNIX.COM to be dominated by "Bad AI" or "AI Gone Mad", so we are going to manage this directly in our database and will delete all new registered users who are obviously spammers.

UNIX.COM is fighting back against "bad AI' and so we will use humans, not machines, to manage user registration and spam until further notice.

Thank you as always for being a UNIX.COM team member.

Resist Bad AI (RBAI)

Also, just removed "homepage" urls from all users with zero posts who have not been active on the site since 1 March 2018:

mysql> update user set homepage=''  where homepage!='' and posts=0 and lastactivity < 1519862400;
Query OK, 68 rows affected (0.22 sec)
Rows matched: 68  Changed: 68  Warnings: 0

And on top of that, just deleted all "homepage" links from profile pages of member who have not been active on the site since 1 Sept 2017 (over a year ago) regardless of the number of posts they had:

mysql> update user set homepage=''  where homepage!=''  and lastactivity < 1504224000;
Query OK, 6337 rows affected (0.24 sec)
Rows matched: 6337  Changed: 6337  Warnings: 0

And just deleted any "homepage" links from all users with zero posts who have not been active since 1 September 2018:

mysql> update user set homepage=''  where homepage!='' and posts=0 and lastactivity < 1535760000;
Query OK, 153 rows affected (0.21 sec)
Rows matched: 153  Changed: 153  Warnings: 0

So, that should get rid of most of the prior "register and just add a link" spammers.

Haha.

Was going to run this query, but decided not to:

delete user where posts=0 and post_thanks_thanked_posts < 1 and post_thanks_thanked_times < 1 and lastactivity < 1420070400;

Delete all users who have no posts, never thanked anyone, never received any thanks, and have not been active since 1 Jan 2016.

But that's a big number (67652).

I'll stop and not delete these inactive, thankless, and none contributing 67652 users yet.

Maybe I'll refine the query by country or email address in the future.

Would it make sense to check usernames for patterns (like the recent "125" suffix) before updating / deleting?

If you want to write many pattern matching SQL queries like that, "please go for it" ...

Most the "recent spammers" have already been purged based on other criteria.

Feel free to suggest any MySQL query you like.

Normally these queries should be "in pairs". One MySQL "select" query to test; and then a delete or update query to "do the deed".

I cannot emphasis this enough, and keep (unsuccessfully) encouraging the technical team here at UNIX.COM (who are all very capable and highly technical) to communicate ideas in the actual code (MySQL query for DB suggestions, CSS/HTML/jQuery/Javascript for web site suggestions, etc.)

Thanks