MySQL problems related to modess_access permission

[Brettflan]

We've been randomly getting "MySQL server has gone away" errors in the server log every once in a while, with the only common thread for all of them being a check for the "modess_access" permission. The URLs are all over the place, no common denominator there. One thing that stood out to me a bit was an attachment download being served (the 2nd spoiler below), which doesn't seem like there would be any reason for it to be checking that permission there. And I'm not sure the 4th entry below is even a valid URL for our forums.

Our error log entries for the last several of them are below.

Spoiler

    ErrorException: [E_WARNING] mysqli::query(): MySQL server has gone away src/XF/Db/Mysqli/Adapter.php:184 
    Generated by: Unknown account Feb 2, 2022 at 2:52 PM 

Stack trace
#0 [internal function]: XF::handlePhpError(2, '[E_WARNING] mys...', '/<website-path>...', 184, Array)
#1 src/XF/Db/Mysqli/Adapter.php(184): mysqli->query('SET @@session.s...')
#2 src/XF/Db/Mysqli/Adapter.php(28): XF\Db\Mysqli\Adapter->makeConnection(Array)
#3 src/XF/Db/AbstractAdapter.php(62): XF\Db\Mysqli\Adapter->getConnection()
#4 src/XF/Db/AbstractAdapter.php(85): XF\Db\AbstractAdapter->connect()
#5 src/XF/Db/AbstractAdapter.php(152): XF\Db\AbstractAdapter->query('
            SELECT cach...', 1)
#6 src/XF/PermissionCache.php(34): XF\Db\AbstractAdapter->fetchOne('
            SELECT cach...', 1)
#7 src/XF/PermissionSet.php(35): XF\PermissionCache->getGlobalPerms(1)
#8 src/XF/Entity/User.php(1197): XF\PermissionSet->hasGlobalPermission('general', 'modess_access')
#9 src/addons/SV/ModeratorEssentials/XF/Entity/User.php(34): XF\Entity\User->hasPermission('general', 'modess_access')
#10 src/addons/SV/ModeratorEssentials/XF/Entity/User.php(62): SV\ModeratorEssentials\XF\Entity\User->canViewModeratorPanel()
#11 src/addons/SV/ModeratorEssentials/Listener.php(48): SV\ModeratorEssentials\XF\Entity\User->canViewModeratorLogCounts()
#12 src/XF/Extension.php(69): SV\ModeratorEssentials\Listener::appPubStartEnd(Object(XF\Pub\App))
#13 src/XF/App.php(2853): XF\Extension->fire('app_pub_start_e...', Array, NULL)
#14 src/XF/Pub/App.php(201): XF\App->fire('app_pub_start_e...', Array)
#15 src/XF/App.php(2347): XF\Pub\App->start(true)
#16 src/XF.php(517): XF\App->run()
#17 index.php(20): XF::runApp('XF\\Pub\\App')
#18 {main}

Request state
array(4) {
  ["url"] => string(26) "/index.php?threads/143849/"
  ["referrer"] => bool(false)
  ["_GET"] => array(1) {
    ["threads/143849/"] => string(0) ""
  }
  ["_POST"] => array(0) {
  }
}

Spoiler

    ErrorException: [E_WARNING] mysqli::query(): MySQL server has gone away src/XF/Db/Mysqli/Adapter.php:184 
    Generated by: Unknown account Feb 9, 2022 at 11:19 PM 

Stack trace
#0 [internal function]: XF::handlePhpError(2, '[E_WARNING] mys...', '/<website-path>...', 184, Array)
#1 src/XF/Db/Mysqli/Adapter.php(184): mysqli->query('SET @@session.s...')
#2 src/XF/Db/Mysqli/Adapter.php(28): XF\Db\Mysqli\Adapter->makeConnection(Array)
#3 src/XF/Db/AbstractAdapter.php(62): XF\Db\Mysqli\Adapter->getConnection()
#4 src/XF/Db/AbstractAdapter.php(85): XF\Db\AbstractAdapter->connect()
#5 src/XF/Db/AbstractAdapter.php(152): XF\Db\AbstractAdapter->query('
            SELECT cach...', 1)
#6 src/XF/PermissionCache.php(34): XF\Db\AbstractAdapter->fetchOne('
            SELECT cach...', 1)
#7 src/XF/PermissionSet.php(35): XF\PermissionCache->getGlobalPerms(1)
#8 src/XF/Entity/User.php(1197): XF\PermissionSet->hasGlobalPermission('general', 'modess_access')
#9 src/addons/SV/ModeratorEssentials/XF/Entity/User.php(34): XF\Entity\User->hasPermission('general', 'modess_access')
#10 src/addons/SV/ModeratorEssentials/XF/Entity/User.php(62): SV\ModeratorEssentials\XF\Entity\User->canViewModeratorPanel()
#11 src/addons/SV/ModeratorEssentials/Listener.php(48): SV\ModeratorEssentials\XF\Entity\User->canViewModeratorLogCounts()
#12 src/XF/Extension.php(69): SV\ModeratorEssentials\Listener::appPubStartEnd(Object(XF\Pub\App))
#13 src/XF/App.php(2853): XF\Extension->fire('app_pub_start_e...', Array, NULL)
#14 src/XF/Pub/App.php(201): XF\App->fire('app_pub_start_e...', Array)
#15 src/XF/App.php(2347): XF\Pub\App->start(true)
#16 src/XF.php(517): XF\App->run()
#17 index.php(20): XF::runApp('XF\\Pub\\App')
#18 {main}

Request state
array(4) {
  ["url"] => string(65) "/index.php?attachments/photo-1558976233-1e4e7e25eac5-jpeg.162037/"
  ["referrer"] => string(47) "https://forums.taleworlds.com/index.php?groups/"
  ["_GET"] => array(1) {
    ["attachments/photo-1558976233-1e4e7e25eac5-jpeg_162037/"] => string(0) ""
  }
  ["_POST"] => array(0) {
  }
}

Spoiler

    ErrorException: [E_WARNING] mysqli::query(): MySQL server has gone away src/XF/Db/Mysqli/Adapter.php:184 
    Generated by: Unknown account Feb 19, 2022 at 1:13 PM 

Stack trace
#0 [internal function]: XF::handlePhpError(2, '[E_WARNING] mys...', '/<website-path>...', 184, Array)
#1 src/XF/Db/Mysqli/Adapter.php(184): mysqli->query('SET @@session.s...')
#2 src/XF/Db/Mysqli/Adapter.php(28): XF\Db\Mysqli\Adapter->makeConnection(Array)
#3 src/XF/Db/AbstractAdapter.php(62): XF\Db\Mysqli\Adapter->getConnection()
#4 src/XF/Db/AbstractAdapter.php(85): XF\Db\AbstractAdapter->connect()
#5 src/XF/Db/AbstractAdapter.php(152): XF\Db\AbstractAdapter->query('
            SELECT cach...', 1)
#6 src/XF/PermissionCache.php(34): XF\Db\AbstractAdapter->fetchOne('
            SELECT cach...', 1)
#7 src/XF/PermissionSet.php(35): XF\PermissionCache->getGlobalPerms(1)
#8 src/XF/Entity/User.php(1197): XF\PermissionSet->hasGlobalPermission('general', 'modess_access')
#9 src/addons/SV/ModeratorEssentials/XF/Entity/User.php(34): XF\Entity\User->hasPermission('general', 'modess_access')
#10 src/addons/SV/ModeratorEssentials/XF/Entity/User.php(62): SV\ModeratorEssentials\XF\Entity\User->canViewModeratorPanel()
#11 src/addons/SV/ModeratorEssentials/Listener.php(48): SV\ModeratorEssentials\XF\Entity\User->canViewModeratorLogCounts()
#12 src/XF/Extension.php(69): SV\ModeratorEssentials\Listener::appPubStartEnd(Object(XF\Pub\App))
#13 src/XF/App.php(2853): XF\Extension->fire('app_pub_start_e...', Array, NULL)
#14 src/XF/Pub/App.php(201): XF\App->fire('app_pub_start_e...', Array)
#15 src/XF/App.php(2347): XF\Pub\App->start(true)
#16 src/XF.php(517): XF\App->run()
#17 index.php(20): XF::runApp('XF\\Pub\\App')
#18 {main}

Request state
array(4) {
  ["url"] => string(48) "/index.php?threads/intrigues.438953/post-9637261"
  ["referrer"] => bool(false)
  ["_GET"] => array(1) {
    ["threads/intrigues_438953/post-9637261"] => string(0) ""
  }
  ["_POST"] => array(0) {
  }
}

Spoiler

    ErrorException: [E_WARNING] mysqli::query(): MySQL server has gone away src/XF/Db/Mysqli/Adapter.php:184 
    Generated by: Unknown account Mar 4, 2022 at 2:18 AM 

Stack trace
#0 [internal function]: XF::handlePhpError(2, '[E_WARNING] mys...', '/<website-path>...', 184, Array)
#1 src/XF/Db/Mysqli/Adapter.php(184): mysqli->query('SET @@session.s...')
#2 src/XF/Db/Mysqli/Adapter.php(28): XF\Db\Mysqli\Adapter->makeConnection(Array)
#3 src/XF/Db/AbstractAdapter.php(62): XF\Db\Mysqli\Adapter->getConnection()
#4 src/XF/Db/AbstractAdapter.php(85): XF\Db\AbstractAdapter->connect()
#5 src/XF/Db/AbstractAdapter.php(152): XF\Db\AbstractAdapter->query('
            SELECT cach...', 1)
#6 src/XF/PermissionCache.php(34): XF\Db\AbstractAdapter->fetchOne('
            SELECT cach...', 1)
#7 src/XF/PermissionSet.php(35): XF\PermissionCache->getGlobalPerms(1)
#8 src/XF/Entity/User.php(1197): XF\PermissionSet->hasGlobalPermission('general', 'modess_access')
#9 src/addons/SV/ModeratorEssentials/XF/Entity/User.php(34): XF\Entity\User->hasPermission('general', 'modess_access')
#10 src/addons/SV/ModeratorEssentials/XF/Entity/User.php(62): SV\ModeratorEssentials\XF\Entity\User->canViewModeratorPanel()
#11 src/addons/SV/ModeratorEssentials/Listener.php(48): SV\ModeratorEssentials\XF\Entity\User->canViewModeratorLogCounts()
#12 src/XF/Extension.php(69): SV\ModeratorEssentials\Listener::appPubStartEnd(Object(XF\Pub\App))
#13 src/XF/App.php(2853): XF\Extension->fire('app_pub_start_e...', Array, NULL)
#14 src/XF/Pub/App.php(201): XF\App->fire('app_pub_start_e...', Array)
#15 src/XF/App.php(2347): XF\Pub\App->start(true)
#16 src/XF.php(517): XF\App->run()
#17 index.php(20): XF::runApp('XF\\Pub\\App')
#18 {main}

Request state
array(4) {
  ["url"] => string(24) "/index.php?sw/cache.json"
  ["referrer"] => bool(false)
  ["_GET"] => array(1) {
    ["sw/cache_json"] => string(0) ""
  }
  ["_POST"] => array(0) {
  }
}

Spoiler

    ErrorException: [E_WARNING] mysqli::query(): MySQL server has gone away src/XF/Db/Mysqli/Adapter.php:184 
    Generated by: Unknown account Mar 7, 2022 at 4:02 AM 

Stack trace
#0 [internal function]: XF::handlePhpError(2, '[E_WARNING] mys...', '/<website-path>...', 184, Array)
#1 src/XF/Db/Mysqli/Adapter.php(184): mysqli->query('SET @@session.s...')
#2 src/XF/Db/Mysqli/Adapter.php(28): XF\Db\Mysqli\Adapter->makeConnection(Array)
#3 src/XF/Db/AbstractAdapter.php(62): XF\Db\Mysqli\Adapter->getConnection()
#4 src/XF/Db/AbstractAdapter.php(85): XF\Db\AbstractAdapter->connect()
#5 src/XF/Db/AbstractAdapter.php(152): XF\Db\AbstractAdapter->query('
            SELECT cach...', 1)
#6 src/XF/PermissionCache.php(34): XF\Db\AbstractAdapter->fetchOne('
            SELECT cach...', 1)
#7 src/XF/PermissionSet.php(35): XF\PermissionCache->getGlobalPerms(1)
#8 src/XF/Entity/User.php(1197): XF\PermissionSet->hasGlobalPermission('general', 'modess_access')
#9 src/addons/SV/ModeratorEssentials/XF/Entity/User.php(34): XF\Entity\User->hasPermission('general', 'modess_access')
#10 src/addons/SV/ModeratorEssentials/XF/Entity/User.php(62): SV\ModeratorEssentials\XF\Entity\User->canViewModeratorPanel()
#11 src/addons/SV/ModeratorEssentials/Listener.php(48): SV\ModeratorEssentials\XF\Entity\User->canViewModeratorLogCounts()
#12 src/XF/Extension.php(69): SV\ModeratorEssentials\Listener::appPubStartEnd(Object(XF\Pub\App))
#13 src/XF/App.php(2853): XF\Extension->fire('app_pub_start_e...', Array, NULL)
#14 src/XF/Pub/App.php(201): XF\App->fire('app_pub_start_e...', Array)
#15 src/XF/App.php(2347): XF\Pub\App->start(true)
#16 src/XF.php(517): XF\App->run()
#17 index.php(20): XF::runApp('XF\\Pub\\App')
#18 {main}

Request state
array(4) {
  ["url"] => string(124) "/index.php?threads/%C4%B0ntel%E2%80%99in-11-nesil-i%C5%9Flemcisini-neden-%C3%B6nermiyorlar-ve-pc-%C3%B6zellik-yorumu.448450/"
  ["referrer"] => bool(false)
  ["_GET"] => array(1) {
    ["threads/İntel'in-11-nesil-işlemcisini-neden-önermiyorlar-ve-pc-özellik-yorumu_448450/"] => string(0) ""
  }
  ["_POST"] => array(0) {
  }
}

Is it possible this permission check could be streamlined or cached or something? Or, do you have any suggestions for other things I could look into for this?

 

[Xon]

I did spot a bug where this additional permission check shouldn't have been done.

Bu these permission checks should be cached, as the current user's permission should be fetched at the start of the session.
Do you have a caching provider setup? Are you using full-page caching?

I've got an add-on which is vastly more aggressive about caching these sorts of permission checks into a caching provider than using MySQL;

Cache Permission Checks

Cache permissions blobs in redis

atelieraphelion.com

 

[Brettflan]

Thanks for looking into it and putting out a quick improvement release.

We're currently using Redis caching, with your Redis Cache addon. It is configured and working. A while back we were using APC (the usercache offshoot now available since the code compilation part is now built into PHP), but we switched to Redis for some of the additional integration your User Activity addon has with it for caching that info.

I wasn't sure specifically what you meant by full page caching in the context of XenForo; I looked around and did notice XenForo has guest page caching options, which need extra configuration to work, and which we're not yet using but certainly should be. I'm guessing that's what you're talking about. I'll definitely be looking to get that set up now that I know about it.

I'll check into that other addon you linked and may end up using it as well.

 

[Xon]

I recommend checking your MySQL server logs, it may give you a hint on what is happening.

My random guess is the maximum number of connections maybe too low, this would be noticable if you are using pre-forking apache.