How to create a stored procedure and run it on a scheduled time (in MYSQL)

Question

Need your expertise, I have a script in MySQL and I want it to create a stored procedure and run it on a scheduled time (let's say I need to run it on 7 am and 1 pm).

sample script:

update systemuser 
set defaultSiteCode =(select siteCode from site where isremote = 0)
where type in ('U', 'A');

Answer 1

As you didn't go into technical details about the technology stack and OS you use I will answer your question theoretically.

You have multiple approaches. One of them is to set up a cron task:

1. Create a procedure on MySQL server and save
2. Using any serverside programming language create an executable file with a code that executes the function on MySQL DB. Based on OS you use it might be shell or batch script as well.
3. Set this script as a cronjob

Take a look at the answers to this question: Run a mySQL query as a cron job?

---

Another way might be to create a MySQL event. Read about MySQL events here: https://t.ly/wmvqv

And here is the comparison of these two options:

Cronjob or MySQL event?

Answer 2

Here is the event that I have create and it runs as expected:

--

CREATE DEFINER=root@localhost EVENT daily_userupdate ON SCHEDULE EVERY 5 MINUTE STARTS '2020-05-01 00:44:00' ON COMPLETION PRESERVE ENABLE DO update systemuser set defaultSiteCode =(select siteCode from site where isremote = 0) where type in ('U', 'A');

--