XF 2.1 MySQL error when running Generate random album cache cron

[Joe Link]

We're getting this MySQL error in the AdminCP when the random album cache cron runs.

XF\Db\Exception: MySQL query error [1406]: Data too long for column 'data_value' at row 1 in src/XF/Db/AbstractStatement.php at line 217

    XF\Db\AbstractStatement->getException() in src/XF/Db/Mysqli/Statement.php at line 196
    XF\Db\Mysqli\Statement->getException() in src/XF/Db/Mysqli/Statement.php at line 77
    XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
    XF\Db\AbstractAdapter->query() in src/XF/DataRegistry.php at line 177
    XF\DataRegistry->set() in src/XF/SimpleCache.php at line 57
    XF\SimpleCache->save() in src/XF/SimpleCache.php at line 40
    XF\SimpleCache->setValue() in src/XF/SimpleCacheSet.php at line 41
    XF\SimpleCacheSet->setValue() in src/XF/SimpleCacheSet.php at line 68
    XF\SimpleCacheSet->__set() in src/addons/XFMG/Cron/RandomCache.php at line 20
    XFMG\Cron\RandomCache::generateRandomAlbumCache()
    call_user_func() in src/XF/Admin/Controller/CronEntry.php at line 112
    XF\Admin\Controller\CronEntry->actionRun() in src/XF/Mvc/Dispatcher.php at line 321
    XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 244
    XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 100
    XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 50
    XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2178
    XF\App->run() in src/XF.php at line 390
    XF::runApp() in admin.php at line 13

 

[Chris D]

That's kind of interesting.

What's happening here specifically is that the "Simple Cache", which is a general purpose cache for add-ons to store data, is full. It is stored in the xf_data_registry table and the contents are in the data_value field which is a MEDIUMBLOB which can store up to 16MB of data.

This error is saying that it has exceeded 16MB which is actually huge. That's 16 million characters, essentially.

The random album cache, and random media cache, is actually just a cache of media and album IDs. I think we store a maximum of 500 of each. Given that, I think the maximum amount of storage this would take up in any worse case scenario is about maybe 0.01MB which is 100KB of data.

So, all of that to say, although it is falling over when XFMG is trying to add to the cache, it's only falling over because ~15.99MB of data already exists in the cache and XFMG is the straw that breaks the camel's back here.

It is rather unfathomable to me that such a large amount of data needs to be stored in this cache, so we need to try and ascertain which add-on has added such a huge amount of data to it.

You will need to take steps to try and download the binary data which is stored in the data_value field of the xf_data_registry table for the record which has a data_key of simpleCache.

I think in phpMyAdmin if you find that record and that field it lets you click on it to download the data as a bin file. If you can get that data downloaded, upload it somewhere secure (like Google Drive or similar) and then submit a ticket from your customer area with a link to where that file can be downloaded.

We probably can't fix the problem directly, but we might be able to indicate which add-on has added a lot of data to it. Then you can report it to that developer who may need to make changes to ensure they're not storing as much data.

 

[Joe Link]

Ticket submitted

 

[Chris D]

Thanks.

I've performed an analysis of the simple cache data and it became clear very quickly which add-on may be storing a significant amount of data:



So the culprit appears to be this add-on:

Widget Threads Enhancement

The Widget Threads Enhancement for XenForo 1. New thread types This add-on introduces several new thread types, including: Recent threads Most viewed Most reacted More types... These new thread types provide a more diverse range of content for...

xenforo.com

I'm not exactly sure what it is storing and why it is storing so many, but another approach will need to be found. "Simple Cache" shouldn't be used for storing this amount of data. This data is queried for on every page and held in memory so it's potentially bad for performance too.

If you have caching enabled in these thread widgets, it may be wise to switch it off (hopefully that clears the already cached data?)

 

[Joe Link]

Thanks.

I've performed an analysis of the simple cache data and it became clear very quickly which add-on may be storing a significant amount of data:

View attachment 207618

So the culprit appears to be this add-on:

Widget Threads Enhancement

The Widget Threads Enhancement for XenForo 1. New thread types This add-on introduces several new thread types, including: Recent threads Most viewed Most reacted More types... These new thread types provide a more diverse range of content for...

xenforo.com

I'm not exactly sure what it is storing and why it is storing so many, but another approach will need to be found. "Simple Cache" shouldn't be used for storing this amount of data. This data is queried for on every page and held in memory so it's potentially bad for performance too.

If you have caching enabled in these thread widgets, it may be wise to switch it off (hopefully that clears the already cached data?)

I really appreciate you getting to the bottom of it so quickly @Chris D, thank you!