Create event daily, on a specific time

Question

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.

score 1 · Accepted Answer · answered Sep 05 '15 at 22:10

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

cronJob is a program? Yes I have access. It`s on my wamp server. — Bogdan Constantinescu, Sep 05 '15 at 22:35
I don't know how it works on windows but the name is schtasks using the command line utility. Sorry but i suppose you are on linux machine — Bestbug, Sep 05 '15 at 23:07
I have an Event scheduler in phpmyadmin. There I should work it out. — Bogdan Constantinescu, Sep 06 '15 at 07:45
Yeah you can also use the phpMyAdmin event scheduler, fill all the field. If you have problem write here — Bestbug, Sep 06 '15 at 08:11

Create event daily, on a specific time

1 Answers1

crontab -e