zen of coding

Important database session limitation

If you are using a database to keep your sessions, there is one rather significant limitation which you should be aware of.

It does depend on the specifics of your DB, but I am going to guess that in most cases it is something that could happen to any database…

For example, we were using MySQL to keep the session data.
Looking at the table you’ll see that session data is serialized and stored in the “data” field…

By default that field is set to MySQL’s “TEXT” type, which has a size limitation of ~ 65K.

I know, I know… why would you want sessions larger than 65K?
Well…

“640k ought to be enough for anybody”

So, just in case, you do need a larger session the remedy is pretty simple, change the field type to “MEDIUMTEXT”, which effectively gives you ~ 16MB.

p.s. If you need even a larger size you can go with “LONGTEXT”.

For your reference, here’s the semi-official breakdown of different text-type fields in MySQL and their limitations:

TEXT
A BLOB or TEXT column with a maximum length of 65535 (2^16 – 1) characters

MEDIUMTEXT
A BLOB or TEXT column with a maximum length of 16777215 (2^24 – 1) characters

LONGTEXT
A BLOB or TEXT column with a maximum length of 4294967295 (2^32 – 1) characters

  • Great tip, thank you!

    Why are you using database for session storage? What’s the benefif? security? perfomance?

  • @Mauro Zadunaisky

    No problem.

    Well, one benefit is that we already have proper fail-over and redundancy for our DB, and because we have multiple web servers we need a single point of storage. Prior to that we used memcache, but found it to be a little unreliable (I can’t really get into much detail, since I don’t know all the specifics). Again, we’d need to setup proper fail-over for memcache servers, which adds an extra level of maintenance and unnecessary complexity, at least for the time being.

    Another benefit to using the DB is that you can further extend the session handling to “remember” users’ shopping carts, or preform additional analysis. (Since your data is in a relational DB, it makes extending base functionality a bit easier).

    Performance-wise we didn’t see any difference one way or another.

  • Dan

    Good to know. This is probably one of those things that I or a friend will run into at some point. Thanks for the tip.

  • Great info.Thanks for sharing
    Use of sessions for DB is new for me.

  • One benefit to store sessions in DB is using cluster for web site and two DBs with master to master replication. We’re trying to setup it. But by some reason session is stored partially. Even with LONGTEXT data field. Have no idea why.

  • teknoid

    @Michael

    Remember that cake serializes the session object data. One known issue (perhaps in older versions) of PHP is that serialization can fail when certain (null or empty line) characters are stored in the session. Unfortunately I don’t have a link handy, but a little googlin’ might give more insight.

  • Consider that you are storing a whole model on the session and this model already has some html or special chars in it. Wouldn’t this break the db session in a similar way that a sql injection would do with a database?

  • teknoid

    @Cristiano
    Whole model… or model data?
    I would not recommend storing the entire object in the session. As far as any special chars, whenever information is saved into the DB cake takes special care of escaping all fields and values.

%d bloggers like this: