0

Is it possible to create mysql event that would start every weekend. It should START EVERY Friday 7pm, execute in interval of 1 HOUR and end EVERY MONDAY AT 5am. (Query updates a table that cron process reads every min)

It seems that mysql events "ENDS" is only absolute time. Also, it does not seem to be possible to create EVENT that would DISABLE or DROP another EVENT.

So, it looks like mysql events limitation and I likely have to combine mysql event and cron process to handle "ENDS".

Any help would be appreciated.

grizzthedj
  • 7,131
  • 16
  • 42
  • 62
NotTooTechy
  • 448
  • 5
  • 9

1 Answers1

1

I think of 2 ways to do it:

  1. Set your event to run every hour. Put an ugly IF inside your event body. 

    SELECT  @dow    := dayofweek(NOW())
,       @oclock := hour(NOW());`

IF (@dow = 6 AND @oclock >= 19) 
OR (@dow = 7) 
OR (@dow = 1 AND @oclock <= 5) THEN 
    -- run your code here 
END IF;
    • Advantage: It is simple and it just works.
    • Disadvantage: It's ugly as hell.

  2. Use more than one event to achieve it. For example:

    a) Your first event runs every Friday at 19h00;

    b) It creates an event to run every hour, starting on CURRENT_TIMESTAMP and ending on CURRENT_TIMESTAMP + number of hours you need.

    • Advantage: Won't have dummy runs. Uses events at its best.
    • Disadvantage: Higher complexity and "dynamic" events. Need to think about permissions/tracing/alerting/etc
Matheus Safraid
  • 11
  • 1
  • Thank you, I like the solution number 1. It is ugly but it does do the job with flexibility I wanted to have. I can have a user to put all dates and repetition into the table and so it's more convinient to view archived events. Otherwise, I would have to parse through the 'do' routine within event. – NotTooTechy Mar 12 '18 at 13:10
  • is the ` at the end of line 2 supposed to be? – popkutt May 24 '21 at 11:40