XF 2.1 Batch remove inactive users: Welp

[sudrien]

I thought I had nightly backups going. I didn't confirm I had nightly backups going properly.

So, too late for reverting.

I was trying to delete users with no posts that hadn't logged in for a year. Spammer cleanup. Batch update users seemed the right way to handle it.

It did that. Also, because a lack of reading comprehension, it also deleted users with 0 - -1 posts, aka "any number", which is the default, which hadn't logged in in a year. This seems like a good default, for people who can read (not me). I might suggest making it from 0 - 0.

This also deleted users that hadn't logged off in a year, and were still on the same PHP session, as far as I can tell. Should the logon date - or another searchable date field - be updated when somebody posts? I don't know.

The list was so long - 100 pages - that I didn't see specific names of active users on it that would have tipped me off.


Also, might as well report, got several instances of this dump:

• XF\Db\DeadlockException: Job XF:UserDeleteCleanUp: MySQL query error [1213]: Deadlock found when trying to get lock; try restarting transaction
• src/XF/Db/AbstractStatement.php:228

• Generated by: Unknown account
• Dec 24, 2019 at 12:59 PM

Stack trace

UPDATE (
SELECT content_id FROM xf_reaction_content
WHERE content_type = ?
AND reaction_user_id = ?
) AS temp
INNER JOIN xf_post AS reaction_table ON (reaction_table.post_id = temp.content_id)
SET reaction_table.reaction_users = REPLACE(reaction_table.reaction_users, ?, ?)

------------

#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL query err...', 1213, '40001')
#1 src/XF/Db/Mysqli/Statement.php(77): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1213, '40001')
#2 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Reaction/AbstractHandler.php(141): XF\Db\AbstractAdapter->query('
UPDATE (
...', Array)
#4 src/XF/Service/User/ContentChange.php(343): XF\Reaction\AbstractHandler->updateRecentCacheForUserChange(23546, 0, 'Almiradoj', 'Almiradoj')
#5 src/XF/MultiPartRunnerTrait.php(48): XF\Service\User\ContentChange->stepRebuildReactions(0, G)
#6 src/XF/Service/User/ContentChange.php(198): XF\Service\User\ContentChange->runLoop(G)
#7 src/XF/Service/User/DeleteCleanUp.php(255): XF\Service\User\ContentChange->apply(G)
#8 src/XF/MultiPartRunnerTrait.php(48): XF\Service\User\DeleteCleanUp->stepChangeOwner(NULL, G)
#9 src/XF/Service/User/DeleteCleanUp.php(93): XF\Service\User\DeleteCleanUp->runLoop(G)
#10 src/XF/Job/UserDeleteCleanUp.php(32): XF\Service\User\DeleteCleanUp->cleanUp(G)
#11 src/XF/Job/Manager.php(253): XF\Job\UserDeleteCleanUp->run(G)
#12 src/XF/Job/Manager.php(195): XF\Job\Manager->runJobInternal(Array, G)
#13 src/XF/Job/Manager.php(79): XF\Job\Manager->runJobEntry(Array, G)
#14 job.php(42): XF\Job\Manager->runQueue(false, 8)
#15 {main}

 

[sudrien]

In trying to rebuild the missing accounts, I've had some success. This post will be updated if I have any more.


Users create new accounts themselves, send me a link to one of the posts they want to claim. I confirm the IP matches.

update `xf_post`
set user_id = (SELECT user_id FROM `xf_user` where username = "new_name")
where username = "old_name";

update `xf_deletion_log`
set delete_user_id = (SELECT user_id FROM `xf_user` where username = "new_name")
where delete_username = "old_name";

update `xf_thread`
set user_id = (SELECT user_id FROM `xf_user` where username = "new_name")
where username = "old_name";

update `xf_forum`
set last_post_user_id = (SELECT user_id FROM `xf_user` where username = "new_name")
where last_post_username = "old_name";


update `xf_conversation_user`
set last_message_user_id = (SELECT user_id FROM `xf_user` where username = "new_name")
where last_message_username = "old_name";

update `xf_conversation_master`
set user_id = (SELECT user_id FROM `xf_user` where username = "new_name")
where username = "old_name";

update `xf_conversation_message`
set user_id =  (SELECT user_id FROM `xf_user` where username = "new_name")
where username = "old_name";

#xf_conversation_recipient based off xf_conversation_master.recipients? need to break out some json looping somehow ???


update `xf_user`
set message_count = (select count(*) from xf_post where username = "old_name")
where username = "new_name";



#rebuild search index to get Memeber/Postings list