XF 2.1 Mass errors when batch deleting

[Azaly]

Not sure is it bug or just my local problem (should I open support ticket?). I did some cleanup and batch deleted old unused acccounts (≈200). Soon after that cpu usage is grown and I got ton of errors like this:

XF\Db\Exception: Job XF:UserDeleteCleanUp: MySQL query error [1205]: Lock wait timeout exceeded; try restarting transaction src/XF/Db/AbstractStatement.php:228
Generated by: Unknown account Nov 18, 2019 at 1:49 AM
Stack trace
                        UPDATE IGNORE `xf_reaction_content` SET
                            `reaction_user_id` = 0
                        WHERE `reaction_user_id` = ?

Spoiler: Some more details

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

Request state

array(4) {
  ["url"] => string(13) "/talk/job.php"
  ["referrer"] => string(52) "https://***/talk/conversations/35538/page-3"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(0) {
  }
}

Then some users got problem with reaction due to busy mysql:

XF\Db\Exception: MySQL query error [1205]: Lock wait timeout exceeded; try restarting transaction src/XF/Db/AbstractStatement.php:228
Stack trace
INSERT  INTO `xf_reaction_content` (`reaction_id`, `content_type`, `content_id`, `reaction_user_id`, `content_user_id`, `is_counted`, `reaction_content_id`, `reaction_date`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)

I didn't have something like this on xf 1.5 (and earlier) when batch delete was used. It was not quick, but without any errors. Heavy rebulding of reactions is annoyng.

 

[chris TFF]

We are getting a similar issue and server error logs - when batch deleting users awaiting moderation.

For some users it also sits in a loop "Updating Subscriptions".

 

[egerci]

Not sure is it bug or just my local problem (should I open support ticket?). I did some cleanup and batch deleted old unused acccounts (≈200). Soon after that cpu usage is grown and I got ton of errors like this:

XF\Db\Exception: Job XF:UserDeleteCleanUp: MySQL query error [1205]: Lock wait timeout exceeded; try restarting transaction src/XF/Db/AbstractStatement.php:228
Generated by: Unknown account Nov 18, 2019 at 1:49 AM
Stack trace
                        UPDATE IGNORE `xf_reaction_content` SET
                            `reaction_user_id` = 0
                        WHERE `reaction_user_id` = ?

Spoiler: Some more details

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

Request state

array(4) {
  ["url"] => string(13) "/talk/job.php"
  ["referrer"] => string(52) "https://***/talk/conversations/35538/page-3"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(0) {
  }
}

Then some users got problem with reaction due to busy mysql:

XF\Db\Exception: MySQL query error [1205]: Lock wait timeout exceeded; try restarting transaction src/XF/Db/AbstractStatement.php:228
Stack trace
INSERT  INTO `xf_reaction_content` (`reaction_id`, `content_type`, `content_id`, `reaction_user_id`, `content_user_id`, `is_counted`, `reaction_content_id`, `reaction_date`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)

I didn't have something like this on xf 1.5 (and earlier) when batch delete was used. It was not quick, but without any errors. Heavy rebulding of reactions is annoyng.

Hi,
Have you find a solution for this ?

 

[duderuud]

I recognize this. Batch deleting is a (too) heavy query for the DB to handle. Pretty annoying.

 

[egerci]

I recognize this. Batch deleting is a (too) heavy query for the DB to handle. Pretty annoying.

Not only deleting but also username change, user merging
Both of them have heavy query to handle.

 

[nocte]

Adding an index can fix this. I wrote a bug report:

Fixed - Add an index to `xf_reaction_content`.`reaction_user_id`

I had a similar issue as described here: https://xenforo.com/community/threads/mass-errors-when-batch-deleting.172453/ Running this query with a random unused user id took bout 40 seconds. This query runs with ever user deletion: UPDATE IGNORE `xf_reaction_content` SET `reaction_user_id` = 0...

xenforo.com