Clean Up Sessions Table

[Parikesit]

Hi All,

 

OsC store session in database (TABLE_SESSIONS / table_sessions by default). Currently, there is no procedure to remove old session from database, except if tep_session_destroy() function call. But this only delete one session key (or, current user session). I don't know why OsC did not provide "clean up" session.

 

Just maybe you have hundred or thousand session stored in your database, ... for websites those have ran for long periode or have big hit-rate.

 

So, to clean up table session easly, you can add below script in your application_top.php page (catalog/includes/application_top):

 

    tep_db_query("delete from " . TABLE_SESSIONS . " where expiry < '" . time() . "'");

 

To prevent loaded each time user hit the page, you may place this at line 252 (when registering cart variable to session):

 

// create the shopping cart & fix the cart if necesary
 if (tep_session_is_registered('cart') && is_object($cart)) {
   if (PHP_VERSION < 4) {
     $broken_cart = $cart;
     $cart = new shoppingCart;
     $cart->unserialize($broken_cart);
   }
 } else {
   tep_db_query("delete from " . TABLE_SESSIONS . " where expiry < '" . time() . "'");

   tep_session_register('cart');
   $cart = new shoppingCart;
 }

 

Cheers,

zaenal

[Parikesit]

Just adding more safe method.

 

Maybe some user idle for a while and come back to our site and found their session, example shopping cart data was gone. So, to prevent this, we add a periode (how old the expire session to be deleted), example 1 week or one month.

 

in unix time:

- 1 week = 604800

- 1 month = 2592000

 

there will be no user idle for 1 month, right? <_<

 

1 week old:

tep_db_query("delete from " . TABLE_SESSIONS . " where expiry < '" . (time()-604800) . "'");

 

1 month old:

tep_db_query("delete from " . TABLE_SESSIONS . " where expiry < '" . (time()-2592000) . "'");

 

Cheers,

zaenal

[jashnu]

Hi!

 

I just wondered whether anybody else has tried this and does it cause any problems?

 

Cheers,

 

Jani

[jashnu]

You can do this with phpmyadmin (or such) as well. Run DELETE FROM `sessions`WHERE expiry < ( UNIX_TIMESTAMP( NOW( ) ) -2592000 ) and if you still have overhead run OPTIMIZE TABLE `sessions`. This is an easy way to reduce your database size.

[kev@num]

after nearly 4 years use of OSC my sessions table is up to 500MB!!

I've just found this thread so might give this ago..

[mme]

Anyway to put this into a cron job? so it runs automatically every week or so?

 

Thanks

[ozEworks]

If you are worreid about your session table after 4 years I'd check the customer_baskets. That might give you a surprise as well.

[Jan Zonjee]

Anyway to put this into a cron job? so it runs automatically every week or so?

How about running that query upon a successfull login in the admin section?

tep_db_query("DELETE FROM " . TABLE_SESSIONS . " WHERE expiry < ( UNIX_TIMESTAMP( NOW( ) ) -2592000 ))";

[GemRock]

after nearly 4 years use of OSC my sessions table is up to 500MB!! ..

 

what do you think the field "expiry" in that table is for? or maybe you are running a very (way too) old version of osc, or maybe it hasn't been setup (RE sessions) correctly?

from what I have seen, osc cleans up the expired sessions automatically, and i just looked at one osc shop that have over 1000 visits/250,000 hits daily, and the session table has only fewer than 100 records, all of which is within the past hour or so.

 

Ken

[Jan Zonjee]

from what I have seen, osc cleans up the expired sessions automatically

I think I read it on the German forum but certain Linux servers (I think it was Debian) have a peculiarity in their PHP versions so that the table is never cleared. From the top of my head it had something to do with a function dealing with session probability. The probabilty function makes it that only once in every x page requests the table is cleared but if the probability is zero it never happens.

[GemRock]

<br />...certain Linux servers (I think it was Debian)...it never happens.<br />

<br /><br /><br />

Thank for the info, Jan.

so it looks like i am just being lucky and happen to always use non debian linux servers or any other not so suitable for oscommerce servers, although i always believe first and foremost thing to do before setting up an osc shop is to seek professional advice RE selecting a suitable server which has been set up, among other things, with osc in mind. far too many problems reported and asked on this forum are in fact server related (including those security issues and their unnecessarily 'fixes' had the server be set up properly). it seems many people think servers are born equal but unforfunately they aren't.

 

Ken

[bugmenot]

I think I read it on the German forum but certain Linux servers (I think it was Debian) have a peculiarity in their PHP versions so that the table is never cleared. From the top of my head it had something to do with a function dealing with session probability. The probabilty function makes it that only once in every x page requests the table is cleared but if the probability is zero it never happens.

 

Actually, it is all because of the php built in function: session_set_save_handler (http://php.net/manual/en/function.session-set-save-handler.php). The GC-callback decides how long the session can live and after that time kills it. In OSC we can find:

 

    
   function _sess_gc($maxlifetime) {
     tep_db_query("delete from " . TABLE_SESSIONS . " where expiry < '" . time() . "'");
     return true;
   }

 

Some servers and versions of PHP doesn't execute the $gc-callback because of the session.gc_probability is set too low (and if that's set too low it will never be executed). Try to add this in the beginning of index.php (or set it manually in php.ini) and you'll see that the table will be cleaned up.

 

<?php
ini_set('session.gc_probability', 100);
ini_set('session.gc_divisor', 100);
?>

 

Note that standard values are 1 for probability and 100 for advisor (if I remember right). You don't want to call this function too often. Also note that you shouldn't write code that continuously make changes in php.ini (as above), some webhosts doesn't support that either. Make the change once and if you can do it manually (directly in php.ini) then do it manually.

 

-DEF------------------

session.gc_probability in conjunction with session.gc_divisor is used to manage probability that the gc (garbage collection) routine is started. Defaults to 1. See session.gc_divisor for details.

session.gc_divisor integer

 

session.gc_divisor coupled with session.gc_probability defines the probability that the gc (garbage collection) process is started on every session initialization. The probability is calculated by using gc_probability/gc_divisor, e.g. 1/100 means there is a 1% chance that the GC process starts on each request. session.gc_divisor defaults to 100.

----------------------

 

Anyhow, it's not because of your webhost this happens. It's bacuase OSC doesn't care about that different webhosts have different standard settings. If you are a good programmer you know this and can relate it to the proverb "don't blame the messenger". Period.

 

JRL