17

Is it possible, to create a record in MySQL database, that would subject to TTL (Time to live) option.

I want to make a simple password recovery feature and I need to store an activation key, that would be stored in database for only 3600 seconds and then be deleted automatically after that time? I know there are bunch of other ways to achieve this but they are not as straight forward as an idea of TTL functionality.

I guess that MySQL doesn't have such functionality but I just thought that maybe I'm missing something and there is?

Ilia
  • 12,670
  • 11
  • 49
  • 83

2 Answers2

27

I just found out that MySQL 5.1+ has event scheduler. The MySQL Event Scheduler manages the scheduling and execution of events - tasks that run according to schedule.

Stored routines require the event table in the MySQL database. This table is created during the MySQL installation procedure.

Syntax for using it would be:

CREATE EVENT
  ClearUserActivationCodes
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
DELETE FROM
  user_activation_code
WHERE code_time_stamp < NOW()
END

It's quite useful and fully satisfies my needs for automatically clearing tables without using cron jobs.

Ray Baxter
  • 3,152
  • 22
  • 25
Ilia
  • 12,670
  • 11
  • 49
  • 83
  • 2
    I got syntax errors using your code example. I could only execute it w/o errors after removing `BEGIN` and `END` statements! :) – Renato Gama Oct 13 '15 at 18:25
  • Hello how efficient is this in case you have thousands of records to delete? Is there any table locking involved – Playdome.io Sep 05 '18 at 18:28
  • 1
    It should be as efficient as _MySQL_ itself but it would depend on your database design and engine type. _Useful to read:_ https://stackoverflow.com/questions/37835714/performance-implications-of-mysql-event-scheduling https://dev.mysql.com/doc/refman/8.0/en/internal-locking.html – Ilia Sep 06 '18 at 06:16
3

Personally I would store the key with a TIMESTAMP field, using the DEFAULT CURRENT_TIMESTAMP option.

Then, when fetching the key, check the timestamp. If it's less than an hour ago, then you're okay to go ahead. Otherwise, treat it as invalid and delete the key (you can also provide a specific error message saying that the key expired and the user needs a new one).

Additionally, use a cron task running once a day to delete keys that are older than a day. This ensures you don't get a growing pile of expired codes if people never actually enter them.

Niet the Dark Absol
  • 311,322
  • 76
  • 447
  • 566
  • 1
    Yes, yes I know all about that! I would do the same! But having it done as in-build functionality would be absolutely great, I think! – Ilia Oct 05 '13 at 11:57
  • Honestly I don't think it'd be that great. Having the ability to provide a custom error message for codes that have expired would be very useful and user-friendly. – Niet the Dark Absol Oct 05 '13 at 11:58
  • I agree, then you wouldn't have such ability. But besides *activation key* there are a lot of different situations, when you could possibly need TTL dependent record. – Ilia Oct 05 '13 at 11:59
  • Again, I don't think so. I prefer for data to be deleted only when I explicitly tell it to be. – Niet the Dark Absol Oct 05 '13 at 12:00
  • Isn't having an imaginary option like **DEFAULT TTL** wouldn't be explicitly specified by you? Yes, it would be! :) – Ilia Oct 05 '13 at 12:03
  • And there would be no need for another process hanging in the servers' memory as *mysqld* already there.. – Ilia Oct 05 '13 at 12:04