Store Symfony Sessions in Database with Doctrine

By marc • May 4th, 2008 • Category: Symfony

Here is how you can store your sessions in a central database with Doctrine in symfony. If you are trying to do this with Propel, the check out my follow up post: Store Symfony Sessions in Database with Propel

Step1 - Create your sessions table

  1.  
  2. CREATE TABLE IF NOT EXISTS `sessions` (
  3. `sess_id` varchar(64) NOT NULL,
  4. `sess_data` text NOT NULL,
  5. `sess_time` int(11) NOT NULL,
  6. KEY `sess_id` (`sess_id`)
  7. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Thanks to Andy from blog.t8d.de for pointing out that there needs to be an index on the ’sess_id’ column. You’ll want to make sure that it’s there so that your queries don’t drag when you start getting thousands of records in your sessions table.

Step 2 - Edit /config/databases.yml

You need to make sure that you have a connection set up for the session database. So edit /config/databases.yml so it has something like the following:

  1. all:
  2.   sessions_db:
  3.     class:          sfDoctrineDatabase
  4.     param:
  5.       dsn:          mysql://my_user:my_password@localhost/my_sessions_db

Step 3 - Edit the app/config/factories.yml

You will need to let symfony know that you are going to be using the sfPDOSessionStorage class to handle your session storage, so edit /app/config/factories.yml:

  1.   storage:
  2.     class: sfPDOSessionStorage
  3.     param:
  4.       db_table: sessions           # Name of the table storing the sessions
  5.       database: sessions_db     # Name of the database connection to use

Finally, make sure to clear your cache and you should be all set!

Step 4 - Configure symfony session cleanup, etc. (update)

Andy, from blog.t8d.de posted some additional findings and links on their blog about some settings that you will want to change within symfony and your php.ini to make sure that your application is performing the proper session cleanup.

Check out:

  1. http://blog.t8d.de/2008/05/06/symfony-sessions-in-mysql-mit-doctrine/
  2. http://robrosenbaum.com/php/howto-disable-session-timeout-in-symfony/
  3. http://redotheweb.com/2008/02/01/database-session-handling-and-garbage-collector/

marc is a Web Developer in Los Angeles, CA. He likes to receive comments :)
All posts by marc

5 Responses »

  1. t8d blog » Blog Archiv » Symfony Sessions in MySql mit Doctrine on May 11th, 2008 at 2:19 am:

    […] Sessions in Zukuft in der Datenbank speichern. Wie das zusamen mit Doctrine funktioniert steht beim Lampjunkie sehr gut beschrieben. Weiterhin haben wir folgendes […]

  2. Andreas Stephan on May 11th, 2008 at 2:24 am:

    Hi, thanks for this great tutorial.
    We have found that you have to add an index to the sess_id column in order to prevent the session management from getting horribly slow. Without the index,our update queries took up to 15 seconds! to complete, having 80.000 entries in the db. With the index added we’re back to a few milliseconds. :)

    So make sure you do: ALTER TABLE `sessions` ADD INDEX ( `sess_id` );
    Otherwise you will run into trouble.

    Cheers from Hamburg, Germany

    Andy

  3. marc on May 11th, 2008 at 12:35 pm:

    Hey Andy, I’m glad that you found the article useful! Thanks for the additional info on the index for the sess_id column. The other information your found out about session timeouts and posted on your blog was very useful, so I made sure to include the links above.

    -Marc

  4. Levan on October 8th, 2008 at 2:11 pm:

    Thanks for this nice tutorial. It seems I have yet to figure out the way to setup garbage collection properly. You see, I’ve added another field to the table called user_id which enables me to figure out who’s online, but the problem is, that even when user is logged out the session is not destroied fully (record in the table is not cleared completely) and user appears online as long as he navigates on the site as an annonimous user. Perhaps this is a symfony only issue (using 1.0 here). I’ll try to find a workaround.

    Cheers!

  5. Colnector on October 12th, 2008 at 1:22 pm:

    Thanks for this tutorial. Don’t you think that having an sfAPCSessionStorage or sfMemcachedSessionStorage would be a much better solution for storing sessions than using a database? I mean, it does seem like database storage of session information can slow things down since this information is fetched on every request.

Leave a Reply