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.
  1. i have a table where i have a column named 'state'(int(2)) and 'modify_time' (time_stamp on update current timestamp).

  2. i have an update trigger which changes the value of state column to 0 based on some condition.

    3 . i want to set the value of state column to 1 after 24 hours of modify_time , if it still 0 .

    4 . i tried below method to test :

CREATE EVENT myevent1 ON SCHEDULE AT current_timestamp + interval 1 minute DO UPDATE test.mytabletable SET state = 0;

but it is not doing any thing.

is there any other alternative method ?

share|improve this question
add comment

1 Answer

Your UPDATE query will need some work, because as-written, it will update all of the state values to 0 every minute, but if it's not working, you should verify that the event scheduler is actually running.

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   | 
+-----------------+-------+
1 row in set (0.00 sec)

It shouldn't be OFF.

mysql> set global event_scheduler = on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

If it's already on, then check your MySQL error log. Events that fail to run will log messages there.

share|improve this answer
    
so you mean , for this i can use only Events , not any triggers ? i need to update my state column to 1 after 24 hours of modify_time , if it is 0.. –  user21546 May 30 '13 at 7:35
add comment

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.