CODEWITHSUNDEEP
eventsmysql-event
Bogdan Constantinescu
Bogdan Constantinescu

Reputation: 75

Create event daily, on a specific time

First event deletes all rows from a table:

CREATE DEFINER=`frontdes_user`@`localhost` EVENT `DELETE_flux_receptie` ON SCHEDULE EVERY 1 DAY STARTS '2015-09-06 00:00:00' ON COMPLETION PRESERVE ENABLE DO DELETE FROM `flux_receptie`

Second event deletes the id column from the same table:

CREATE DEFINER=`frontdes_user`@`localhost` EVENT `delete_id` ON SCHEDULE EVERY 1 DAY STARTS '2015-09-06 00:00:00' ON COMPLETION PRESERVE ENABLE DO ALTER TABLE `flux_receptie` DROP `id`

Third event adds id column into the same table:

CREATE DEFINER=`frontdes_user`@`localhost` EVENT `add_id` ON SCHEDULE EVERY 1 DAY STARTS '2015-09-06 00:00:00' ON COMPLETION PRESERVE ENABLE DO ALTER TABLE `flux_receptie` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`)

I want these events to run daily: First one daily at 06:30, the second one daily at 06:35 and the third daily at 06:40. Can anyone help me please with the correct schedule? Best regards, Bogdan.

Upvotes: 0

Views: 63

Answers (1)

Bestbug
Bestbug

Reputation: 475

If you have access to your machine you can use cronJob, the basic format of a crontab schedule consists of 6 fields, placed on a single line and separated by spaces, formatted as follows:

minute hour day month day-of-week command-line-to-execute

first create a script:

First script

#!/bin/bash

mysql --user=[username] --password=[password] --database=[db name] --execute="CREATE DEFINER=`frontdes_user`@`localhost` EVENT `DELETE_flux_receptie` ON SCHEDULE EVERY 1 DAY STARTS '2015-09-06 00:00:00' ON COMPLETION PRESERVE ENABLE DO DELETE FROM `flux_receptie`"

Second script

#!/bin/bash

mysql --user=[username] --password=[password] --database=[db name] --execute="CREATE DEFINER=`frontdes_user`@`localhost` EVENT `delete_id` ON SCHEDULE EVERY 1 DAY STARTS '2015-09-06 00:00:00' ON COMPLETION PRESERVE ENABLE DO ALTER TABLE `flux_receptie` DROP `id`"

Third script

#!/bin/bash

mysql --user=[username] --password=[password] --database=[db name] --execute="ALTER TABLE `flux_receptie` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`)"

then create 3 cron using crontab -e

crontab -e

30 6 * * * /path/of/scriptOne

35 6 * * * /path/of/scriptTwo

40 6 * * * /path/of/scriptThree

Upvotes: 1

Related Questions