moses toh moses toh - 5 months ago 22
SQL Question

Why mysql event schedule not working in database localhost?

I had run :

SET GLOBAL event_scheduler = ON;


My event scheduler script is like this :

CREATE EVENT update_status
ON SCHEDULE EVERY 2 MINUTE
DO
UPDATE customer_group
SET is_status = 1
WHERE CURRENT_TIMESTAMP >= end_date


It's not updating value in database localhost

Even though, in database server, it's an update

Any solution to solve my problem?

Thank you

Answer

Look into the following.

Issue a select CURRENT_TIMESTAMP; or

select count(*) as theCount
from customer_group
where is_status=1 and CURRENT_TIMESTAMP >= end_date;

In other words, do some recon. Events succeed or fail silently as they have no user interface.

Run some commands like:

show variables where variable_name='event_scheduler';

-- currently OFF (or it could be ON)

Specify the start time for your event during creation, and specify ON COMPLETION PRESERVE such as

drop event if exists `Every_5_Minutes_QuestionUpdateImport`;
DELIMITER $$
CREATE EVENT `Every_5_Minutes_QuestionUpdateImport`
  ON SCHEDULE EVERY 5 MINUTE STARTS '2015-09-01 00:00:00'
  ON COMPLETION PRESERVE
DO BEGIN
    --
    -- all your statements go here
    -- 
END$$
DELIMITER ;

Turn on the event handler:

SET GLOBAL event_scheduler = ON;  -- turn her on and confirm below

Confirm it:

show variables where variable_name='event_scheduler';

Examine the status of all events in a particular database by database name:

show events from stackoverflow; -- note stackoverflow is my database name 

enter image description here

Disable or enable a certain event by name in the current selected db:

ALTER EVENT Every_2_Minutes_QuestionUpdateImport disable;
ALTER EVENT Every_2_Minutes_QuestionUpdateImport enable;

And last but not least, when I am writing a new Event, I always add initially in the event statements to log to a log table (with an insert statement and a datetime of now()). That way, I know it fired and that data in a where clause like yours perhaps is not giving me a wrong read on the whole thing.

And I mean I go crazy all over the place. Initially, then I rem it out later:

    set filename:=concat('c:\\datavault\\stackoverflow\\app01\\batches\\processMe_',LPAD(@idToPerform, 8, '0'),'.txt');
    set logMsg:=concat("It would appear I am to process this thing: ",filename);

    insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged)
    select incarnationId,evtAlias,10,logMsg,now(); -- 10: some step somewhere in the middle

Remember for all practical purposes (except maybe your table you really care about from your question) ... that EvtsLog of mine is your only user interface as an Events programmer and your best friend.

Look at the manual page for what ON COMPLETION PRESERVE means as well as other things.