Session Table Full

**Steve Machol** · Sat 5 Jul '08, 8:35am

Note: Not all scheduled tasks are logged.

Run this query:

DELETE FROM session;

Then revert your footer template. You made a change somewhere that stopped the Scheduled Tasks from running and clearing out this table on a regular basis.

**ALanJay** · Sat 5 Jul '08, 2:32pm

Originally posted by Steve Machol

Note: Not all scheduled tasks are logged.

Run this query:

DELETE FROM session;

Then revert your footer template. You made a change somewhere that stopped the Scheduled Tasks from running and clearing out this table on a regular basis.

Thanks Steve - unfortunately we know the tasks are running and have manually run them more frequently but that does not seem to improve the matter.

We were wondering if there were other potential issues to be look at when the standard reply is not this issue.

**Zachery** · Sat 5 Jul '08, 2:35pm

What is your max_heap_table_size set to? You said 16k, do you mean 16 kilobytes? If so thats pretty small.

**ALanJay** · Sat 5 Jul '08, 2:52pm

Originally posted by Zachery

What is your max_heap_table_size set to? You said 16k, do you mean 16 kilobytes? If so thats pretty small.

I assume you mean in the my.cnf and the the answer is 16M (not k - sorry)

When using an admin interface to look at the session table it seems to be about 16M and then starts producing these errors.

We do get a lot of users - peak as recorded by your system is 27,000 on the 3rd July though the session table issue doesn't seem to be directly related ot the number of people online.

Regards
ALan

**Steve Machol** · Sat 5 Jul '08, 4:13pm

This has to be due to a modification. The default vB code does clear out this table on a regular basis.

**ALanJay** · Sat 5 Jul '08, 9:28pm

Originally posted by Steve Machol

This has to be due to a modification. The default vB code does clear out this table on a regular basis.

Well the admincp shows that the two hourly clean up apps are running (assuming the report can be relied upon) and assuming that these are what is supposed to clear out the session table.

33062 Hourly Cleanup .. 06:05, 6th Jul 2008 Hourly Cleanup #1 Completed
33061 Hourly Cleanup #2 05:40, 6th Jul 2008 Hourly Cleanup #2 Completed
33060 Hourly Cleanup .. 05:05, 6th Jul 2008 Hourly Cleanup #1 Completed
33059 Hourly Cleanup #2 04:40, 6th Jul 2008 Hourly Cleanup #2 Completed
33058 Hourly Cleanup .. 04:05, 6th Jul 2008 Hourly Cleanup #1 Completed
33057 Hourly Cleanup #2 03:40, 6th Jul 2008 Hourly Cleanup #2 Completed

If the control panel can show that the two cleanups are running but they still are not is it possible to run them outside of vBulletin - we have tried to run them manually but it does not seem to change the result significantly.

**Steve Machol** · Sun 6 Jul '08, 7:45am

And as I said this does work with the default vB code and templates. This means that you have a modification that broke this or there is something wrong with your database.

**ALanJay** · Sun 6 Jul '08, 8:19am

Originally posted by Steve Machol

And as I said this does work with the default vB code and templates. This means that you have a modification that broke this

And what I am trying to say is - you say this always work - you provide logs that say the scripts are running. Yet even though the scripts are running the response is there is something in the footer causing it not to work.

So my question was how can one run the script without there being an issue.

Originally posted by Steve Machol

or there is something wrong with your database.

Or maybe there are conditions where the scripts don't behave in the way they do normally. But as you don't seem to be willing to provide any information about what the script does to manage the "session" table I can't tell if there is a real problem or the script isn't completing due to an anomaly with say our mySQL config.

Usually you are very forthcoming on what is going on but all the responses to this query result in the same response.

I am told that if the cron jobs were not running correctly our site wouldn't be up at all and all the other cron jobs in your system perform the tasks one expects as you expect them without an issue.

As far as I know (from the logs) so does the scripts here in question but they don't appear to be having the desired effect.

So a little guidance over what SQL is being used to clear out the session table (which is a mysterios black box as far as we can tell

) would be helpful to let us get an idea if there is a problem with our mySQL install what that is and how we might be able to fix it.

**Steve Machol** · Sun 6 Jul '08, 8:22am

I'm not going to argue about this. I believe this is from a modification. You disagree. If you are not running ANY modifications then please feel to report this in the Bug Tracker here:

vBulletin Issue Tracker

http://www.vbulletin.com/forum/project.php?projectid=6

The bug genie, friendly issue tracking

**ALanJay** · Sun 6 Jul '08, 8:48am

Originally posted by Steve Machol

I'm not going to argue about this. I believe this is from a modification. You disagree. If you are not running ANY modifications then please feel to report this in the Bug Tracker here:

http://www.vbulletin.com/forum/project.php?projectid=6

Of course we are running modifications - though very minor changes these days there are some. We are trying to help by asking for sufficient help from you to be able to diagnose the problem which has only occurred recently with another peak in users.

As far as I can tell the only thing that the cleanup.php and cleanup2.php do with the session table is:

DELETE FROM " . TABLE_PREFIX . "session
WHERE lastactivity < " . intval(TIMENOW - $vbulletin->options['cookietimeout']) . "
### Delete stale sessions ###

So there is no apparent unusual mySQL requests.

One of our thoughts is that the version of mySQL we are running does not do table optimizations but that doesn't seem to be required.

One of my questions early on in this thread was can one alter the mySQL config to make the session table less prone to being full. Does changing the max_heap_table_size help or hinder - we did some tests today and it seemed to cause the mySQL config to run out of resources.

Our guess is that we are actully filling the session table - our assumption is that - reducing the cookie timeout - should help and having looked at the code potentially running the code for the session cleanup every 15minutes instead of every 30 minutes.

**ALanJay** · Mon 7 Jul '08, 2:57am

Well having reduced the cookietimeout the problem seems to have gone away (ie the problem was not that the script wasn't running just that it really was filling up).

This has lead to a few comments / complaints that the last active time is updated more frequently and asking if there is a way to change back to the previous numbers.

Well I can't see one without running into the same issues we had last week.

But looking at the code:

Code:

DELETE FROM " . TABLE_PREFIX . "session
        WHERE lastactivity < " . intval(TIMENOW - $vbulletin->options['cookietimeout']) . "
        ### Delete stale sessions ###

Is there any reason why the stale sessions script could not be more sophisticated (not sure if this makes sense or would put a stress on the database) but why couldn't you hard code a different timeout for registered users and guests?

From a quick poke around the database it looks like a userid of 0 is used for guests in the session table so - would something like this help solve the issue of clearing out the huge number of guests that I suspect was the problem in the first place:

Code:

DELETE FROM " . TABLE_PREFIX . "session
         WHERE lastactivity < " . intval(TIMENOW - $vbulletin->options['cookietimeout']) . " AND userid < 0
### Delete stale sessions from registered users
DELETE FROM " . TABLE_PREFIX . "session
          WHERE lastactivity < 900 AND userid = 0
### Delete stale sessions for guests ###
### 900 is 15 minutes / 600 is 10 minutes ###

Now there may be a very good reason for not doing this but I thought I would ask if anyone has done anything like this before.

One would need to change cleanup.php and cleanup2.php in includes/cron/ and probably create another version cleanup3.php to just run this code so that it is actually run every 15 minutes ie cleanup3.php

Code:

<?php
// ######################## SET PHP ENVIRONMENT ###########################
error_reporting(E_ALL & ~E_NOTICE);
if (!is_object($vbulletin->db))
{
        exit;
}

// ########################################################################
// ######################### START MAIN SCRIPT ############################
// ########################################################################

$vbulletin->db->query_write("
        DELETE FROM " . TABLE_PREFIX . "session
        WHERE lastactivity < " . intval(TIMENOW - $vbulletin->options['cookietimeout']) . "
        ### Delete stale sessions ###
");


($hook = vBulletinHook::fetch_hook('cron_script_cleanup')) ? eval($hook) : false;

log_cron_action('', $nextitem, 1);


?>

This might not be the right route to solve the issue of the Session Table being truely full but at first glance to me (as a not very technical outsider) it looks like a possible solution.

Comments?