Schedduled queries

papo85's profile image papo85 posted 2 years ago in General Permalink

Hello

My queries don't run and I don't know why ?

Can you help me ?

papo85's profile image papo85 posted 2 years ago Permalink

the CREATE CODE :

CREATE DEFINER=`root`@`%` EVENT `Automatic_completion`
    ON SCHEDULE
        EVERY 1 SECOND STARTS '2021-11-08 15:11:02'
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT ''
    DO BEGIN
UPDATE device SET device_status_id = 5 WHERE device_status_id<>5 AND remove_date < CURRENT_DATE 
;
UPDATE device SET device_status_id = 4 
    WHERE 
    device_status_id<>1 
    AND (expedition_date < CURRENT_DATE AND add_date IS NULL AND remove_date IS NULL)
;
UPDATE device SET device_status_id = 4 
    WHERE device_status_id<>4
    AND add_date < CURRENT_DATE 
    AND (remove_date IS NULL OR remove_date > CURRENT_DATE)
;   
UPDATE device AS d
INNER JOIN house AS h ON h.house_id = d.house_id
SET d.device_url = CONCAT(h.setup_id,"/*")
WHERE d.device_url IS NULL AND h.setup_id IS NOT NULL 
;   
END
ansgar's profile image ansgar posted 2 years ago Permalink

I guess you should uncheck "Drop event after expiration". On the other hand there doesn't seem to be an END date set, so the expiration should never happen. No clue.

papo85's profile image papo85 posted 2 years ago Permalink

The proble come from : event_scheduler = 'OFF';

And I don't have SUPER PRIVILEGE in order to change this ...

Please login to leave a reply, or register at first.