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,

 

[Bozza]

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

Oh man - sounds so similar to mine.

Have you tried Cloudflare - if you follow what I did above, you should see which countries are flooding your site and put a hard block on them.

 

[TUG]

i guess thats the only option though ive confirmed that the server itself isnt even breaking a sweat even with the 20,000 connections so its not so much a hardware issue vs whatever limitations this particular table has that allows it to fill up and bring down the entire forum.

 

[TUG]

noticed it didnt even take 7000 "guests" to break it this evening at 4am. i suppose the regular chron job ran to clear out the table on its own as it started working about 10min ago. looking at the IPs a huge % of them all coming from 177. and 179. and other similar subnets showing from brazil.

out of curiosity, does any of the options within the admin cp trigger this job to run manually vs waiting for its regular schedule to truncate that table?

will say i opened a ticket with xf support earlier this afternoon when it started but have gotten no reply yet

 

[TUG]

looking at the log file. the errors started at 3:03am in the xenforo server log indicating the table was full.

they stopped on their own at 4:11am so im guessing the job runs once an hour perhaps and it just filled up nearly instantly after the 3am run time?

 

[TUG]

just a final update. xenforos (and my hosts) suggestion was to install cloudflare.

in the meantime, host was able to block all traffic from vietnam, brazil, ukraine and russia. and that alone has wiped out 90% of the bot traffic we were seeing (sofar at least).

they also modified the settings mentioned above to boost the table/heap size to 64mb

 

[TUG]

nearly 2 weeks later and have had zero issues with bots or similar, though overall "active users" is 20% down from normal...guess a regular % of my normal active users were these bots all the time...they just got out of control. (posts/threads/registrations/etc are all normal)

either way, what a pain in the ass...hope the info helps someone else should they encounter this in the future!

 

[Ricsca]

but if the table is emptied via PHPmyAdmin, what happens?

 

[HLR]

but if the table is emptied via PHPmyAdmin, what happens?

My Xenforo site is getting SLAMMED with bot traffic and it's crashing constantly with this same error.

I asked AI and it said it's fine to truncate the xf_session_activity table. It just keeps track of user sessions and how long people are tracked as "online".

As soon as I did that, my forum started working again.

In the admin page, I also changed: Setup->Options->User Options->Online status timeout to 5 minutes instead of 15. This seemed to help keep that table from filling up.

 

[HLR]

in the meantime, host was able to block all traffic from vietnam, brazil, ukraine and russia.

nearly 2 weeks later and have had zero issues with bots or similar, t

I took your info here and did the same thing. First off, I'm already on Cloudflare, but it was still letting lots of traffic through. The issue is my forum is on a subdomain, and I can't block those countries' traffic to a subdomain. (Maybe you can with CF page rules, etc, but I didn't want to mess with my production site on the primary domain)

So I solved this with Apache tools. The steps below assume you already have Cloudflare running as a proxy.

Step 1: Modify the Apache server config to save the country header field CF-IPCountry to a separate log file by adding these lines to your apache virtual host config:

LogFormat "%{CF-Connecting-IP}i %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\" CF-Country:%{CF-IPCountry}i" cloudflare_real_ip
CustomLog /home/xen/logs/access.log cloudflare_real_ip

This does two things -- it adds the originating IP address to your logs instead of the CF server IP address. Second, it adds a "CF-Country" field at the end of each log line.

Step 2: Restart Apache to enable these changes.

Step 3: Wait an hour or two. The run this bash command to summarize the most countries hitting your server:

 grep -o 'CF-Country:[A-Z][A-Z]' access.log | cut -d: -f2 | sort | uniq -c | sort -nr | head -20

Output will look like this:
1409 US
457 BD
370 IN
268 IQ
221 GB
140 AR
127 PK
122 SA
120 ZA
107 TR
97 EC
96 UZ
91 KE
84 NP
82 CA
75 JO
72 VE
72 ET
68 FR
67 MX

Step 5: Figure out which countries you want to allow or block. I blocked: VN|BR|UA|RU|IN|BD|IQ|PK|SR|ZA|VE|AR|SA|UZ|KE|MA|CO|TR

Step 6: Modify .htaccess thusly with the countries you want to block, like below:

    RewriteEngine On

    # Set environment variable for blocked requests
    RewriteCond %{HTTP:CF-IPCountry} ^(VN|BR|UA|RU|IN|BD|IQ|PK|SR|ZA|VE|AR|SA|UZ|KE|MA|CO|TR)$ [NC]
    RewriteRule ^(.*)$ - [F,L,E=blocked:%{HTTP:CF-IPCountry}]

    # Set environment variable for allowed requests
    RewriteCond %{HTTP:CF-IPCountry} !^(VN|BR|UA|RU|IN|BD|IQ|PK|SR|ZA|VE|AR|SA|UZ|KE|MA|CO|TR)$ [NC]
    RewriteRule ^(.*)$ - [E=allowed:1,L]

Right there that's enough to block all traffic from those countries!

Step 7: If you want to track allowed versus blocked requests, go back to your Apache config for the virtual host, and change/add these lines to log the blocked accesses:

# Use the custom format to log all traffic (blocked or not)
CustomLog /home/xen/logs/access.log cloudflare_real_ip

# Log blocked attempts to separate file
CustomLog /home/xen/logs/blocked.log cloudflare_real_ip env=blocked

# Log allowed requests with country info
CustomLog /home/xen/logs/allowed.log cloudflare_real_ip env=allowed

I personally just skipped the allowed.log and changed the main access.log to only save allowed requests like this:

# Log only allowed requests 
CustomLog /home/xen/logs/access.log cloudflare_real_ip env=allowed

That should do it. I haven't had any forum crashes since.