CODEWITHSUNDEEP
mysqldatabase
Om Bissa
Om Bissa

Reputation: 148

How to schedule a MySQL Event on every XX:50 Seconds?

I Have a MySQL table with field name result. I want to use a event to execute on every XX:50 seconds and update the value of field by a random number.

Need help regarding the syntax for every XX:50 seconds.

I have tried this query.

CREATE EVENT `testEvent` 
ON  SCHEDULE EVERY 50 SECOND 
DO BEGIN

SET @A := (SELECT MAX(`id`) FROM `table`);

UPDATE `table` SET `field` = (RAND()*18) WHERE `id` = @A;   

END

using this query first update is done on 00:00:50 then next update will be done on 00:01:40, 00:02:30 and so on.

but i want to execute it on 00:00:50, 00:01:50, 00:02:50 and so on..

Upvotes: 1

Views: 13973

Answers (2)

user1191247
user1191247

Reputation: 12998

Too late for OP, but may be of use to someone else in future.

If you want your event to be executed EVERY XX:50 seconds, then you want it to be executed EVERY 1 MINUTE with the start time offset by 50 seconds:

CREATE EVENT `testEvent` 
ON SCHEDULE
    EVERY 1 MINUTE
    STARTS DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:50')
DO
    UPDATE `table` SET `field` = CEIL(RAND() * 18) WHERE id = (SELECT id FROM (SELECT MAX(id) id FROM `table`) t);

If the datetime is 2023-10-09 10:36:53 when the event is created, the Starts value for the event is set to 2023-10-09 10:36:50, which is in the past. The first execution will still maintain the correct time offset and be at 2023-10-09 10:37:50.

Upvotes: 0

peterm
peterm

Reputation: 92785

MySql events

To execute an update every 50 seconds:

CREATE EVENT myevent
ON SCHEDULE EVERY 50 SECOND
DO
   UPDATE yourdb.yourtable SET yourcolumn = RAND();

To do it every 2 min and 50 seconds:

CREATE EVENT myevent
ON SCHEDULE EVERY '2:50' MINUTE_SECOND
DO
   UPDATE yourdb.yourtable SET yourcolumn = RAND();

Use SHOW PROCESSLIST to check if event scheduler is enabled. If it's ON you should see a process "Daemon" by user "event_scheduler". Use SET GLOBAL event_scheduler = ON;to enable the scheduler if it's currently not enabled. More on configuring event scheduler here

Upvotes: 11

Related Questions