MySQL Sessions

 

A share-nothing approach to web development is great for scalability, but there aren't many web applications that don't need to share anything between requests. The solution PHP (and most other web development lanugages) utilises is sessions. Sessions basically allow you to store some data between requests. That data is tied to an ID that gets passed between the browser and server in every request, using a cookie, in the URL or in GET/POST parameters.

The default data store for PHP sessions is files, and that's fine so long as you only have one server, or you can tie each user to one server. When your app scales to the point where each request from a given user could go to one of any number of servers you need to replace this storage mechanism with something accessible from all of them. A database is the obvious choice.

I wrote the code below to solve this problem for a site that get > 1 million unique users per month (at the time of writing). It's designed for ease of use and maximum performance. The session table exists in its own database so it can be moved to a dedicated server if required. It would also be trivial to split the session data across several tables by hashing or modifying the session ID to indicate which shard it was on.

The code is liberally commented so I won't waste electrons describing it separately. Hopefully the way it works is straightforward and easy to understand. Don't forget to check out the session documentation on the PHP website for full details about putting in your own session handler.

Update: Thanks to Jim Lucas for spotting some errors. I've updated the code below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
/***********************************************************************
    MySQL Session class

    This class encapsulates everything needed to store your PHP sessions
    in a MySQL database. To use it simply call Session::start() instead
    of session_start().

    You'll need a table like this in your database. You can change the
    name but the fields should remain as they are defined here.

    CREATE TABLE `sessions` (
      `id` varchar(50) NOT NULL,
      `name` varchar(50) NOT NULL,
      `expires` int(10) unsigned NOT NULL default '0',
      `data` text,
      PRIMARY KEY  (`id`, `name`)
    ) TYPE=InnoDB;
***********************************************************************/
class Session
{
    protected $_lifetime = 900;
    protected $_db = false;
    protected $_table = 'sessions';
    protected $_name = 'phpsess';

    static public function start($host = 'localhost', $username = 'root', $password = '', $db = 'sessionstore', $table = 'sessions', $_lifetime = 0)
    {
        // Create the object
     $GLOBALS['_SESSION_OBJ_'] = new self($host, $username, $password, $db, $table, $lifetime);
        // Hook up the handler
     session_set_save_handler(
                        array($GLOBALS['_SESSION_OBJ_'], 'open'),
                        array($GLOBALS['_SESSION_OBJ_'], 'close'),
                        array($GLOBALS['_SESSION_OBJ_'], 'read'),
                        array($GLOBALS['_SESSION_OBJ_'], 'write'),
                        array($GLOBALS['_SESSION_OBJ_'], 'destroy'),
                        array($GLOBALS['_SESSION_OBJ_'], 'gc')
                    );
        // Start the session
     session_start();
    }

    private function __construct($host = 'localhost', $username = 'root', $password = '', $db = 'sessionstore', $table = 'sessions', $_lifetime = 0)
    {
        // By default we use the session _lifetime in php.ini, but this can be overridden in code
     $this->_lifetime = ($_lifetime == 0 ? get_cfg_var('session.gc_max_lifetime') : $_lifetime);
        // This is the table where session data is to be stored
     $this->_table = $table;
        // Now we connect to the database, throwing expections if anything fails
     $this->_db = @mysql_connect($host, $username, $password);
        if ($this->_db === false)
            throw new Exception('Failed to connect to the session store', 1);
        if (false === @mysql_select_db($db, $this->_db))
            throw new Exception('Failed to select session store', 2);
    }

    public function open($path, $name)
    {
        // Store the session name for future use, we don't have any use for the path
     $this->_name = $name;
        // Everything is OK if we have a connection to the database
     return ($this->_db !== false);
    }

    public function close()
    {
        // Run the garbage collector 10% of the time
     if (rand(1, 10) == 5) {
            $this->gc($this->_lifetime);
        }
        // Close the database connection
     return @mysql_close($this->_db);
    }

    public function read($id)
    {
        // By default we return nothing
     $retval = '';

        // Try to read an entry from the database
     $result = mysql_query('select data from `'.$this->_table.'` where id = "'.mysql_real_escape_string($id, $this->_db).'" and name = "'.mysql_real_escape_string($this->_name, $this->_db).'" and expires > '.time().' order by expires desc', $this->_db);
        if ($result !== false and mysql_num_rows($result) > 0) {
            // Found one, get it
         $retval = mysql_result($result, 0, 0);
        }

        return $retval;
    }

    public function write($id, $data)
    {
        $retval = false;
        // Build the query. We use the MySQL ON DUPLICATE KEY feature to do an insert/update in one query.
     $sql = 'insert into `'.$this->_table.'` set ';
        $sql.= 'id = "'.mysql_real_escape_string($id, $this->_db).'", ';
        $sql.= 'name = "'.mysql_real_escape_string($this->_name, $this->_db).'", ';
        $sql.= 'expires = '.(time() + $this->_lifetime).', ';
        $sql.= 'data = "'.mysql_real_escape_string($data, $this->_db).'" ';
        $sql.= 'on duplicate key update expires = values(expires), data = values(data)';
        // Run it and return true if it was successful
     $result = mysql_query($sql, $this->_db);
        if ($result !== false and mysql_affected_rows($this->_db) > 0) {
            $retval = true;
        }
        @mysql_free_result($result);
        return $retval;
    }

    public function destroy($id)
    {
        // Remove this session from the database
     $result = mysql_query('delete from `'.$this->_table.'` where id = "'.mysql_real_escape_string($id, $this->_db).'" and name = "'.mysql_real_escape_string($this->_name, $this->_db).'"', $this->_db);
        if ($result !== false and mysql_affected_rows($this->_db) > 0) {
            return true;
        }
        return false;
    }

    public function gc($_lifetime)
    {
        // Remove any sessions that have expired
     $result = mysql_query('delete from `'.$this->_table.'` where expires < '.time(), $this->_db);
        return ($result === false ? 0 : mysql_affected_rows($this->_db));
    }
}
 
 
blog comments powered by Disqus