XF 2.1 Mass errors when batch deleting

Azaly

Active member
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:

Code:
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` = ?
Code:
#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
Code:
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:
Code:
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.
 

egerci

Member
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:

Code:
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` = ?
Code:
#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
Code:
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:
Code:
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 ?
 

nocte

Well-known member
Adding an index can fix this. I wrote a bug report:

 
Solution
Top