Take the 2-minute tour ×
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

share|improve this question
    
can i assume the 'delete from table' query that you said works is a type (should be delete from ta_table?) Also, are there any errors in your mysql error log? –  Derek Downey Jul 17 '11 at 21:27
    
@DTest: no there are no errors. no retcode=0 –  lovesh Jul 17 '11 at 21:29
    
@DTest: ya it is delete from ta_table. thanks for pointing out –  lovesh Jul 17 '11 at 21:42
    
@lovesh : Is this problem solved ??? –  RolandoMySQLDBA Jul 17 '11 at 22:22
    
@RolandoMySQLDBA no its not solved. i just removed a "printing mistake" –  lovesh Jul 17 '11 at 23:12

2 Answers 2

up vote 5 down vote accepted

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:

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:

drop database lovesh;
create database lovesh;
use lovesh
create table mydata (id int not null auto_increment primary key,ti_time timestamp DEFAULT current_timestamp) ENGINE=MyISAM;
DELIMITER $$
DROP PROCEDURE IF EXISTS `lovesh`.`LoadMyData` $$
CREATE PROCEDURE `lovesh`.`LoadMyData` ()
BEGIN
    DECLARE NDX INT;
    SET NDX = 0;
    WHILE NDX < 100 DO
        INSERT INTO mydata (ti_time) VALUES (NOW() - INTERVAL CEILING(14400*RAND()) SECOND);
    SET NDX = NDX + 1;
    END WHILE;
END $$
DELIMITER ;
show create table mydata\G
SHOW CREATE PROCEDURE LoadMyData\G
CALL lovesh.LoadMyData();
CREATE TABLE ta_table LIKE mydata;
ALTER TABLE ta_table DISABLE KEYS;
INSERT INTO ta_table SELECT SQL_NO_CACHE * FROM mydata;
ALTER TABLE ta_table ENABLE KEYS;
CREATE EVENT ev
    ON SCHEDULE
      EVERY 1 MINUTE
      STARTS (NOW() + INTERVAL 1 MINUTE)
    DO
      DELETE FROM ta_table WHERE ti_time > NOW() - INTERVAL 2 HOUR;
SELECT COUNT(1) FROM ta_table;
SELECT SLEEP(62);
SELECT COUNT(1) FROM ta_table;

This worked for me when ta_table was MyISAM. It just kept running using InnoDB. This may be the sticking point.

share|improve this answer
    
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. –  RolandoMySQLDBA Jul 18 '11 at 1:50
    
I added a link to the MySQL 5.1 downloads to my answer –  RolandoMySQLDBA Jul 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 –  lovesh Jul 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 Downey Jul 18 '11 at 17:38

I believe your event must be defined using fully qualified schema and tablename:

In MySQL 5.1.6, any table referenced in an event's action statement must be fully qualified with the name of the schema in which it occurs (that is, as schema_name.table_name).

Reference: http://www.cs.duke.edu/csl/docs/mysql-refman/events.html#events-limitations-restrictions

So your query in the EVENT should be redefined (using Rolando's improved delete query):

DELETE FROM `ta_db`.`ta_table` WHERE ti_time < (NOW() - INTERVAL 2 HOUR);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.