CODEWITHSUNDEEP
mysql
Abhilash
Abhilash

Reputation: 55

MySQL Event Scheduling

I have a MySQL event scheduled to run everyday at midnight - But the database server is shutdown everyday evening and is restarted every morning, but not necessarily at the same time. For instance the server is started at: 10AM, I still need the event to be executed for the day, though it was scheduled for 12AM. I tested this scenario in ORACLE and it works, but doesn't quite in MySQL. Do you have a suggestion?

Thanks in advance, Abhilash

Upvotes: 2

Views: 879

Answers (3)

Devart
Devart

Reputation: 121902

You can create an event that would fire every hour. Create additional table to store information about event's firing.

Do these steps in the event:

  • check if event was fired - read info. from the table
  • do the code if the event stil was not fired, write information (the date) into the table.

Upvotes: 0

eggyal
eggyal

Reputation: 125835

Direct support for this behaviour was submitted as a feature request in bug #46813. It is still "awaiting triage":

The event scheduler should be able to periodically check for, and catch up on, missed events. While events that need to run this way can be scheduled using Task Scheduler on Windows and anacron on Mac and *nix, this is inelegant and platform-dependent.

As the bug report suggests, you can schedule such events using software that is external to MySQL. Alternatively, one could use MySQL's init-file option to specify a file containing commands that should be executed on startup; using that one could check the INFORMATION_SCHEMA.EVENTS table: compare LAST_EXECUTED column against event schedule and execute EVENT_DEFINITION if a scheduled event was missed.

Upvotes: 2

Ariel
Ariel

Reputation: 26753

Sorry, but MySQL doesn't handle this. If the event is missed, it's missed. It doesn't try to run it again later.

What you could do is make a table that logs when an event was last run (the date), then schedule it for each hour, but before running check if it already ran that day. That way the first time it runs after midnight it will activate.

Upvotes: 0

Related Questions