MySQL query error [1105]: Maximum writeset size exceeded

[Xon]

Affected version

2.2.18

Unbound deletes can run into the issue that MariaDB Galera Cluster have an effective maximum delete/write size. This is very relevant for the xf_search table which has very wide rows and modern crawlers hammer the site in unexpected ways.

XF\Db\Exception: MySQL query error [1105]: Maximum writeset size exceeded src/XF/Db/AbstractStatement.php:230
Generated by: Unknown account Mar 23, 2026 at 11:00 AM

DELETE  FROM `xf_search` WHERE search_date < ?
------------

#0 src/XF/Db/Mysqli/Statement.php(198): XF\Db\AbstractStatement->getException('MySQL query err...', 1105, 'HY000')
#1 src/XF/Db/Mysqli/Statement.php(78): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1105, 'HY000')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(290): XF\Db\AbstractAdapter->query('DELETE  FROM `x...', 1774148448)
#4 src/XF/Repository/Search.php(84): XF\Db\AbstractAdapter->delete('xf_search', 'search_date < ?', 1774148448)
#5 src/XF/Cron/CleanUp.php(41): XF\Repository\Search->pruneSearches()
#6 src/XF/Job/Cron.php(37): XF\Cron\CleanUp::runDailyCleanUp(Object(XF\Entity\CronEntry))
#7 src/XF/Job/Manager.php(260): XF\Job\Cron->run(2)
#8 src/addons/SV/CachePermissionChecks/XF/Job/Manager.php(24): XF\Job\Manager->runJobInternal(Array, 2)
#9 src/XF/Job/Manager.php(202): SV\CachePermissionChecks\XF\Job\Manager->runJobInternal(Array, 2)
#10 src/XF/Job/Manager.php(86): XF\Job\Manager->runJobEntry(Array, 2)
#11 job.php(43): XF\Job\Manager->runQueue(false, 2)
#12 {main}

This error was caused on spacebattles.com when trying to delete something 422k rows. But the issue will also occur for the latest XF version.

 

[Xon]

It took about +40 seconds to manually prune the table in batches of 50k rows. Looking at the on-disk copy of the table shows why:

$ du -hs --apparent-size /var/lib/mysql/sbforums/xf_search.ibd
14G     /var/lib/mysql/sbforums/xf_search.ibd