Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.4 to mysql 5.5 but the event still fails

On another note, I would change the SQL query to not only do less work

Instead of your DELETE query:

delete from ta_table where timestampdiff(minute,ti_time,now())>120;

Restructure it as follows:

delete from ta_table where ti_time < (now() - interval 2 hour);

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:

ALTER TABLE ta_table ADD INDEX (ti_time);

Assuming the table is MyISAM, you may also want to periodically shrink the table every month like this:

ALTER TABLE ta_table ENGINE=MyISAM;

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:

He may run into issues with upgrading the MySQL for the WAMP install that he's using. Just a comment.
–
jcolebrand♦Jul 18 '11 at 1:42

2

@jcolebrand : I hope MySQL 5.1.58 would be OK to upgrade to since there are no major changes to the mysql schema and metadata layout from 5.1.x to a more recent 5.1.x. Personally, I have gotten away with upgrading MySQL 5.1 to 5.5 in WAMP using great care.
–
RolandoMySQLDBAJul 18 '11 at 1:50

I added a link to the MySQL 5.1 downloads to my answer
–
RolandoMySQLDBAJul 18 '11 at 2:03

@RolandoMySQLDBA i have mysql 5.5 setup but i am unsure of installing it as things like phpMyAdmin will not work since it came bundled with xampp
–
loveshJul 18 '11 at 2:54

2

@lovesh Rolando explained how the new DELETE would be more efficient (now()-interval 2 hour) only has to be calculated once by the parser. the timestampdiff() function must be calculated for each row, because it requires that row's 'ti_time' to be used in calculation.
–
Derek DowneyJul 18 '11 at 17:38