Session data in cookies vs database

[eluser]mradlmaier[/eluser]
Hi All,

I am thinking about moving my seesion from cookie storage to database storage.
Some questions come to mind:

First about the database schema:
The user guide states following sql for creating the table:

Code:

CREATE TABLE IF NOT EXISTS  `ci_sessions` (
session_id varchar(40) DEFAULT '0' NOT NULL,
ip_address varchar(16) DEFAULT '0' NOT NULL,
user_agent varchar(50) NOT NULL,
last_activity int(10) unsigned DEFAULT 0 NOT NULL,
user_data text NOT NULL,
PRIMARY KEY (session_id)
);

1. If i look at that, I assume that all items of a session (which I access through $this->session->userdata('my_item')) are all stored together in on single database column named userdata. Is that right?

2. What about flashdata? Are they stored in the userdata column too?

3. Having said that, to move my session to database storage, I only have to create that database table and enable database storage in the config.php, and everything else stays the same? Including flashdata?

Somebody enlighten me pls,
Michael

[eluser]Dam1an[/eluser]
1 yes
2 yes
3 yes

When you enable the database for sessions, at the start of every request it will look for a row with that session id, and if it exists will take the row, de-serialize the userdata into an array, which is then used when fetching session data
At the end of the request the array 9with any changes) is then serialized and written back

Flash data is stores in the userdata field as well (I think it has a new: prefix or something)

[eluser]mradlmaier[/eluser]
OK, thanks, that sounds good to me, any other things to keep in mind?

The user guide says the prefix is 'flash_'


Michael

[eluser]Dam1an[/eluser]
Ah, I just checked in the session librray, and it appears it uses both lol

Code:

$new_flashdata_key = $this->flashdata_key.':new:'.$key; // flashdata_key is flash
$this->set_userdata($new_flashdata_key, $value);

which results in flash:new:key or flash:old:key

[eluser]mradlmaier[/eluser]
From analysing the rows which are written to my ci_sessions table, i conclude the following:

1. Each time a logged in user requests a page, a row is created in the ci_sessions table with his user_data, last_activity, user_agent and ip_address and session_id as primary key of the row. I am not 100% sure, triggers the page request itself, or triggers the first time within a request, that particalur session is accessed by calling $this->session->....

2. When $this->session->sess_destroy() is called the most recent rows' user_data of that users is cleared.

3. When the timeout of a session is reached, that row is removed completely

4. the user_agents' value is only written when the session starts, otherwise it set to 0

That means:
To get a list of all currently logged in users, i need to query for all rows which
a) dont have the user_agent set to 0
AND
b) dont have the user_data cleared

Is that correct?

[eluser]sl3dg3hamm3r[/eluser]
Why not simply querying the field last_activity minus e.g. 5 minutes?

[eluser]mradlmaier[/eluser]
[quote author="sl3dg3hamm3r" date="1248723816"]Why not simply querying the field last_activity minus e.g. 5 minutes?[/quote]

Because if a user requests several pages in the last 5 mins, there will be several rows of this user in the ci_sessions table.

As far as i can tell, the 2 conditions i described before, filter for all distinct logged in users.

Maybe the name sessions table is a bit misleading.
From what I understand by looking at the table's rows, it stores a row for each single request of all users having a session. So one row does not correspond directly to a user's session, but to one request of each user having a session.
Simply put, there may be several rows for one user's session.
So you actually can track the path a user takes through your website with the rows stored in this table.

And, as all session vars are serialized into a single field userdata, you can not easily query for a user in a WHERE or HAVING clause.

BTW, user guide says, there is built-in garbage collection, so after a sessions times out, that session's rows are deleted.

[eluser]sl3dg3hamm3r[/eluser]
[quote author="mradlmaier" date="1248735866"]Because if a user requests several pages in the last 5 mins, there will be several rows of this user in the ci_sessions table.[/quote]

I don't know how you end up with several entries for the same session - if you do a session_destroy(), the entry is deleted. If you create and update one, there will be only one entry in the table...

[eluser]mradlmaier[/eluser]
[quote author="sl3dg3hamm3r" date="1248737675"][quote author="mradlmaier" date="1248735866"]Because if a user requests several pages in the last 5 mins, there will be several rows of this user in the ci_sessions table.[/quote]

I don't know how you end up with several entries for the same session - if you do a session_destroy(), the entry is deleted. If you create and update one, there will be only one entry in the table...[/quote]

I don't know how I end up with several entries for the same session - I agree, this does not seem to be intuitive, I expected a single row for each session, too.
But it is the observation i made by testing it. I did not invent the session class...

But i tested it again, with interesting results
Below is how my session table looks after i switched to database storage and made some requests:

Code:

DROP TABLE IF EXISTS `usr_web1301_1`.`ci_sessions`;
CREATE TABLE  `usr_web1301_1`.`ci_sessions` (
  `session_id` varchar(40) NOT NULL default '0',
  `ip_address` varchar(16) NOT NULL default '0',
  `user_agent` varchar(50) NOT NULL,
  `last_activity` int(10) unsigned NOT NULL default '0',
  `user_data` text NOT NULL,
  PRIMARY KEY  (`session_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `usr_web1301_1`.`ci_sessions`
--

/*!40000 ALTER TABLE `ci_sessions` DISABLE KEYS */;
LOCK TABLES `ci_sessions` WRITE;
INSERT INTO `usr_web1301_1`.`ci_sessions` VALUES  ('db37474e45ff1b7d057d9a378beee251','127.0.0.1','Mozilla/5.0 (X11; U; Linux x86_64; de; rv:1.9.0.4)',1248527407,''),
('479c973ff5baf42355d3a67cf8f87764','127.0.0.1','Mozilla/5.0 (X11; U; Linux x86_64; de; rv:1.9.0.4)',1248698514,''),
('640fbaff45d9faa9a363ea4414cfa588','127.0.0.1','0',1248526092,'a:3:{s:11:\"logged_user\";s:5:\"michi\";s:7:\"user_id\";s:1:\"9\";s:8:\"username\";s:5:\"michi\";}'),
('5eef63dc2f67bf37c515703d548b2647','127.0.0.1','0',1248527542,'a:3:{s:11:\"logged_user\";s:5:\"michi\";s:7:\"user_id\";s:1:\"9\";s:8:\"username\";s:5:\"michi\";}'),
('3061af90a973b54058b24f05c9aa605f','127.0.0.1','0',1248527536,'a:3:{s:11:\"logged_user\";s:5:\"michi\";s:7:\"user_id\";s:1:\"9\";s:8:\"username\";s:5:\"michi\";}'),
('03826023b34da2a637e7ee18be914903','127.0.0.1','0',1248527538,'a:3:{s:11:\"logged_user\";s:5:\"michi\";s:7:\"user_id\";s:1:\"9\";s:8:\"username\";s:5:\"michi\";}'),
('6970e59b2424f88701e1f415fc50cc0e','127.0.0.1','0',1248527531,'a:3:{s:11:\"logged_user\";s:5:\"michi\";s:7:\"user_id\";s:1:\"9\";s:8:\"username\";s:5:\"michi\";}'),
('661f9e90378c1a9c09284b4d520350ce','127.0.0.1','0',1248527528,'a:3:{s:11:\"logged_user\";s:5:\"michi\";s:7:\"user_id\";s:1:\"9\";s:8:\"username\";s:5:\"michi\";}');
INSERT INTO `usr_web1301_1`.`ci_sessions` VALUES  ('56f0778e05811a69ae610bfd8f2a8a6a','127.0.0.1','Mozilla/5.0 (X11; U; Linux x86_64; de; rv:1.9.0.4)',1248544331,''),
('b748bc0e24a3873e5d93dd3c9f6b1b0a','127.0.0.1','Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.8.1',1248526280,''),
('3376e4b157f2199cbcbeb9cccaaf68e9','127.0.0.1','Mozilla/5.0 (X11; U; Linux x86_64; de; rv:1.9.0.4)',1248528126,''),
('451575e8063d0e5700e7e3c2e80c8010','127.0.0.1','0',1248526151,'a:3:{s:11:\"logged_user\";s:4:\"anna\";s:7:\"user_id\";s:2:\"27\";s:8:\"username\";s:4:\"anna\";}'),
('0718e98ee4c186ff275640da719f0232','127.0.0.1','0',1248527464,'a:3:{s:11:\"logged_user\";s:5:\"michi\";s:7:\"user_id\";s:1:\"9\";s:8:\"username\";s:5:\"michi\";}'),
('db62a65e3a4a42bde84105f717300719','127.0.0.1','0',1248527521,'a:3:{s:11:\"logged_user\";s:5:\"michi\";s:7:\"user_id\";s:1:\"9\";s:8:\"username\";s:5:\"michi\";}'),
('93c94017ea21a94f95ec5943ea9d9f07','127.0.0.1','0',1248527484,'a:3:{s:11:\"logged_user\";s:5:\"michi\";s:7:\"user_id\";s:1:\"9\";s:8:\"username\";s:5:\"michi\";}'),
('ca6fe1e17ace3346245ed4fad9b522ad','127.0.0.1','0',1248527488,'a:3:{s:11:\"logged_user\";s:5:\"michi\";s:7:\"user_id\";s:1:\"9\";s:8:\"username\";s:5:\"michi\";}');
INSERT INTO `usr_web1301_1`.`ci_sessions` VALUES  ('42468aac711370e61161ff32ef4b83bb','127.0.0.1','0',1248527512,'a:3:{s:11:\"logged_user\";s:5:\"michi\";s:7:\"user_id\";s:1:\"9\";s:8:\"username\";s:5:\"michi\";}'),
('8e10dfca7f8fd115cd36812fcae6c8cf','127.0.0.1','Mozilla/5.0 (X11; U; Linux x86_64; de; rv:1.9.0.4)',1248701324,''),
('46f94b09aaf346cf5e6317d9a577571b','127.0.0.1','Mozilla/5.0 (X11; U; Linux x86_64; de; rv:1.9.0.4)',1248703208,''),
('3f5fdc59d9e2bb9383ce41bf4c426eab','127.0.0.1','Mozilla/5.0 (X11; U; Linux x86_64; de; rv:1.9.0.4)',1248716280,''),
('a8ed8a5d169f9c73f1797a538b8cd813','127.0.0.1','Mozilla/5.0 (X11; U; Linux x86_64; de; rv:1.9.0.4)',1248721693,'a:4:{s:11:\"logged_user\";s:4:\"susi\";s:7:\"user_id\";s:2:\"13\";s:8:\"username\";s:4:\"susi\";s:22:\"flash:new:nav_bar_view\";s:16:\"gallery_nav_view\";}');
UNLOCK TABLES;

Now, a couple of days later, i looked into the table again, and it now looks very intuitive, like one expects, with one row for each session.

Maybe switching to database storage causes some inconsistences in the data the session class creates(the hybrid session class, based on native php sessions, see http://codeigniter.com/wiki/Session_Hybrid/) if the server is not restarted.

Seems after having restart the server this behaviour disappeared....