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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
  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 is 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;

This did not doing anything. Is there any alternative method?

share|improve this question
up vote 1 down vote accepted
SET GLOBAL event_scheduler = ON; -- enable event scheduler.
SELECT @@event_scheduler;  -- check whether event scheduler is ON/OFF
CREATE EVENT e_store_ts  -- create your event
      EVERY 24 HOURS  -- run every 24 hours
      UPDATE myschema.youtable set mycolumn='1'
share|improve this 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

Your Answer


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.