i am running
mysql 5.1.41 that came bundled with
xampp on windows. the problem is that the event doesnt run automatically even when the
event scheduler is
ON. i have a table named
innodb engine and it has 4 fields one of which is
ti_time with a
timestamp type with default value of
current timestamp. this field
ti_time is given the value of timestamp at which the row is inserted. now i want to delete all rows which are 2 hours old from the table
ta_table so i created an event
the event looks like this
CREATE EVENT ev ON SCHEDULE EVERY 1 MINUTE STARTS 2011-07-17 14:54:52 ENABLE DO begin delete from ta_table where timestampdiff(minute,ti_time,now())>120; end
now this event should delete any rows with
ti_time field greater than 2 hours(120 minutes). when i execute this query
delete from ta_table where timestampdiff(minute,ti_time,now())>120;
it works. it deletes the rows older than 2 hours. which means my query is correct but the event is not running. my event scheduler is running which i confirmed by
and it shows 2 preocesses root and event scheduler.
state of the event scheduler is
waiting for next activation.
when i ran this query
SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE event_name = 'ev'
it gives result as
status = enabled last executed=2011-07-18 02:36:38
but when i see the table
ta_table the records are not deleted? whats wrong with this?
As of RolandoMySQLDBA's suggestion i upgraded mysql 5.1.14 to mysql 5.5 but the event still fails