Forums

Storing sessions in the database

Permalink 1 user found helpful
Hi guys,

I'm setting up a load balanced configuration that needs high availability, so I need to store the sessions in the database. Just wondering whether anyone has already done this in C5 and can give me any tips / pointers before I get started??

Thanks...
jbx
 
Mnkras replied on at Permalink Reply
Mnkras
i don't think anyone has
jbx replied on at Permalink Best Answer Reply
jbx
ok, so this solution does involve editing the core.
@frz: Any chance of this being included in the core? I reckon as more high availability sites start using C5, this feature will become more and more important.

Helpfully, the main code for this is provided for us by adodb. So we only have to modify a single core file, plus define a few new constants in the site.php.

Add the following to the top of concrete/startup/session.php
if (SESSION_HANDLER == 'mysql') {
    Loader::library('3rdparty/adodb/session/adodb-cryptsession2');
    ADOdb_Session::config(SESSION_HANDLER,
                          SESSION_DB_SERVER,
                          SESSION_DB_USERNAME,
                          SESSION_DB_PASSWORD,
                          SESSION_DB_DATABASE,
                          array('table' => SESSION_DB_TABLE)
                         );
}


Then define the constants in site.php:
define('SESSION_HANDLER', 'mysql');
    define('SESSION_DB_SERVER', DB_SERVER);
    define('SESSION_DB_USERNAME', DB_USERNAME);
    define('SESSION_DB_PASSWORD', DB_PASSWORD);
    define('SESSION_DB_DATABASE', DB_DATABASE);
    define('SESSION_DB_TABLE', 'PhpSession');


We also need to create the db table:
CREATE TABLE PhpSession(
      sesskey VARCHAR( 64 ) NOT NULL DEFAULT '',
        expiry DATETIME NOT NULL ,
      expireref VARCHAR( 250 ) DEFAULT '',
      created DATETIME NOT NULL ,
      modified DATETIME NOT NULL ,
      sessdata LONGTEXT,
      PRIMARY KEY ( sesskey ) ,
      INDEX sess2_expiry( expiry ),
      INDEX sess2_expireref( expireref )
)


Hope that helps someone :)

Jon
andrew replied on at Permalink Reply
andrew
I don't think this will make it to 5.4.1, but this is great! I can definitely see us enabling SESSION_HANDLER == 'mysql' as a way to do exactly what you've done, likely using the session handler (or perhaps Zend_Session, but ADODB's session handling looks pretty nice so we may not even need to go that route.

Nice work, looks great. Let me know if you encounter any issues with this setup - I can definitely see us adding this to the core.
benede replied on at Permalink Reply
benede
Someone, quickly invent a way of passing beer over the internet now.

That's exactly what I needed.

Thanks very much!
jbx replied on at Permalink Reply
jbx
You're welcome :)
afixia replied on at Permalink Reply
afixia
Hey guys, I've been installing this script on all of my sites and so far great except for the latest version of C5 - 5.4.2.2. Is there something that came out in this latest version that causes the issue or is there something built in now to handle this?

This is my error:

Fatal error: session_start() [<a href='function.session-start'>function.session-start</a>]: Failed to initialize storage module: user (path: C:\WINDOWS\Temp) in C:\Inetpub\wwwroot\bewell\concrete\models\user.php on line 71

Could this be some kind of permission problem on my temp file maybe?
afixia replied on at Permalink Reply
afixia
Just tested this on my new server environment and it works with 5.4.2.2 so must be my old server's environment.
caybar replied on at Permalink Reply
FYI: I implemented this solution in my development environment and found that although session information appeared to be saved into the database table, it was _not_ being persisted from page to page.

I was able to resolve the problem by changing the ADOdb driver to match the PHP MySQL extension I was using, which required me to change "mysql" to "mysqli".

Hope this helps somebody.