Mysql – Event to update a column won’t run

MySQLmysql-eventupdate

I have successfully created an event in MySQL to update a column called dob in a table called auth.

My problem is that the event won't run. Here is my code:

DELIMITER $$
   CREATE EVENT update_event1
   ON SCHEDULE EVERY 1 MINUTE
   ON COMPLETION PRESERVE
  DO
    BEGIN
      UPDATE auth set dob=DATE_ADD(dob,INTERVAL 1 DAY) ;
    END $$;

How to solve this problem?

Best Answer

It seem that your event scheduler is not running causing the problem of event not getting executed. use following command to check

show global variables like 'event_scheduler';

if you get that it is off, you can do if you are in MySQL 5.6 or higher

 set global event_scheduler=1;

or add the following line in cnf file to make this change permanent and in versions <5.6

 event_scheduler=ON

The given code would work once you turn on the event scheduler using above approach.

Hope it helps.

Related Solutions

Mysql – Cannot update certain rows in innodb tables

There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.

You also have special cases of SELECT where you can lock specific rows on demand.

Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

MySQL Auto Update Column – How to Auto Update Column After 24 Hours

CREATE EVENT reset
    ON SCHEDULE
      EVERY 1 HOUR
        DO
update T1 
set state=1 
where time < date_sub(now(),interval 24 hour) 
  and (state=0 or state=2) ;

it will run in every hour

Best Answer

Related Solutions

Mysql – Cannot update certain rows in innodb tables

MySQL Auto Update Column – How to Auto Update Column After 24 Hours

Related Question