Articles

Expirable user sessions with MySQL events

One awesome feature of Redis is that you can set an expiration time in a database key, and this works great to handle your user session expirations.

If a session token is used for any kind of operation you can just renew that time after completing it. If the token is not used for a given period of time, it will be deleted automagically and the operation will be denied, so the user will need to login again in order to get a new session token.

This can also be achieved by using MySQL, through the MySQL Event Scheduler, that can be used to setup a one-time or recurring event to cleanup stale or unused sessions. Let's see how!

Turn on the MySQL Event Scheduler

To setup the MySQL Event Scheduler, you can refer to the MySQL manual, but here are the basic steps:

If you are using Amazon RDS to host your MySQL database, you will be able to find the event_scheduler option in the Parameter Group for your database instance.

Once the event scheduler is running, you should be able to see it by doing a SHOW PROCESSLIST in your MySQL server.

Create a table to save your user sessions

The first needed step is to create a table to store your user sessions. This is a simple, regular table, and the most important column is the one named "last_activity". We're going to store a unix timestamp into this field.

For every new session that your application creates, you need to store the session token here, along with the ID of the owner and set the "last_activity" column to the unix timestamp of the creation time.

Also, every time the session token is used, you need to "keep alive" the token by updating "last_activity" with the unix timestamp of the operation.

Create a mysql periodic event in the event scheduler to delete unused sessions

Here's where the magic actually happens. We are going to create a periodic (or recurring) event in the MySQL event scheduler so it runs once a minute, to delete any session that hasn't been used in the last 30 minutes:

As long as your application is updating the "last_activity" field of your sessions whenever they are used, this will make sure that older sessions are deleted, and just keep the ones in use.

Enjoy :) Until the next time!