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 ta_table
using 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 show processlist
and it shows 2 preocesses root and event scheduler.
the 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?
Edit:
As of RolandoMySQLDBA's suggestion i upgraded mysql 5.1.14 to mysql 5.5 but the event still fails
Best Answer
I did some real digging in the bug list for event scheduler issues.
It seems that a certain time calculation for the event scheduler was not portable. This bug was fixed as of MySQL 5.1.48 (Fixed June 2, 2010).
There was a past issue about SHOW EVENTS not getting events from the correct database. This bug was fixed as of MySQL 5.1.57 (Fixed May 5, 2011).
The latest scheduler bug was fixed July 5, 2011 in MySQL 5.1.58
You are using MySQL 5.1.41. You may want to upgrade to the latest version of MySQL 5.1 which is 5.1.58. No scheduler bugs exists as of today.
CAVEAT
On another note, I would change the SQL query to not only do less work
Instead of your DELETE query:
Restructure it as follows:
Your DELETE will calculate against every row in the table. This new DELETE stops short when comparing ti_time against a time value (now() - interval 2 hour) instead of computing timestampdiff on every row.
Make sure ti_time is indexed. If not, do this:
Assuming the table is MyISAM, you may also want to periodically shrink the table every month like this:
I hope this information helps !!!
UPDATE 2011-07-19 08:00 EDT
From the last chat room session lovesh and I had, here is the example I ran to create the event on my PC running MySQL 5.5.12:
This worked for me when ta_table was MyISAM. It just kept running using InnoDB. This may be the sticking point.