Menu
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?
347
Scylla, Cassandra, DynamoDB, MySQL, Oracle, PostgreSQL, and SQL Serve are all major platforms that support TTL, providing the functionality to delete expired data according to the TTL value automatically. TTL is also frequently deployed in cache and storage systems such as RocksDB, Redis, and MyRocks.
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.
The transaction log in MySQL is not enabled by default and must be enabled in order to log transactions. To determine if the transaction log is active you can use the “show binary logs” statement: SHOW BINARY LOGS; If binary logging is disabled you will receive an error stating “you are not using binary logging”.
2) In a database, a record (sometimes called a row) is a group of fields within a table that are relevant to a specific entity. For example, in a table called customer contact information, a row would likely contain fields such as: ID number, name, street address, city, telephone number and so on.
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.
52
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.
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
