3
  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?

2
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
    ON SCHEDULE
      EVERY 24 HOURS  -- run every 24 hours
    DO
      UPDATE myschema.youtable set mycolumn='1'
1

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.

1
  • 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.. May 30 '13 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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