XF 2.2 The table ‘xf_session_activity’ is full

[bburton]

What might cause the xf_session_activity table to become repeatedly full resulting in the contents of forum pages not loading and an error message displaying instead?

The 'hourly clean up' cron job is running, so that's not the reason. This is occurring between those hourly clean ups - about every 20 minutes or so. Running the 'hourly clean up' manually temporarily 'fixes' the problem, then 20 minutes or so later the xf_session_activity table fills up again and this error occurs repeatedly:

XF\Db\Exception: MySQL query error [1114]: The table 'xf_session_activity' is full
src/XF/Db/AbstractStatement.php:230

There is not an unusual number of logged in users. Nothing appears unusual with the number of logged in registered users.

Thanks,

 

[ebikes.com]

Yes i figured the false positives i see might sometimes be due to a bot activity trigger, which has very little information to go off, so the accuracy can suck.

Bots will come back. Few of them ever stop.

Consider the route of piping your web server logs to fail2an and creating some rate limits and areas for which to trap crawlers who are particularly abusive.

I'm not sure how good crowdsec is, but it might be a good addition that run on the server, since it's aware of the network of bots out there. I have yet to need it.

Your server has more information available to it than cloudflare can get. So the protection you equip it with can be a lot smarter.

 

[Jja]

Why bother with fail2ban and the traffic which still reaches your server?
Using Cloudflare you can configure many settings to reduce unwanted spam traffic. I have monitored some of the bot traffic and have placed Amazon's AWS, Microsoft's Azure, Alibaba, Huawei ASNs behind a challenge in the WAF.
Also bots who scrape wordpress related stuff - you can play here with URI request as well in the CF.

 

[ebikes.com]

It gets blocked at the OS level firewall, so it has basically no impact other than a miniscule impact on bandwidth.
You could refer bans up to the ISP if needed.

I can grab a range of IP addresses from a provider like AWS and just ban them at my firewall, they have no business accessing the site, don't need cloudflare to do that.

cloudflare is free because they sell data on all your web traffic. I don't want to give that away for things i could setup locally that don't require me to do that. I also don't like making them an external dependency of our site ( they're more likely to go down than we are )

 

[Jja]

I am perfectly aware of how fail2ban works. I don't see how you manage it successfuly when you have thousands of bot sessions.

Your logic - Cloudflare is bad but let's allow bots to scrape.

 

[ebikes.com]

I have tens of thousands of bot sessions and it's allright if you're familiar with linux and apache/nginx configuration to use fail2ban

Your logic - Cloudflare is bad but let's allow bots to scrape.

Wondering where you got that, since i'm talking about techniques that are i successfully use for defeating scrapers...?
You can actually get better results with fail2ban since your webserver and it's logs contain much more useful information than what cloudflare gets.

 

[Bozza]

Found this thread having been told some of my users are having issues today, prompting "is the forum down?" questions.

Checked the server logs in my admin panels and I have a lot of:

XF\Db\Exception: MySQL query error [1114]: The table 'xf_session_activity' is full

Scrolled down on my forum's homepage to see:

Total: 40,087 (members: 486, guests: 39,601)

I've read this thread but feel at a bit of a loss as to next steps.

I've used Cloudflare in the past a few times and although I can't remember specifics now, I always seem to hit some kind of issue and end up reverting to having the site without it.

I've also quickly looked at fail2ban, but it looks a bit beyond my non-technical non-command line skillset.

I'll keep reading and researching, but I'll appreciate any pointers!

 

[ebikes.com]

Hey.. see my partial solution here:

There's a sessions table that xenforo uses that is in memory and gets too big.
We use the below lines in the mysql configuration to increase the size of these tables and it's been successful so far.

View attachment 323843

We have a LOT of bots but we ban the ones that are on bad behavior with lots of fail2ban rules and let the rest suck the data up as long as they follow the rules stated in robots.txt.

This cuts out a ton of bots, because most are AI scrapers. But as you can see, we still have a crapton.

View attachment 323844

As for this giant botnet that you can't easily detect..
It's very likely that this is a giant block of AWS IP addresses pretending to be legitimate users by their agent identifier.
I found a cluster of 87 AWS addresses from the southeast region is responsible for >90% of my bot traffic.

This could be blocked with:

• some cloudflare rules/settings that block all known large hosting providers
• feed AWS' IP address range list into a .htaccess or IPtables to perma ban them

BTW i've seen this set of bots attached to many websites i operate. This is likely an AI company scraping the entire internet.