I'm developing with PHP + MySQL.
I need to delete a row (tuple) in a table when the expiration date occurs.
Probably the best way be a Trigger but I have no idea how to do it.
Somebody have an example?
7
-
When should this happen? On a specific time, then an update on the record occurs. Triggered by your application? Depending on that this can be realized with an Event, Trigger or Procedure.juergen d– juergen d2014-07-10 18:37:26 +00:00Commented Jul 10, 2014 at 18:37
-
Consider using the event scheduler instead. This seems me the best solution for expiration dates.VMai– VMai2014-07-10 18:46:01 +00:00Commented Jul 10, 2014 at 18:46
-
the expiration date is a field on my table.DominicS– DominicS2014-07-10 18:51:30 +00:00Commented Jul 10, 2014 at 18:51
-
Specifically I have a table that stores student enrollment. if the student does not validate their registration within 24, I want to delete this row. I store the (registration time + 24 hours) in the table in a field "endDate". So, When "endDate" occurs I have to delete this row.DominicS– DominicS2014-07-10 19:03:06 +00:00Commented Jul 10, 2014 at 19:03
-
Have you got some example about "event scheduler" ?DominicS– DominicS2014-07-10 19:10:34 +00:00Commented Jul 10, 2014 at 19:10
|
Show 2 more comments
1 Answer
You could use MySQLs event scheduler. The minimal example of the manual page of CREATE EVENT would be a good base for that what you probably need:
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
If you want to clean up every 10 minutes you would use
CREATE EVENT myevent
ON SCHEDULE EVERY 10 MINUTE
And your clearing statement would be a DELETE:
DO
DELETE FROM yourTable WHERE expiration_date < NOW();
That's all and MySQL will do it automatically every 10 minutes. Complete in four lines of SQL:
CREATE EVENT clear_expirations -- a better name for the event
ON SCHEDULE EVERY 10 MINUTE -- as you need it
DO
DELETE FROM yourTable WHERE expiration_date < NOW();
Edit
Per default the event scheduler will be stopped:
The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server. It has one of these 3 values, which affect event scheduling as described here:
OFF: The Event Scheduler is stopped. The event scheduler thread does not run, is not shown in the output of SHOW PROCESSLIST, and no scheduled events are executed. OFF is the default value for event_scheduler.
When the Event Scheduler is stopped (event_scheduler is OFF), it can be started by setting the value of event_scheduler to ON. (See next item.)
ON: The Event Scheduler is started; the event scheduler thread runs and executes all scheduled events.
...
DISABLED: This value renders the Event Scheduler nonoperational. When the Event Scheduler is DISABLED, the event scheduler thread does not run (and so does not appear in the output of SHOW PROCESSLIST). In addition, the Event Scheduler state cannot be changed at runtime.
You can get the value of this system variable with
SELECT @@event_scheduler;
If the result is OFF you can start the event scheduler with
SET GLOBAL event_scheduler = 'ON';
You should modify the configuration of your MySQL server, so that the event scheduler will be running at the start of the mysqld process.
It should be clear, that you got to be administrator of your MySQL server to modify this configuration.
11 Comments
DominicS
This is my code exactly: (My code is located in the same file as CREATE TABLE. Is this correct?) <\n>
CREATE EVENT clear_expirations ON SCHEDULE EVERY 1 MINUTE DO DELETE FROM #__anual`` WHERE end_date <= NOW(); But does nothing. My table remains the same. What can be happening? (I don't writ "AT" before "EVERY" because I want to do this always every X minutes). Thanks for your example. It's very valuable.
VMai
@DominicS You spotted my slackness in modifying the example code, great! I've edited my answer. Please have a look at dev.mysql.com/doc/refman/5.6/en/events-privileges.html and look at
SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='clear_expirations' AND EVENT_SCHEMA='your database name';DominicS
GRANT EVENT ON systemadmin.* TO [email protected]; event_scheduler=on mysql> SET GLOBAL event_scheduler=on; and your SELECT is OK, but does nothing.VMai
What's the result of
select @@event_scheduler;? If it's value is off, then the event scheduler won't do anything. You can set it with set global event_scheduler = 'ON';DominicS
The result of select @@event_scheduler; is ON
|