0

I wrote a query in MySQL which I want to run daily on our wordpress server. Using Cronjobs gave me a lot of collate errors, so I took the easier rout with the Event Scheduler which is easier to implement via phpMyAdmin.

Now I would like to run the query only at specific times: Once per hour, between 8 am and 5 pm.

Is this possible using Event Scheduler? I know of: 

STARTS = ...
ENDS = ...

But as far as I know, that only sets global values (start and end once). Is there any possibility to set a recurring daily end? Or do I have to use cron for that?

Francisco
  • 10,918
  • 6
  • 34
  • 45
Jan T.
  • 55
  • 1
  • 7
  • You do not include your mysql commands, your scripts, your SH or bat script. This question is not complete and cannot be answered. – Menelaos Jun 16 '17 at 07:56
  • Neither of these are needed to answer my question. Since I use Event Scheduler, a sh or bat script is not used. I want to know if setting a specific recurring time in event scheduler is possible or not. Please advise me what information you need additionally. – Jan T. Jun 16 '17 at 07:59
  • Have a look @ https://stackoverflow.com/questions/3070277/mysql-event-scheduler-on-a-specific-time-everyday – Menelaos Jun 16 '17 at 08:03
  • I saw that thread. The problem there is different to mine, since it only refers to setting a daily recurring time. I need to make further changes to Event Scheduler (daily time frame), hence my question. – Jan T. Jun 16 '17 at 08:07

2 Answers2

0

Issue

The problem from what I see is that you want to do two things:

  • Run something once per hour (interval)
  • But not continue doing this action outside of a specific daily timeframe.

The first part is very easily to take care of using the example from: MySQL Event Scheduler on a specific time everyday

The second part is the one that creates the complexity.

Solutions

You have two available solutions that I can think of:

  1. Schedule an event with interval 1 hour, and add a check in your SQL statement / function / procedure to check that that time of day is within the specific time interval you want.
  2. Schedule multiple events (9-10) for the specific times e.g. (8:59am, 9:59am...4:59am)

Personally I would prefer solution one since it is much easier to change a value in your script than have to reschedule / manage 9-10 events.

Quick Example

delimiter |

CREATE EVENT hours
    ON SCHEDULE
      EVERY 1 HOUR
      STARTS (TIMESTAMP(CURRENT_DATE))
    DO
      proc_label:BEGIN

         IF curtime() >= "08:00" OR curtime() <= "17:00" THEN
          LEAVE proc_label;
        END IF;

        select 1+1;
      END |

delimiter ;
Menelaos
  • 23,508
  • 18
  • 90
  • 155
  • 1
    Option 1 seems like a good workaround for my problem, thanks! I will leave it open for a while, if somebody else has another idea. – Jan T. Jun 16 '17 at 08:51
0

Just a little sugestion, to change from OR to AND.

IF curtime() >= "08:00" AND curtime() <= "17:00" THEN
      LEAVE proc_label;
David Buck
  • 3,752
  • 35
  • 31
  • 35
Robson
  • 1