Odd issue with "session" table

[MarkFL]

I provide technical assistance at a forum running vB 4.2.2, and a few days ago for no apparent reason the "session" table began to fill up and throw a MySQL error preventing our users from using the site.

I directed the site owner to truncate the "session" table via phpMyAdmin, and we were back up. However, the issue came back very soon.

The first thing I checked was to make certain the cronimage was intact in all of the footer templates, which it is, and I verified the image is present in the DOM of all the styles.

Next I checked the Scheduled Tasks, and verified that the 2 hourly cleanup tasks are being run. One was set at 5 after the hour and the other at 20 after, and I changed to one set at 20 after to 35 after so that the two hourly cleanups would be 30 minutes apart. The issue persisted.

As a temporary fix, I wrote a plugin that prunes the "session" table of all expired rows any time one of the 4 senior staff members loads the index, showthread, search and online scripts. When I leave my compute for more than a few minutes I leave Who's Online running so it will auto-refresh and keep pruning the table.

Earlier today, I followed:

AdminCP -> Maintenace -> Diagnostics -> System Information -> Table Status

and I found that the "session" table has very small values for Data_length and Max_data_length:

Data_length: 11744
Max_data_length: 12768

At MHB (Math Help Boards) these values are:

Data_length: 127296
Max_data_length: 13486755

And on my local dev site, they are:

Data_length: 129500
Max_data_length: 14738963

So my questions are:


Is it possible that these values were recently changed (via plugin code or host) to be much smaller?
How do we go about resetting them to larger values?


I appreciate any advice you can give. :D

[nhawk]

Anything is possible with a plugin.

To increase the size, look into this MySql query...


alter table tablename max_rows = XXXXXXX avg_row_length = XXXX

[TheLastSuperman]

What is your session timeout set to?
Admin CP > Settings > Options > Cookies and HTTP Header Options > Session Timeout

Some sites try to increase that (by daysssss not minutes) and too large of a session timeout setting without a plugin to conteract it can cause issues as you've described. When you prune the session table, granted it reduces the session table from filling up and preventing access however it also resets the user and if they were previously logged in now they aren't and if in a middle of a post and autosave didn't kick in just in time bam now typed post is now gone as well. Be careful with the session table, you can also simply use sql TRUNCATE to wipe the slate clean but same thing will happen i.e. all users still on the site BUT seems as if everyone logged out all at once that way.

[Dave]

How many entries were in the table and what is the difference between the timestamp of the first and last entry in the table?
Also if it fills up so quickly, are there thousands of bots crawling that forum or something?

[MarkFL]

I don't have phpMyAdmin access, or the ability to run a manual query. And there are just a few bots at any given time.

The site owner did run an "alter table" query, but it had no effect.

I'm thinking the database may have to be altered via server config. I just now noticed the max_heap_table_size is set at 16384 while at my local dev site it's at 16777216 (2^10 times as large). We'll have a go at changing that. :)

--------------- Added 1470352791 at 1470352791 ---------------

What is your session timeout set to?
Admin CP > Settings > Options > Cookies and HTTP Header Options > Session Timeout

Some sites try to increase that (by daysssss not minutes) and too large of a session timeout setting without a plugin to conteract it can cause issues as you've described. When you prune the session table, granted it reduces the session table from filling up and preventing access however it also resets the user and if they were previously logged in now they aren't and if in a middle of a post and autosave didn't kick in just in time bam now typed post is now gone as well. Be careful with the session table, you can also simply use sql TRUNCATE to wipe the slate clean but same thing will happen i.e. all users still on the site BUT seems as if everyone logged out all at once that way.

We have the session timeout at 900 right now. So far no one is complaining about being logged out in the middle of a session though. :)

[MarkFL]

I first had the site owner run the manual query:

alter table `session` max_rows = 25000 avg_row_length = 532

That didn't work.

Then I directed the owner to run the manual query:

set @@max_heap_table_size=16777216

That didn't work...and I began to suspect the db would need to be restarted.

I directed the owner to the following link:

How to edit the MySQL my.cnf file (http://www.inmotionhosting.com/support/website/general-server-setup/edit-mysql-my-cnf)

I was informed by the owner:

That's not possible as we're not running on a VPS or dedicated server.

Okay, well I then suggested contacting the host for assistance. After waiting nearly a week for them to reply, they finally edited the my.cnf file, and we appear to be back in business.

We still have no idea how the MySQL variable was changed in the first place. :o