craftcms
[4.x]: MySQL locking up when searching with DbCache and a large number of elements
What happened?
Description
Using Craft's Search functionality with DB caching and a large number of searched elements results in an Unable to update or insert cache data: SQLSTATE[08S01]: Communication link failure: 1153 Got a packet bigger than 'max_allowed_packet' bytes error, seemingly because the resulting data object is too large to fit in a single query.
The searchElements method in the Search service alters the query to have no search term and no limit, and subsequently attempts to cache those results. Any search request to a large set of elements means thousands of results get written to the cache in one go, which triggers the database error when using DbCache.
We're experiencing this in User element index searches, and Commerce's customer search, where we have approximately 50k users. This error seems to lock up our database for a few seconds, and is therefore causing other database requests to fail when a User search is performed in the CP.
Steps to reproduce
- Use craft\cache\DbCache as system cache component
- Use search() on an element query where thousands of elements exist
Craft CMS version
4.4.13
PHP version
8.1
Operating system and version
No response
Database type and version
MariaDB 10.5
Image driver and version
No response
Installed plugins and versions
No response
