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   MySQL Session class
  3 
  4   This class encapsulates everything needed to store your PHP sessions
  5   in a MySQL database. To use it simply call Session::start() instead
  6   of session_start().
  7 
  8   You'll need a table like this in your database. You can change the
  9   name but the fields should remain as they are defined here.
 10 
 11   CREATE TABLE `sessions` (
 12     `id` varchar(50) NOT NULL,
 13     `name` varchar(50) NOT NULL,
 14     `expires` int(10) unsigned NOT NULL default '0',
 15     `data` text,
 16     PRIMARY KEY  (`id`, `name`)
 17   ) TYPE=InnoDB;
 18 ***********************************************************************/
 19 class Session
 20 {
 21   protected $_lifetime = 900;
 22   protected $_db = false;
 23   protected $_table = 'sessions';
 24   protected $_name = 'phpsess';
 25 
 26   static public function start($host = 'localhost', $username = 'root', $password = '', $db = 'sessionstore', $table = 'sessions', $_lifetime = 0)
 27   {
 28     // Create the object
 29     $GLOBALS['_SESSION_OBJ_'] = new self($host, $username, $password, $db, $table, $lifetime);
 30     // Hook up the handler
 31     session_set_save_handler(
 32             array($GLOBALS['_SESSION_OBJ_'], 'open'),
 33             array($GLOBALS['_SESSION_OBJ_'], 'close'),
 34             array($GLOBALS['_SESSION_OBJ_'], 'read'),
 35             array($GLOBALS['_SESSION_OBJ_'], 'write'),
 36             array($GLOBALS['_SESSION_OBJ_'], 'destroy'),
 37             array($GLOBALS['_SESSION_OBJ_'], 'gc')
 38           );
 39     // Start the session
 40     session_start();
 41   }
 42 
 43   private function __construct($host = 'localhost', $username = 'root', $password = '', $db = 'sessionstore', $table = 'sessions', $_lifetime = 0)
 44   {
 45     // By default we use the session _lifetime in php.ini, but this can be overridden in code
 46     $this->_lifetime = ($_lifetime == 0 ? get_cfg_var('session.gc_max_lifetime') : $_lifetime);
 47     // This is the table where session data is to be stored
 48     $this->_table = $table;
 49     // Now we connect to the database, throwing expections if anything fails
 50     $this->_db = @mysql_connect($host, $username, $password);
 51     if ($this->_db === false)
 52       throw new Exception('Failed to connect to the session store', 1);
 53     if (false === @mysql_select_db($db, $this->_db))
 54       throw new Exception('Failed to select session store', 2);
 55   }
 56 
 57   public function open($path, $name)
 58   {
 59     // Store the session name for future use, we don't have any use for the path
 60     $this->_name = $name;
 61     // Everything is OK if we have a connection to the database
 62     return ($this->_db !== false);
 63   }
 64 
 65   public function close()
 66   {
 67     // Run the garbage collector 10% of the time
 68     if (rand(1, 10) == 5) {
 69       $this->gc($this->_lifetime);
 70     }
 71     // Close the database connection
 72     return @mysql_close($this->_db);
 73   }
 74 
 75   public function read($id)
 76   {
 77     // By default we return nothing
 78     $retval = '';
 79 
 80     // Try to read an entry from the database
 81     $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);
 82     if ($result !== false and mysql_num_rows($result) > 0) {
 83       // Found one, get it
 84       $retval = mysql_result($result, 0, 0);
 85     }
 86 
 87     return $retval;
 88   }
 89 
 90   public function write($id, $data)
 91   {
 92     $retval = false;
 93     // Build the query. We use the MySQL ON DUPLICATE KEY feature to do an insert/update in one query.
 94     $sql = 'insert into `'.$this->_table.'` set ';
 95     $sql.= 'id = "'.mysql_real_escape_string($id, $this->_db).'", ';
 96     $sql.= 'name = "'.mysql_real_escape_string($this->_name, $this->_db).'", ';
 97     $sql.= 'expires = '.(time() + $this->_lifetime).', ';
 98     $sql.= 'data = "'.mysql_real_escape_string($data, $this->_db).'" ';
 99     $sql.= 'on duplicate key update expires = values(expires), data = values(data)';
100     // Run it and return true if it was successful
101     $result = mysql_query($sql, $this->_db);
102     if ($result !== false and mysql_affected_rows($this->_db) > 0) {
103       $retval = true;
104     }
105     @mysql_free_result($result);
106     return $retval;
107   }
108 
109   public function destroy($id)
110   {
111     // Remove this session from the database
112     $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);
113     if ($result !== false and mysql_affected_rows($this->_db) > 0) {
114       return true;
115     }
116     return false;
117   }
118 
119   public function gc($_lifetime)
120   {
121     // Remove any sessions that have expired
122     $result = mysql_query('delete from `'.$this->_table.'` where expires < '.time(), $this->_db);
123     return ($result === false ? 0 : mysql_affected_rows($this->_db));
124   }
125 }
blog comments powered by Disqus