[Solved] Cdbhttpsession - Queryscalar Always Returns False

Yii 1.1 General Discussion
1

Hi there,

I’ve got some very strange behaviour here and was wondering if anybody has an idea what the issue could be…

I’m currently testing to see if storing the session data in a database table is beneficial for our application and so I’ve added this to my components as described in this post: Slow db session table…




	// application components

	'components'=>array(

             ...   

               'db_session'=>array(

				'class' => 'CDbConnection',

				'connectionString' => 'mysql:host=localhost;dbname=session_db',

				'emulatePrepare' => true,

				'username' => 'xyz',

				'password' => 'xyz',

				'charset' => 'utf8',

		),

		'session'=>array(

				'class' => 'CDbHttpSession',

				'connectionID' => 'db_session',

				'autoCreateSessionTable' => false,				

		),

             ...

         ),

I’ve also created the DB with the table ‘YiiSession’ and the columns with index as described in the post above.

Now the strange thing is that when I try to log in the new session entry is created in the database as expected but when it comes to ‘public function writeSession($id,$data)’ in CDbHttpSession there is this line:


if($db->createCommand()->select('id')->from($this->sessionTableName)->where('id=:id',array(':id'=>$id))->queryScalar()===false)

The queryScalar() here always returns ‘false’, no matter if the ID actually exists in the table or not. Which in turn produces this error message:


CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 

1062 Duplicate entry 'b5pesr9jmdnu6hgc6nelfmrjso8733f8' for key 'PRIMARY'. 

The SQL statement executed was: INSERT INTO `YiiSession` (`id`, `data`, `expire`) VALUES (:id, :data, :expire)

So far I’ve tried it with different databases and even adding the YiiSession-table to another existing database, always with the same result.

If I do a normal ‘SELECT count(*)’ with the same parameters I get an accurate result.

Could it be a PDO-bug or something like that?

I’m forced to work on SLES11 SP2, PHP 5.3.8, pdo_mysql: mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $

Any ideas would be greatly appreciated.

2

Right… think I found the problem.

In the Create Table statement the id is specified as CHAR(32):




CREATE TABLE YiiSession

(

    id CHAR(32) PRIMARY KEY,

    expire INTEGER,

    data BLOB

)

However, on our system it looks like the id is longer. With a bit of research it looks like these php.ini settings are responsible for that:




session.hash_bits_per_character	= 5

session.hash_function = sha256

So I increased the length of the id column and the queryScalar() now returns the correct value.

This was all new to me since I haven’t paid much attention to session ids up until now so maybe this could be mentioned in the documentation? Especially as the CHAR(32) is hard-coded into CDbHttpSession as well!

3

Hi,

I am also using the same php settings, what column size are you using now for id?

I have tried changing id CHAR() to different values but does not seem to have much effect. Is it possible that the session table has already been created? I looked into /protected/runtime and did not see any session table.

Much appreciated

4

Hi there,

As I said at the time I was only testing and have since gone away from DB-Sessions again. However, instead of using the automatic creation of the session table I then created it manually in my DB with “id VARCHAR(255)” to be variable with the size and since it was only for testing it was ok. I’m sure this could be optimised.

What I’m not sure about is why you are looking in your /protected/runtime? The idea of a session table in DB is that it is all created in the database you have specified in the components.

Or am I missing something!?

Just to clarify… the problem I ran into was that I let Yii automatically generate the session table in my DB since it just uses its default values. Once I created it myself with a longer id-field it worked fine.

Hope this helps…