Updating a column after expiration of specific time in MySQL

Question

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?

Answer 1

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'

Answer 2

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.