the issue

we have a database table containing usernames and passwords, but we want to make them temporary like expiring after a fixed number of days from the creation. This is typical usage for a wi-fi captive portal with RADIUS authentication backed on mysql.

the idea

we store a new field in the table with the timestamp, and run a periodic “cleaner” job that deletes record older than X days.

we can leverage the mysql event scheduler in order to provide a self-contained solution, indipendent from operating system.

the implementation

alter table radcheck add column ts_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

CREATE EVENT expireuser
ON SCHEDULE EVERY 12 HOUR
DO
DELETE FROM radcheck 
WHERE TIMESTAMPDIFF(DAY, ts_create , NOW()) > 7
;

to get it started, make sure you have enabled the event scheduler:

SET GLOBAL event_scheduler = ON; 

or by placing

event_scheduler=ON

in your my.cnf, section [mysqld]