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.

 

[vicos]

So, cron task cleanUpHourly is responsible for cleaning up table xf_session_activity?

Is there any harm in having this run every 20 minutes instead of once per hour?

 

[emaw]

So, cron task cleanUpHourly is responsible for cleaning up table xf_session_activity?

Is there any harm in having this run every 20 minutes instead of once per hour?

can that be changed in the admin cp?

 

[nevershutup]

Hi. I'm having serious issues with this on my forum too. Bots are filling up the xf_session_activity table. There's nearly 5k bots active at any one time. I've tried to block them using Cloudflare, but I'm just an amateur. I don't know what rules to set up or other methods to prevent them. I'm just using the free tier at the moment, if anyone can help out, would appreciate it.

 

[TUG]

same thing happening here, cleared the activity table and restarted mariadb and forum was normal.... till 15 thousand more connections happened and now it appears the search table has crashed? site generates this error for any attempt to post/edit

• Table './tugbbsc_xenforo/xf_search_index' is marked as crashed and last (automatic?) repair failed

 

[Bozza]

Hi. I'm having serious issues with this on my forum too. Bots are filling up the xf_session_activity table. There's nearly 5k bots active at any one time. I've tried to block them using Cloudflare, but I'm just an amateur. I don't know what rules to set up or other methods to prevent them. I'm just using the free tier at the moment, if anyone can help out, would appreciate it.

The problem, for me at least, is they don't look like regular bots, and Xenforo classifies them as guests - they simply can't be living human users though.

Are you seeing the same?

On my forum I had in excess of 40,000 at one stage.

Even now, as I type, I have this:

Total: 6,336 (members: 415, guests: 5,921)
Robots: 192

But as I have a pretty high-specced dedicated server it's not making too much difference, and Cloudflare seems to be keeping a lot away. This is what I did, also on the free tier:

Go to Analytics & Logs > HTTP Traffic

Scroll down and look at the countries of the visitors. My forum is about an English Premier League football side and, as a matter of course, 90% of my traffic is from the UK. My UK traffic was being swamped by Brazil, China, Hong Kong, Indonesia, Singapore and Vietnam.

So I went to Security > WAF > Custom Rules and added a rule that blocks if the country equals any of those. I threw in Russia for good measure.

That seemed to sort me out, but I'm now going to take a closer look at where the current volume of guests are coming from, and look to block more countries if it looks like it would help.

 

[TUG]

same here, im seeing anywhere from 10 to 20 thousand, however the server itself is not anywhere near its limits in disk/ram/network/etc...

my issues have all been related to these two tables with now the search table being corrupted...not quite sure how to fix that.

 

[Bozza]

same thing happening here, cleared the activity table and restarted mariadb and forum was normal.... till 15 thousand more connections happened and now it appears the search table has crashed? site generates this error for any attempt to post/edit

• Table './tugbbsc_xenforo/xf_search_index' is marked as crashed and last (automatic?) repair failed

You need to login to phpmyadmin and run the repair command on that table.

If the table is very large, truncate it and then rebuild the search index.

 

[nevershutup]

The problem, for me at least, is they don't look like regular bots, and Xenforo classifies them as guests - they simply can't be living human users though.

Are you seeing the same?

On my forum I had in excess of 40,000 at one stage.

Even now, as I type, I have this:

Total: 6,336 (members: 415, guests: 5,921)
Robots: 192

But as I have a pretty high-specced dedicated server it's not making too much difference, and Cloudflare seems to be keeping a lot away. This is what I did, also on the free tier:

Go to Analytics & Logs > HTTP Traffic

Scroll down and look at the countries of the visitors. My forum is about an English Premier League football side and, as a matter of course, 90% of my traffic is from the UK. My UK traffic was being swamped by Brazil, China, Hong Kong, Indonesia and Singapore and Indonesia.

So I went to Security > WAF > Custom Rules and added a rule that blocks if the country equals any of those. I threw in Russia for good measure.

That seemed to sort me out, but I'm now going to take a closer look at where the current volume of guests are coming from, and look to block more countries if it looks like it would help.

This is my cloudflare stats page. I don't know what the normal numbers should look like as I only just started using it. I'd guess that most here are okay. Maybe USA should be this high because it's a big country? But Brazil being the 2nd highest is absolutely anomalous. I put a Cloudflare rule to challenge connections from that entire country with a JS captcha.

same here, im seeing anywhere from 10 to 20 thousand, however the server itself is not anywhere near its limits in disk/ram/network/etc...

my issues have all been related to these two tables with now the search table being corrupted...not quite sure how to fix that.

I actually made my problem way worse by accidently. The first time it happened and I looked into it, I tried setting my php memory limit higher. It was at -1... -1 is unlimited, but I'd forgotten that, so I set it to 128M. That filled up within twenty minutes. Leaving it at -1 and running a cron entry to clear the session data every few hours seems to massively alleviate the problem for me. At bare minimum it's been an hour or three now and the 5k or so bots on the site haven't filled the session database yet.

Also I just refreshed and we're back down to about 160 guests. Maybe CF finally kicked in properly, or maybe the attack just moved on. Either way, will update if anything changes.

Kind of 'happy' in a way others are experiencing this. I'm a complete noob at any of this stuff, I just follow guides and google stuff. If other more experienced people are having issues too, maybe they can fix it better than I can.

 

[TUG]

did that, it ran for about a minute then gave me a server timeout error.

I did NOT choose to delete the table before rebuilding, and yes its very.

 

[TUG]

got the same error clicking to delete the table before recreating it.

now clicking on any page on the forum generates this error:

XF\Db\Exception: MySQL fetch error [2014]: Commands out of sync; you can't run this command now in src/XF/Db/AbstractStatement.php at line 230

 

[TUG]

You need to login to phpmyadmin and run the repair command on that table.

If the table is very large, truncate it and then rebuild the search index.

repair command failed via phpmyadmin

 

[TUG]

had to restart mariadb in recovery mode just to access it, was able to truncate the search table..and now i am able to run the repair via the admin console (and see it actively rebuilding)

 

[TUG]

sadly after hours of fixing this earlier today, the situation has repeated itself starting at 4am with 20,000 connected users and the site is no longer displaying any pages vs a generic "an error occured while the page was being generated, please try again later" with the server log just showing thousands of entries of:

• The table 'xf_session_activity' is full